Ruby - IF or OR condition on a JOINS statement

Question

How can I specify a OR condition on a joins statement? Basically I don't want the joins for where(:tenant_id => User.current_tenant. If I try OR condition I get the following error.

I get the following error.

Relation passed to #or must be structurally compatible. Incompatible values: [:joins] [catalog/explorer]

  scope :public_service_templates,                  ->         { joins(:miq_user_roles).where(miq_user_roles: { settings: nil}).or(where(:tenant_id => User.current_tenant)) }

Could you write down the pure SQL query you want AR to generate? It's a good way to see basically if it's possible in SQL first of all. — brcebn, Jun 10 '22 at 11:28
No `Users` is part of a different model. The scope is on `service_templates` model. `service_templates` has access to `Users` model. If I remove the `OR` and just use `where` condition then the filter works but I want it to be a `OR` condition. — user630702, Jun 10 '22 at 12:02

score 0 · Answer 1 · answered Jun 10 '22 at 11:49

0

In rails 6, you can do the following:

scope :first_query, -> { joins(:miq_user_roles).where(miq_user_roles: { settings: nil }) }

scope :second_query, -> { where(:tenant_id => User.current_tenant)) }

scope :first_query_or_second_query, -> { first_query.or(self.second_query) }

Hope it helps

answered Jun 10 '22 at 11:49

AbdUlRahman Shawareb

161
3
8

Is there no way to add OR condition directly on the first query? – user630702 Jun 10 '22 at 11:53
Didn't try it before tbh, but this one could be a good reference and you can try the multiple solutions there [link](https://stackoverflow.com/questions/3684311/how-to-chain-scope-queries-with-or-instead-of-and) @JohnDoe – AbdUlRahman Shawareb Jun 10 '22 at 11:56

engineersmnky · Answer 2 · 2022-06-10T13:50:11.713

The title says "IF" or "OR" so I am a bit unclear if you are looking for both templates where "miq_user_roles"."setttings" IS NULL OR "service_template"."tenant_id" = SOME_ID or if you are looking for "miq_user_roles"."setttings" IS NULL but if that is empty then return "service_template"."tenant_id" = SOME_ID.

Assumptions

You are looking for the former combined OR condition
The reasoning is that where(:tenant_id => User.current_tenant) may not have a relationship with MiqUserRole and thus the join prohibits the return of the appropriate values.

If so just use left_joins instead of joins.

This will create an outer join and will make this part (miq_user_roles: { settings: nil}) of the condition non impactful on this part where(:tenant_id => User.current_tenant)); however you will still have to add the left_joins to the second condition for structural compatibility. e.g.

scope :public_service_templates,-> { 
  left_joins(:miq_user_roles)
   .where(miq_user_roles: { settings: nil})
   .or(left_joins(:miq_user_roles)
       .where(:tenant_id => User.current_tenant))
}

Alternatively you could use Arel for the condition which will allow the OR to be part of the single where clause (without needing to merge scopes) e.g.

scope :public_service_templates,-> {
  left_joins(:miq_user_roles)
    .where(
       MiqUserRole.arel_table[:settings].eq(nil).or(
         arel_table[:tenant_id].eq(User.current_tenant.id)
       )
    )
}

Additionally not sure what User.current_tenant returns. Depending on implementation you need to pass an instance of User to this call e.g.

scope :public_service_templates,->(user) {#...

then you would call as ServiceTemplate.public_service_templates(user)

Ruby - IF or OR condition on a JOINS statement

2 Answers2