Thanks, that mostly works, except it drops the `WHERE (("table2"."user_id"
= <id>)`
I can add a .where(user_id: ...) but I'm not sure how to specify that I
want the actual id of the user in this context
Sorry if the answer is obvious!
On Wednesday, July 6, 2022 at 12:56:13 PM UTC-5 Jeremy Evans wrote:
> 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/73cade86-c91e-4319-95d9-50200424d694n%40googlegroups.com.
