Ordering by associated record id means you need to select one associated record id (of potentially many) per original table row, so wouldn't you need to use sub-selects? e.g. select member.*, (select max(mif.id) from mi left outer join mif where mi.member_id = member.id) as max_mif_id order by max_mif_if ;
On Thursday, July 29, 2021 at 10:48:14 AM UTC-4 Jeremy Evans wrote: > On Thu, Jul 29, 2021 at 6:11 AM Michal Macejko <[email protected]> > wrote: > >> Hi all, >> >> here's a problem of mine. Member has many MemberIntegration, >> MemberIntegration has many MemberIntegrationField >> >> I need to load paginated members and order id by a >> MemberIntegrationField#value. >> >> In the database I have 100 members. >> >> ActiveRecord: >> >> ``` >> Member.includes(member_integrations: [:member_integration_fields]).order(' >> member_integration_fields.id').to_a.count >> ``` >> >> uses `LEFT OUTER JOIN` and it returns 100 members. >> >> Important thing to know is when I run the plain SQL in the pgAdmin, it >> returns `6XX` members, depends on the associations. It gives me an idea >> that ActiveRecord somehow deletes those duplicates. Not sure how yet. >> >> What would be the sequel alternative for that? >> >> I've tried `eager` but that does not allow me order by child attribute, >> as children associations will be loaded in another query. >> >> I've tried `eager_graph` but I ends up with `6XX` members. >> >> Any idea how to deal with such a query? Thank you >> > > It's hard to provide a definitive answer here, since you didn't provide a > self contained example. When you were using eager_graph, did you call #all > on the dataset, or #to_a as in the above ActiveRecord example? #all will > do eager loading and combine rows, and #to_a will not. If that isn't the > issue, can you post a self contained example showing your problem? > > 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/9891fe74-56e2-4b1a-a68a-0e6d6c698d62n%40googlegroups.com.
