On Wed, Jul 6, 2022 at 10:38 AM Matt Culpepper <[email protected]> wrote:
> Hey Jeremy, I'm trying to set up a relationship in a model that generates
> SQL like the following join with an OR between the conditions.
>
> SELECT * FROM table1 INNER JOIN table2 ON (("table2"."resource_id" =
> "table1"."an_id") OR ("table2"."resource_id" = "table1"."another_id"))
> WHERE (("table2"."user_id" = <id>)
>
> This is the closest I've come:
>
> User.many_to_many(:table1, join_table: :table2, right_key: [:resource_id,
> :resource_id], right_primary_key: [:an_id, :another_id])
>
> However, this generates an AND
>
> SELECT * FROM table1 INNER JOIN table2 ON (("table2"."resource_id" =
> "table1"."an_id") AND ("table2"."resource_id" = "table1"."another_id"))
> WHERE (("table2"."user_id" = <id>)
>
> Any ideas on how I can generate the query with the OR?
>
You can use the :dataset option to specify an arbitrary dataset to use.
Something like this may work:
User.many_to_many(:table1,
join_table: :table2,
right_key: [:resource_id, :resource_id],
right_primary_key: [:an_id, :another_id],
dataset: lambda{|r| r.associated_class.join(:table2,
Sequel.expr{(table2[:resource_id] =~ table1[:an_id]) |
(table2[:resource_id] =~ table1[:another_id])})}
)
You could also use the following for IN instead of OR:
lambda{|r| r.associated_class.join(:table2, {:resource_id=>[:an_id,
:another_id]}, :qualify=>:deep)}
Thanks,
Jeremy
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSeV07JRCwDzv1CXbbs9uM22Pab4yfT16udv2dzoN6LDxg%40mail.gmail.com.
