CODEWITHSUNDEEP
sqlruby-on-railspostgresql
MatthewK
MatthewK

Reputation: 63

SQL complicated query with joins

I have problem with one query. Let me explain what I want: For the sake of bravity let's say that I have three tables:

-Offers

-Ratings

-Users

Now what I want to do is to create SQL query:

  1. I want Offers to be listed with all its fields and additional temporary column that IS NOT storred anywhere called AverageUserScore.

  2. This AverageUserScore is product of grabbing all offers, belonging to particular user and then grabbing all ratings belonging to these offers and then evaluating those ratings average - this average score is AverageUserScore.

To explain it even further, I need this query for Ruby on Rails application. In the browser inside application you can see all offers of other users , with AverageUserScore at the very end, as the last column.

Associations:

  1. Offer has many ratings
  2. Offer belongs to user
  3. Rating belongs to offer
  4. User has many offers

Upvotes: 0

Views: 68

Answers (1)

D-side
D-side

Reputation: 9485

Assumptions made:

  • You actually have a numeric column (of any type that SQL's AVG is fine with) in your Rating model. I'm using a column ratings.rating in my examples.
  • AverageUserScore is unconventional, so average_user_score is better.
  • You don't mind not getting users that have no offers: average rating is not clearly defined for them anyway.
  • You don't deviate from Rails' conventions far enough to have a primary key other than id.

Displaying offers for each user is a straightforward task: in a loop of @users.each do |user|, you can do user.offers.each do |offer| and be set. The only problem here is that it will execute a separate query for every user. Not good.

The "fetching offers" part is a standard N+1 counter seen even in the guides.

@users = User.includes(:offers).all

The interesting part here is only getting the averages.

For that I'm going to use Arel. It's already part of Rails, ActiveRecord is built on top of it, so you don't need to install anything extra.

You should be able to do a join like this:

User.joins(offers: :ratings)

And this won't get you anything interesting (apart from filtering users that have no offers). Inside though, you'll get a huge set of every rating joined with its corresponding offer and that offer's user. Since we're taking averages per-user we need to group by users.id, effectively making one entry per one users.id value. That is, one per user. A list of users, yes!

Let's stop for a second and make some assignments to make Arel-related code prettier. In fact, we only need two:

users   =   User.arel_table
ratings = Rating.arel_table

Okay. So. We need to get a list of users (all fields), and for each user fetch an average value seen on his offers' ratings' rating field. So let's compose these SQL expressions:

# users.*
user_fields = users[Arel.star] # Arel.star is a portable SQL "wildcard"
# AVG(ratings.rating) AS average_user_score
average_user_score = ratings[:rating].average.as('average_user_score')

All set. Ready for the final query:

User.includes(:offers) # N+1 counteraction
    .joins(offers: :ratings) # dat join
    .select(user_fields, average_user_score) # fields we need
    .group(users[:id]) # grouping to only get one row per user

Upvotes: 2

Related Questions