Left Outer Joins, ORs, and Rails

Created: 2020-02-17, Last Updated: 2023-02-04

I'm sure there is a better way to do this, but I needed a way to return items that either belonged to a user or were "global" (i.e. not owned by anyone). In this case there were three tables, users, things_users, and things. Together they formed a many to many relationship, and a thing could be owned by a user, some users, or none. In the last case it means that it was a general thing that everyone could see.

Anyway, my sql for something like this would look like

select things.*
from things
left outer join things_users on things_users.thing_id = things.id
left outer join users on users.id = things_users.user_id
where users.id = '1234' or users.id is null

The left outer join is needed because its results will include records in things that don't have a corresponding record in things_users or users. An inner join will exclude those records. Anyway, the way I figured out how to trick activerecord into doing this was by passing in a string with the OR conditions into the where statement, so my ruby looks something like this.

 scope :things_for, ->(user) {
    left_outer_joins(:users)
    .where("users.id = ? OR users.id is null", user.id)
  }

I couldn't figure out how to use the or method in rails, so maybe there is a different way, but the or method kept complaining the the Relation passed to #or must be structurally compatible. Anyway this way works and amazingly it worked with pg_search_scope, but I have a feeling it won't scale very well.

Back