Hi Jeremy, thank you for the explanation, that makes sense. If the only thing that would happen is that the ORDER BY of the joined dataset wouldn't be applied, then I might argue that Sequel could still add it (like it does conditions) so that it works correctly if you use DISTINCT ON. But if that ORDER BY could cause errors in some databases without any limits, as you mentioned, then we certainly want to avoid it.
Kind regards, Janko On 4 Jan 2019, 19:31 +0100, Jeremy Evans <[email protected]>, wrote: > 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 a topic in the Google > Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/vJlG07_QKQM/unsubscribe. > To unsubscribe from this group and all its topics, 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. -- 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.
