Posted over 10 years ago. Visible to the public.

Take care when joining and selecting on scopes

Occasionally some complex query must be processed on the database because building thousands of Ruby objects is impracticable.

Many times you would use scope options, like this:

Copy
users = User.scoped( :joins => 'INNER JOIN orders joined_orders ON users.id = joined_orders.user_id', :conditions => [ 'joined_orders.date BETWEEN ? AND ?', start_date, end_date ], :select => '*, SUM(joined_orders.amount) AS amount_sum', :group => 'users.id' )

You get ActiveRecord objects and you can ask each of them about its amount_sum -- which is great, but your User objects may not have the actual ID from their row in the users table but one of the joined table.

Ensure that you get the correct user IDs by selecting only from the users table:
users = User.scoped(
:joins => 'INNER JOIN orders joined_orders ON users.id = joined_orders.user_id',
:conditions => [ 'joined_orders.date BETWEEN ? AND ?', start_date, end_date ],
:select => 'users., SUM(joined_orders.amount) AS amount_sum', # note the "users." instead of "*" above
:group => 'users.id'
)

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Owner of this card:

Avatar
Arne Hartherz
Last edit:
almost 10 years ago
Keywords:
mysql, primary, key
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more