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.
