CODEWITHSUNDEEP
sqlruby-on-rails-3joinrelational-database
Nick Ginanto
Nick Ginanto

Reputation: 32120

How to write this SQL in a Rails way

*rewrote the entire question so it will be clearer..

This is how my tables look like (without the useless columns) I generated this with pgadmin

enter image description here

SELECT 
  notifications.id
FROM 
  activities, 
  notifications, 
  comments
WHERE 
  activities.trackable_id = comments.id AND
  notifications.activity_id = activities.id;

with pgadmin, this query gives the right result.

I don't know how to use includes (or maybe it is joins) to have 3 tables as the sql above.

my attempt of 

@notifications = current_user.notifications.includes(:activity, :comments).where("activities.trackable_id = comments.id AND notifications.activity_id = activities.id")

isn't fruitful

Upvotes: 0

Views: 75

Answers (1)

MurifoX
MurifoX

Reputation: 15089

By default when you pass ActiveRecord::Base#joins a named association, it will perform an INNER JOIN with the possible primary keys. As we can see in your model, the ids are not quite the default way, so you can distinguish which key you will be using to join the tables inside the joins method. Kinda something like this: 

@notifications = current_user.notifications.
  joins("INNER JOIN activities ON notifications.activity_id = activities.id").
  joins("INNER JOIN comments ON activities.trackable_id = comments.id")

Upvotes: 1

Related Questions