Kenn Kenn - 1 month ago 8
Ruby Question

Creating ActiveRecord scope with multiple conditionals

I have a Rails application with a number of Products, some of which are associated with an Issue model. Some of these products have an issue_id (so an Issue

has_many
products) and some do not. The products without an issue ID are a new addition I'm working on.

I previously had a named scope so that I can list products using
Product.published
, which looks like this:

scope :published, -> {
joins(:issue).reorder('products.created_at ASC, products.number ASC')
.where('products.status = ?', Product.statuses[:available])
.where('issues.status = ?', Issue.statuses[:published])
}


The result of this is that I can find only products that are associated with a published issue (think magazine issue).

I'm now adding products that will not be associated with a particular issue but will still have a draft/available state. The above scope does not find these products, as it looks for an issue_id that does not exist.

I thought I could modify the scope like this, adding the
OR issue_id IS NULL
part in the last line:

scope :published, -> {
joins(:issue).reorder('products.created_at ASC, products.number ASC')
.where('products.status = ?', Product.statuses[:available])
.where('issues.status = ? OR issue_id IS NULL', Issue.statuses[:published])
}


But this doesn't work. I still only get 'available' products associated with a 'published' issue. The products without an issue_id are not included in the returned collection.

(There is a window in which a product will be set to available before its associated issue is published, so for these situations I do need to check the status of both records.)

Here's the SQL generated by the above (wrapped for readability):

pry(main)> Product.published.to_sql
=> "SELECT `products`.* FROM `products` INNER JOIN `issues` ON `issues`.`id` =
`products`.`issue_id` WHERE (products.status = 1) AND (issues.status = 1 OR
issue_id IS NULL) ORDER BY products.created_at ASC, products.number ASC"


I've already created a Product class method that takes an argument as an alternate approach but doesn't work in all cases because I'm often looking up a product based on the ID without knowing in advance whether there's an Issue association or not (eg, for the product's show view).

Also,
Product.published
is nice and concise and the alternative is to load all published products (eg,
Product.where(:status => :published)
) and then iterate through to remove those associated with a not-yet-published issue in a second operation.

I feel like there's something I'm not quite grasping about doing more complex queries within a scope. My ideal outcome is a modified scope that can return available products, both with and without an issue, and without supplying an argument.

Is this possible, or should I resign myself to finding an alternate approach now that I'm adding these unassociated products?

Answer

The problem is that you are using joins(:issue). That method does an INNER JOIN between products and issues tables and discards all the products that doesn't have an issue_id. Maybe you could use LEFT JOIN so you can keep all the products regardless they have an issue.

scope :published, -> {
    joins('LEFT JOIN issues ON issues.id = products.issue_id')
    .select('products.*')
    .reorder('products.created_at ASC, products.number ASC')
    .where('products.status = ?', Product.statuses[:available])
    .where('issues.status = ? OR products.issue_id IS NULL', Issue.statuses[:published])
}
Comments