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.