On Friday, January 4, 2019 at 10:22:31 AM UTC-8, Jeremy Evans wrote:
>
> On Friday, January 4, 2019 at 8:57:19 AM UTC-8, Janko Marohnić wrote:
>>
>> The following example:
>>
>> require "sequel"
>>
>> DB = Sequel.mock
>>
>> class Artist < Sequel::Model
>> one_to_many :albums, order: :created_at
>> end
>>
>> class Album < Sequel::Model
>> end
>>
>> puts Artist.association_join(:albums).sql
>>
>> prints out the following SQL:
>>
>> SELECT * FROM artists INNER JOIN albums ON (albums.artist_id = artists.id
>> )
>>
>> and I was hoping it would be
>>
>> SELECT * FROM artists INNER JOIN (SELECT * FROM albums ORDER BY
>> created_at) AS albums ON (albums.artist_id = artists.id)
>>
>> The scenario where I wanted this ORDER BY to be added was when adding a
>> `DISTINCT ON`, e.g. to JOIN only on the earliest albums for each artist:
>>
>> Artist.association_join(albums: -> (ds) { ds.distinct(:artist_id) })
>>
>> Is the order left out intentionally? If not, I could send a pull request
>> for #association_join to pick up the default :order.
>>
>
> Yes, this is intentional. association_join only adds the join, not any
> other changes made by the eager_graph code. association_join is basically
> a shortcut for manual calls to join.
>
I should add that in your particular example, if you use eager_graph, the
order used in the association is appended to the resulting dataset, it does
not join to a subquery and order the subquery:
puts Artist.eager_graph(:albums).sql
# SELECT * FROM artists LEFT OUTER JOIN albums ON (albums.artist_id =
artists.id) ORDER BY albums.created_at
puts Artist.order(:name).eager_graph(:albums).sql
# SELECT * FROM artists LEFT OUTER JOIN albums ON (albums.artist_id =
artists.id) ORDER BY name, albums.created_at
For one, from an SQL perspective, ordering a subquery without using
distinct/limit/something similar it is not required to affect the result.
The following query is allowed to return rows in an arbitrary order:
SELECT * FROM (SELECT * FROM table ORDER BY name) AS t1
Some databases (IIRC MSSQL) do not allow you to order subqueries that are
not limited to prevent you from making the assumption that the subquery
ordering will have an effect.
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 post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
