On Mon, Aug 1, 2022 at 9:05 AM [email protected] <[email protected]>
wrote:
> Hi,
>
> How can I count a many_to_many relations when retrieving the list?
>
> Example:
>
>
> # models
>
> class Artist < Sequel::Model(db[:artists])
> db[:artists]
>
Note that this db[:artists] call does nothing, you should probably remove
it. Ditto for the db[:albums] on the line by itself below.
>
> many_to_many(
> :albums,
> class: Album,
> join_table: :artist_albums,
> join_table_db: db,
> left_key: :artist_id,
> right_key: :album_id
> )
> end
>
> class Album < Sequel::Model(db[:albums])
> db[:albums]
>
> many_to_many(
> :artiss,
> class: Artist,
> join_table: :artist_albums,
> join_table_db: db,
> left_key: :album_id,
> right_key: :artist_id,
> )
> end
>
>
> # What I want:
>
> artists = Artist.all
> # here I would love to have a field on each artist that gives me the
> # count of the associated albums. e.g. artist.album_count
> # Is there a way to fetch it once and not cycling to each artist fetched
> # and query for that one
>
Multiple ways to do this in SQL. One way is a correlated subquery. Here's
an example (untested):
artists =
Artist.select_append(db[:artist_albums].where(:artist_id=>Sequel[:artists][:id]).select{count(:album_id)}.as(:album_count))
Note that you would need to do artist[:album_count] instead of
artist.album_count, as Sequel does not use method_missing to support
calling methods for columns not in the model's dataset.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSfXDcwkLthEkA3pP571U5K0MjpafxN%2BkOfU3HrFP5aUqA%40mail.gmail.com.
