Genie Discord forum

Author Avatariamcomcy
2/22/2023, 10:08:30 PM

There is a demo app that creates music albums. One feature add that I need to understand is adding the list of songs in the album database. There might be 7-12 songs in an album for example.

How to set up the table?

function up() create_table(:albums) do pk() column(:title, , limit = 200) column(:authors, , limit = 250) column(:year, , limit = 4 column(:????, :?????) #variable list of songs end

add_index(:albums, ) add_index(:albums, ) add_index(:albums, )

end (edited)

module Albums

import SearchLight: AbstractModel, DbId import Base: @kwdef

export Ablums

@kwdef mutable struct Albums <: AbstractModel id::DbId = DbId() title::String = "" authors::String = "" year::Int = 0 songs::??? #how to make this a list? end


Author AvatarPere
2/23/2023, 11:06:10 AM

It is not possible to directly store an array to the DB. You could add a new table for the songs, where each song has an album id. Then, to retrieve all of the songs in an album you'd call something like SearchLight.find(Song, SQLWhereExpression("album_id = $albumid"))

Author Avatarabhimanyuaryan
2/23/2023, 11:58:05 PM

we will work on searchlight this summer for sure. We need to support complex db operations

Author AvatarSchighSchagh
2/24/2023, 1:31:02 PM

another way to do it is to JSON-encode the list into a column.

Author AvatarSchighSchagh
2/24/2023, 1:31:30 PM

noting that SQLite does not support native array columns at all.

Author AvatarSchighSchagh
2/24/2023, 1:32:48 PM

but all 3 supported DB providers do support JSON stuff in their SQL implementation, so you'd still be able to write queries against the column