Hello, I'm trying to produce this query with Sequel, that would then run on PostgreSQL: SELECT o.id, coalesce(cg.price, o.price) price FROM offers o LEFT JOIN country_groups cg ON cg.offer_id = o.id AND cg.country_codes @> '{"US"}'
I understand I can use an array of conditions, as follows: db[:offers].left_join(:country_groups, [[:offer_id, :id], [??]] ) But then I don't know how to express the second condition, which normally would be like this in a filter context: Sequel.pg_array_op(:country_codes).contains(Sequel.pg_array(['US'], :varchar )) Alternatively, I've tried using a subquery: filtered_countries = db[:country_groups].where(Sequel.pg_array_op(: country_codes).contains(Sequel.pg_array(['US'], :varchar))) db[:offers].left_join(filtered_countries, offer_id: :id).select { coalesce( country_groups[:price], offers[:price]) } The issue then is that the country_groups referenced inside coalesce is not part of the FROM clause, so I get: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "country_groups" Tried using the filtered dataset inside coalesce db[:offers].left_join(filtered_countries, offer_id: :id).select { coalesce( filtered_countries[:price], offers[:price]) } and from the error I get, this seems just totally wrong: Sequel::DatabaseError: PG::DatatypeMismatch: ERROR: argument of AND must be type boolean, not type numeric LINE 1: ... (("country_codes" @> ARRAY['DE']::varchar[]) AND "price") LIM... How do I achieve any of this without resorting to raw queries? Thanks in advance for any help or pointers, Best Marcelo -- 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 sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/2b594f9f-0086-491e-b2cf-e9ae2f071910%40googlegroups.com.