Optimize final query by merging exists queries by their association ?

[jwoodrow]

Hi 👋 good work on the where_exists gem, I find it extremely useful. In my usecase I like to use it in my scopes to help avoid needing to use joins in a scope which might cause breakage when building certain OR queries for example.

I've noticed one thing that feels like a shame, it's that exist queries are all individual.

I might have these scopes for example

# using where_exists
scope :scope_one, -> { where_exists(:assoc, attr_one: 42)
scope :scope_two, -> { where_exists(:assoc, attr_two: nil)
# using joins
scope :scope_one, -> { joins(:assoc).where(assocs: { attr_one: 42 })
scope :scope_two, -> { joins(:assoc).where(assocs: { attr_two: nil })

If I do this Record.scope_one.scope_two.to_sql using each method

-- where_exists
((
  EXISTS (
    SELECT 1 FROM "assocs"
    WHERE ("records"."foreign_key" = "assocs"."primary_key")
    AND "assocs"."attr_one" = 42
  )
))
AND ((
  EXISTS (
    SELECT 1 FROM "assocs"
    WHERE ("records"."foreign_key" = "assocs"."primary_key")
    AND "assocs"."attr_two" IS NULL
  )
))
-- joins
INNER JOIN "assocs" ON "assocs"."primary_key" = "records"."foreign_key"
WHERE "assocs"."attr_one" = 42
AND "assocs"."attr_two" IS NULL

The join method merges both conditions which makes it so it doesnt look like "(FIRST CONDITION) AND (SECOND CONDITION)"

I tried looking at the code and it seems like you're not using arel to build your queries so I'm guessing it wouldn't be "easy" to make it so the where_exists could join on their association name like so ?

EXISTS (
  SELECT 1 FROM "assocs"
  WHERE "records"."foreign_key" = "assocs"."primary_key"
  AND "assocs"."attr_one" = 42
  AND "assocs"."attr_two" IS NULL
)

I feel like this might require deliving into rails' query builder but the best I've managed to do in the past is make a method that would build arel queries for JSONB columns but never actually something that messes with that. Open to discuss and try things.

PS: It might just be logs and the query planner might be optimizing the queries into a single one but it feels like it makes it harder de decipher logs when the combination of multiple exists look like that 🤔

[EugZol]

Hey, I don't think what you explain falls under where_exist's purpose 🤔

Rewriting the gem in Arel instead of string manipulation would be useful by itself, however I estimate it would require a lot of effort on part with rewriting it from scratch.

[jwoodrow]

Sure, it was more of a question anyways but thanks for taking some time to reply 👍