Filter query where all associations share the same value

Questions / Help
,
1

Hi, I have the following schemas that I’ve simplified.

  schema "inquiries" do
      has_many :offers, Offer
  end
  schema "offers" do
     belongs_to :inquiry, Inquiry
     field :status, :string, default: "pending" # pending, won, lost
  end

And I’m trying to construct a query where i find all inquiries where all the associated offers are of a certain set of statuses. For example it is useful for me to find a inquiry where all its offers have status “lost”. And somewhat related to also find a inquiry what any of its offers have status “won”.

My current attempt that I’m stuck with:

from i in Inquiry,
  left_join: o in assoc(i, :offers),
  group_by: i.id,
  group_by: o.status,
  having:
    count(o.id) ==
      fragment(
        "SELECT count(?) WHERE ? = ANY(?)",
        o.id,
        o.status,
        ^filter
      )

But if i have:
1 inquiry with 2 offers, where offer 1 is won and offer 2 is lost. and call my query with filter being ["won"] It doesn’t filter it and I’m at the point where I feel completely lost on how to approach the problem.

Any suggestion is welcome as well as tips on literature that teaches this kind of SQL queries/better understanding of relational databases.

Thanks!

2

Postgres has a bool_and(expression) and bool_or(expression) aggregate functions that you can use in the having clause.

Something like (untested):

from i in Inquiry,
  left_join: o in assoc(i, :offers),
  group_by: i.id,
  having: fragment("bool_and(? = 'won')", o.status)
2 Likes
3

Thank you! That did the trick. I guess I should read more of the postgres documentation :slightly_smiling_face:

For anyone stumbling upon this issue the final snippet that I used was:

        from i in Inquiry,
          left_join: o in assoc(i, :offers),
          group_by: i.id,
          having: fragment("bool_and(? = ANY(?))", o.status, ^filter)

Where filter is a list of strings.

2 Likes