Rails | Add condition in join query

Question

Is it possible, to add condition in join query?

For example I want to build next query:

select * from clients
left join comments on comments.client_id = clients.id and comments.type = 1

Client.joins(:comments).all generates only:

select * from clients
left join comments on comments.client_id = clients.id

PS. Client.joins("LEFT JOIN comments on comments.client_id = clients.id and comment.type = 1") isn't nice.

Answer 1

You can do:

Client.left_outer_joins(:comments)
  .where(comments: { id: nil })
  .or(Client.left_outer_joins(:comments)
    .where.not(comments: { id: nil })
    .where(comments: { type: 1 }))

what gives you something equivalent to what you want:

SELECT "clients".* 
FROM "clients" 
  LEFT OUTER JOIN "comments" 
    ON "comments"."client_id" = "clients"."id" 
WHERE "comments"."id" IS NULL 
  OR ("comments"."id" IS NOT NULL) AND "comments"."type" = 1

UPDATE

Actually, this does not work because rails close the parenthesis leaving outside the evaluation of type.

UPDATE 2

If yours comment types are few and is not probable its values will change, you can solve it with this way:

class Client < ApplicationRecord
  has_many :comments
  has_many :new_comments,  -> { where comments: { type: 1 } }, class_name: Comment
  has_many :spam_comments, -> { where comments: { type: 2 } }, class_name: Comment
end

class Comment < ApplicationRecord
  belongs_to :client
end

With this new relationships in your model now you can do:

Client.left_joins(:comments)

gives:

SELECT "clients".*
FROM "clients"
  LEFT OUTER JOIN "comments" 
    ON "comments"."client_id" = "clients"."id"


Client.left_joins(:new_comments)

gives:

SELECT "clients".*
FROM "clients"
  LEFT OUTER JOIN "comments"
    ON "comments"."client_id" = "clients"."id" AND "comments"."type" = ?  
/*[["type", 1]]*/


Client.left_joins(:spam_comments)

gives the same query:

SELECT "clients".*
FROM "clients"
  LEFT OUTER JOIN "comments"
    ON "comments"."client_id" = "clients"."id" AND "comments"."type" = ?  
/*[["type", 2]]*/


Client.left_joins(:new_comments).where name: 'Luis'

gives:

SELECT "clients".*
FROM "clients"
  LEFT OUTER JOIN "comments"
    ON "comments"."client_id" = "clients"."id" AND "comments"."type" = ?
WHERE "clients"."name" = ?
/*[["type", 1], ["name", "Luis"]]*/

NOTE:

I try to use a parameter in the original has_many relationship like...

has_many :comments, -> (t) { where comments: { type: t } }

but this give me an error:

ArgumentError: The association scope 'comments' is instance dependent (the scope block takes an argument). Preloading instance dependent scopes is not supported.