Rails Named_Scopes with Joins

rails scope and joins

I am assuming that you mean this:
Car has radio_id, User has car_id,
since a radio has many cars and car has one user. The table with the foreign key always is on the belongs_to end of the relationship.

Without really knowing the structure you're looking for, something like the following should work:

scope :with_cd_player, joins(:cars).where('cars.radio_id is not null')

if there is a category column on the radio, the following would work.

scope :with_cd_player, joins(:car => :radio).where('cars.radio_id is not null').where("radios.category = 'cd_player'")

For Rails Version >= 4:

scope :with_cd_player, -> { joins(:cars).where.not(cars: { radio_id: nil }) }

ruby on rails scope joins table

The correct syntax should be:

scope :ind, -> { joins(:country).where("countries.name like %india%") }

The name in the joins should be the name of the association, not the table from the doc:

Active Record lets you use the names of the associations defined on
the model as a shortcut for specifying JOIN clause for those
associations when using the joins method.

Scope syntax without callable object is deprecated:

DEPRECATION WARNING: Using #scope without passing a callable object is
deprecated. For example scope :red, where(color: 'red') should be
changed to scope :red, -> { where(color: 'red') }. There are
numerous gotchas in the former usage and it makes the implementation
more complicated and buggy. (If you prefer, you can just define a
class method named self.red.).

Rails Scope with Multiple Joins and Parameters

Here's how I was able to get it working:

scope :within_with_cat, -> (latitude, longitude){ 
    joins(affiliate: [ :affiliate_plan])
        .where(['(ST_Distance(lonlat, \'POINT(%f %f)\') < (affiliate_plans.radius_miles * 1609.34))', longitude, latitude])
}

One of my biggest areas of confusion was that I didn't need to join the table for the model the scope is in.

I discovered that if I called Service.within_with_cat(lat, lon).any? I would receive the error messages needed for debugging. When I called Service.within_with_cat(lat, lon) I would only receive a relation with no errors.

Rails named_scopes with joins

The problem is that "SELECT *" - the query picks up all the columns from clips, series, and shows, in that order. Each table has an id column, and result in conflicts between the named columns in the results. The last id column pulled back (from shows) overrides the one you want. You should be using a :select option with the :joins, like:

named_scope :visible, {
  :select => "episodes.*",
  :joins => "INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id", 
  :conditions=>"shows.visible = 1 AND clips.owner_type = 'Series' "
}

Scope with joins in Rails 4.2

The scope body needs to be callable. (ArgumentError)

This should work

scope :with_comments, -> { joins(:comments) }

A nice explanation here

ActiveRecord OR query with two scopes in SQL where one scope has a join

Ok.
For Rails 4, the where-or gem provides a fix for generating or queries as in rails 5, however if you pass the scopes above you end up with a

 ArgumentError: Relation passed to #or must be structurally compatible

You can use the following:

scope :email_somewhere, -> { association_has_email.or(Model1.joins(:model2).description_has_email }

SELECT \"model1s\".* FROM \"model1s\" INNER JOIN \"model2s\" ON \"model2s\".\"id\" = \"model1s\".\"model2_id\" WHERE ((\"model2s\".\"email\" IS NOT NULL) OR (description ~* '[[:<:]][A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}[[:>:]]'))

Works, but it will exclude anything that has an email in the description but doesn't have a model2, because it uses an inner join.

But by using includes, you can get the desired result.

scope :association_has_email, -> { includes(:model2).where.not(model2s:{email:nil}) }
scope :description_has_email, -> { where("description ~* ?", email_regex) }

Means you can use

scope :email_somewhere, -> { association_has_email.or(Model1.includes(:model2).description_has_email }

The difference is the SQL pulls all of the attributes from model1, and then adds a left outer join for the model2 query.

SELECT [...all the model1 attributes...] LEFT OUTER JOIN \"model2s\" ON \"model2s\".\"id\" = \"model1s\".\"model2_id\" WHERE ((\"model2s\".\"email\" IS NOT NULL) OR (description ~* '[[:<:]][A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}[[:>:]]'))

Irritating if you really needed to use an inner join for the first one, but works for me.

Unique join table reference inside model scope

Sadly, ActiveRecord has no built-in way to specify the alias used when joining an association. Using merge to try merging the two scopes also fails as the condition is overridden.

3 solutions:

1. Use Arel to alias a joins, but that's a bit hard to read, and you still need to repeat the association definition for payment_gateways and coupon_codes

2. Join directly in SQL:

   scope :payment_gateway, -> (pg) { joins(<<-SQL
INNER JOIN log_details payment_gateways
ON payment_gateways.log_id = logs.id
AND payment_gateways.key = 'payment_gateway'
AND payment_gateways.value = #{connection.quote(pg)}
SQL
) if pg.present? }

   But you need to add manually the conditions already defined in the associations

3. Finally, my favorite, a solution that sticks to ActiveRecord:

   scope :payment_gateway, -> (pg) do
where(id: unscoped.joins(:payment_gateways).where(log_details: {value: pg})) if pg.present?
end

   scope :coupon_code, -> (cc) do
where(id: unscoped.joins(:coupon_codes).where(log_details: {value: cc})) if cc.present?
end

Gotcha #1: if you use Rails < 5.2, you might need to use class methods instead of scopes.

Gotcha #2: Solution #3 might be less performant than #2, make sure to EXPLAIN ANALYZE to see the difference.

Rails ActiveRecord model scope with joins on has_many associations

Rails 5 introduced left_outer_joins method that can be used

scope :awaiting_quote, -> { joins(:surveys).left_outer_joins(:quotes).where('yada yada') }

---

Related Topics