Getting desired info out of association join

[criegod]

I've been playing with this for a couple days... getting some thing to work (finally)... but perhaps not the right way. If so, i hope someone will tell me how it should be done. Anyway, same models as i posted before in Discussion #2224... i have a pattern to match one or more artists in my 'artists' table, and i want to get the artist name, album name, path for the album directory, and maybe the year and whatnot to flesh out the line displaying this info. What i've come up with is:

ds = Artist.select(Sequel[:artists][:name],
                    Sequel[:albums][:name],
                    Sequel[:albums][:path]).where(
  Sequel.like( Sequel[:artists][:name],
               "%#{artistPattern}%" )
).association_join(:albums)

The sequel 'qualify' ([][]) i just added in trying to get the info i want returned. It appears that the join causes the dataset returned to be a combination of artists and albums tables. What happens when i specify the select on the artist name, album name, album path, appears to be that all the album names are returned, followed by all the (corresponding) artist models (and i don't know what happened to the path info.), which is not what i want.

Here's my attempt to look at what the above returns:

puts ds.all.each {
  |o| puts "o.name: #{o.name}, o.class: #{o.class}" }

o.name: Christmas, o.class: Artist
o.name: High_Winds_White_Sky, o.class: Artist
<snip... there are 30 albums, in all>
o.name: Small_Source_of_Comfort, o.class: Artist [#29]
o.name: Crowing_Ignites, o.class: Artist [#30]
#Artist:0x00007f739790a0a8

Followed by 29 more lines similar to that last one with slightly different internal identifiers for the Artist (even though the artist is the same in all cases... Bruce Cockburn, as i used as the matching pattern 'Cockburn'.

A question relating to all this is how you can look into a model object. I often want to see what i've got and "puts o" or "puts o.inspect" often just gives me the model name (Artist or Album), and an identifier and i've not been able to find a reliable way to look into the thing. Note that even though, above, i'm doing a 'puts' on o.name and o.class, for the last 30 of the 60 items being returned, it's just printing out '#<Artist: ...> -- which i don't get at all how that can be.

As always, any help (thanks Jeremy) is appreciated.

[jeremyevans]

If you call all on a Artist dataset, you are going to get Artist instances. You probably want to use eager or eager_graph to eagerly load the albums for the artists that are matching. Since you aren't filtering or ordering on the albums, using eager makes more sense. If you want to have a custom selection, you can support that via an eager callback. A simple approach for what you want:

Artist.
  eager(:artists).
  where(Sequel.like(:name, "%#{artistPattern}%")).
  all do |artist|
    p artist.name
    p artist.albums.map(&:name)
  end

[criegod]

The last answer, i thought, would be a stepping-stone to what i really want... a list (dataset) of albums "owned" by the artists selected by a pattern. The example i was given shows how to print album fields (associated with the matched artist(s)), within a block. But i'd like a dataset returned, because i'm filtering albums based on year, then an album pattern (starting with the dataset returned by filtering by year), then taking that dataset as the start of filtering by artist. I wanted to feed my dataset from after the 2nd stage (year, album pattern), into the 3rd stage (filtering by artist) but the example given doesn't suggest any way (to me) to use that dataset. The first two stages were easy, because each operation returns an Album dataset. So, the basic functionality given:

if artistPattern
ds = Artist.eager(:albums).where(
Sequel.like(:name, "%#{artistPattern}%")).all

is supposedly supposed to be the artist(s)selected by the pattern, and all the albums associated with those artists... but in whatever sense is meant by saying that these things are "loaded". (As instructed, 'all', is called.) "loaded" doesn't seem to mean 'returned in the dataset', though:

puts "ds.count: #{ds.count}"
puts "ds: #{ds}, ds.class: #{ds.class}"

Output:

ds.count: 1
ds: [#<Artist @values={:id=>1237, :name=>"Bruce_Cockburn"}>], ds.class: Array

Where are the albums? Hidden behind the scenes by some magic that emerges to affect my doing an artist.albums association? I don't think i've seen the information, yet, that would allow me to understand how this is supposed to work. Examples are very helpful.

[jeremyevans]

The eagerly-loaded albums are stored inside the object. If you call albums on the object, then you'll see them. I'm not sure how much of Sequel's documentation you have read, but it does at least attempt to discuss these topics:

• https://sequel.jeremyevans.net/rdoc/files/README_rdoc.html#label-Associations
• https://sequel.jeremyevans.net/rdoc/files/doc/association_basics_rdoc.html
• https://sequel.jeremyevans.net/rdoc/files/doc/advanced_associations_rdoc.html

If you have additional questions after reviewing that documentation, please let me know.

[criegod]

I've gone over all those pages, and many others. They don't really explain very much, and the examples are more like examples of syntax rather than useful working code examples. Saying i can call "albums" on "the object" sounds useful but:

if artistPattern
   ds = Artist.eager(:albums).where(
      Sequel.like(:name, "%#{artistPattern}%")).all

puts "ds.count: #{ds.count}"
puts "ds: #{ds}, ds.class: #{ds.class}"
put "ds.albums: #{ds.albums}"

$ rand Cockburn
ds.count: 1
ds: [#<Artist @values={:id=>1237, :name=>"Bruce_Cockburn"}>], ds.class: Array
/usr/local/bin/rand:374:in processArgs': undefined method albums' for [#<Artist @values={:id=>1237, :name=>"Bruce_Cockburn"}>]:Array (NoMethodError)

put "ds.albums: #{ds.albums}"
                    ^^^^^^^
    from /usr/local/bin/rand:434:in `<main>'

Or trying this:

put "ds.albums: #{ds[:albums]}"

/usr/local/bin/rand:374:in `processArgs': no implicit conversion of Symbol into Integer (TypeError)

put "ds.albums: #{ds[:albums]}"
                     ^^^^^^^
    from /usr/local/bin/rand:434:in `<main>'

[criegod]

I don't know how to edit these posts. "put" is a typo, but "puts" gives the same output (with "put" replaced by "puts" in the output)..

[jeremyevans]

ds.albums doesn't work because ds is an array. You could use ds.each{|artist| p artist.albums}. Note that ds is a poor choice of variable name, because you are dealing with an array, not a dataset. You should probably use artists instead of ds.

Similarly ds[:artists] doesn't work because ds is an array, not a hash. Note that ds.each{|artist| p artist[:albums]} will not work, because Model#[] is used to access columns, not associations.

[criegod]

The 'put"'s, above, should be "puts" but it doesn't seem to matter. "puts" gives the same (except for displaying "put" rather than "puts") as i posted.

[criegod]

That's pretty simple... i just want the FROM to specify both tables involved:

music=> SELECT artists.name, albums.name, albums.year FROM albums,artists WHERE ((year >= 2017) AND (artists.id = albums.artist_id AND (artists.name LIKE '%Cockburn%')));
name | name | year
----------------+-----------------+------
Bruce_Cockburn | Bone_On_Bone | 2017
Bruce_Cockburn | Rarities | 2022
Bruce_Cockburn | O_Sun_O_Moon | 2023
Bruce_Cockburn | Crowing_Ignites | 2019
(4 rows)

[JonathanFrias]

DB[:albums]
  .join(:artists, id: :artist_id)
  .where(Sequel.like(Sequel[:artists][:name], '%Cockburn%'))
  .where { year >= 2017 }
  .select(Sequel[:artists][:name].as(:artist_name), 
          Sequel[:albums][:name].as(:album_name), 
          :year)

[jeremyevans]

A literal translation would be:

DB[:albums, :artists].
  select{[artists[:name], albums[:name], albums[:year]]}.
  where{year >= 2017}.
  where{{artists[:id] => albums[:artist_id]}}.
  where{Sequel.like(artists[:name], '%Cockburn%')}

However, you are going to want to alias the selected name columns to make sure the result set has unique column names, as in @JonathanFrias example, and it is more typical to joins instead of multiple FROM tables (also as in @JonathanFrias example).

[criegod]

Thank you. That's just what i needed. I was on the track (i was thinking that specifying multiple tables in the FROM clause might really be an inner join) but you provided the variety (2 where's and select at the end).

[JonathanFrias]

Technically, you specified a cross join. They used to be more common, but with the where clause provides enough information for the database to optimize to an equivalent inner join in this specific case

See: https://www.sqltutorial.org/sql-cross-join/