CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecordrails-activerecord
Andrew Schwartz
Andrew Schwartz

Reputation: 4657

How to unscope a where clause on a joined ActiveRecord model

On my model MediaFileAsset, I have the following added via an included lib:

default_scope ->{eager_load(:service_asset_core).where(service_asset_cores: {archived: false})}

Leaving aside reasons to not use default_scopes, is there a way to use unscope to remove this scope from a query?

So, for example, maybe someone passes me

assets = MediaFileAsset.where(custom_conditions)

and I want to count all of these IGNORING the archived field on service_asset_cores. If archived were a field of media_file_assets, I could use

assets.unscope(where: :archived).count

But that doesn't work here because archived is a field on ServiceAssetCore, not MediaFileAsset.

Using unscoped and unscope(:where) work to remove the archived condition, but these remove more than just that condition, which is not quite what I'm looking for. I believe this is why the unscope() method was added in the first place, and I'm trying to learn if it's possible to use it to remove query conditions on joined tables.

For reference:

2.2.2 > MediaFileAsset.unscoped.count
   (505.8ms)  SELECT COUNT(*) FROM `media_file_assets`
 => 3078951

And to unscope just the specific where: :archived clause, I have tried:

2.2.2 > MediaFileAsset.unscope(where: {service_asset_cores: :archived}).count
   (0.5ms)  SELECT COUNT(DISTINCT `media_file_assets`.`id`) FROM `media_file_assets` LEFT OUTER JOIN `service_asset_cores` ON `service_asset_cores`.`service_asset_id` = `media_file_assets`.`id` AND `service_asset_cores`.`service_asset_type` = 'MediaFileAsset' WHERE `service_asset_cores`.`archived` = 0
 => 10

2.2.2 > MediaFileAsset.all.merge(ActsAsServiceAsset::ServiceAssetCore.unscoped).count
   (0.5ms)  SELECT COUNT(DISTINCT `media_file_assets`.`id`) FROM `media_file_assets` LEFT OUTER JOIN `service_asset_cores` ON `service_asset_cores`.`service_asset_id` = `media_file_assets`.`id` AND `service_asset_cores`.`service_asset_type` = 'MediaFileAsset' WHERE `service_asset_cores`.`archived` = 0
 => 10

2.2.2 > MediaFileAsset.unscope(where: :archived).count
   (0.8ms)  SELECT COUNT(DISTINCT `media_file_assets`.`id`) FROM `media_file_assets` LEFT OUTER JOIN `service_asset_cores` ON `service_asset_cores`.`service_asset_id` = `media_file_assets`.`id` AND `service_asset_cores`.`service_asset_type` = 'MediaFileAsset' WHERE `service_asset_cores`.`archived` =
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1: SELECT COUNT(DISTINCT `media_file_assets`.`id`) FROM `media_file_assets` LEFT OUTER JOIN `service_asset_cores` ON `service_asset_cores`.`service_asset_id` = `media_file_assets`.`id` AND `service_asset_cores`.`service_asset_type` = 'MediaFileAsset' WHERE `service_asset_cores`.`archived` =
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1: SELECT COUNT(DISTINCT `media_file_assets`.`id`) FROM `media_file_assets` LEFT OUTER JOIN `service_asset_cores` ON `service_asset_cores`.`service_asset_id` = `media_file_assets`.`id` AND `service_asset_cores`.`service_asset_type` = 'MediaFileAsset' WHERE `service_asset_cores`.`archived` =

2.2.2 > MediaFileAsset.all.merge(ActsAsServiceAsset::ServiceAssetCore.unscope(where: :archived)).count
# output omitted, same error as pervious

That last two seem like a Rails (4.2.3) bug since the SQL generated is invalid. Maybe this is where I need to be but the bug is in my way. Is there another method I should be trying, or am I stuck with the horrors of the unavoidable default_scope?

Upvotes: 4

Views: 8826

Answers (2)

Marian13
Marian13

Reputation: 9248

Unscope column on specified table (Rails 6.1+)

Starting from Rails 6.1, it is possible to unscope only the column in the specified table.

posts = Post.joins(:comments).group(:"posts.hidden")
posts = posts.where("posts.hidden": false, "comments.hidden": false)

posts.count
# => { false => 10 }

# unscope both hidden columns
posts.unscope(where: :hidden).count
# => { false => 11, true => 1 }

# unscope only comments.hidden column
posts.unscope(where: :"comments.hidden").count
# => { false => 11 }

Here is a link to the corresponding PR.

So, I suppose, the following can solve your problem:

MediaFileAsset.unscope(where: :"service_asset_cores.archived").where(custom_conditions)

Upvotes: 1

voxobscuro
voxobscuro

Reputation: 2238

You're pretty close.

MediaFileAsset.unscoped returns an ActiveRecord::Relation, meaning you can call .count, .where, .includes, etc on it.

So the full query would be:

MediaFileAsset.unscoped.where(service_assset_cores: :archived).count

Or, you could define a named scope on MediaFileAsset called archived, and rescope it after you unscope it:

class MediaFileAsset < ActiveRecord::Base
  scope :archived, { where service_asset_cores: :archived }
  # ...
end


MediaFileAsset.unscoped.archived.count

  def self.without_default_scope
    klass = self.dup
    klass.default_scopes = []
    klass
  end

Upvotes: 0

Related Questions