Nikita Miloserdov Nikita Miloserdov - 16 days ago 9
Ruby Question

Active Record doesn't update collection when there is joins and includes in query

Hello I've a problem with my query.
There are my models below:

class Owner
has_many :busiensses
has_many :adverts
end

class Business
belongs_to :owner
end

class Advert
belongs_to :owner
end


When I make this query everything is okay and it returns right collection full of needed objects:

Owner.joins(:adverts).includes(:businesses)
.where(businesses: {owner_id: nil})


But when I add to query update it raises error

Owner.joins(:adverts).includes(:businesses)
.where(businesses: {owner_id: nil})
.update_all(status: 'sth')


Error:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "businesses"


Where is the problem? I bet this error from SQL and it raised when you forget add sth to FROM statement and that sth needed in further conditions, but where in AR i forgot to add it?

Vau Vau
Answer
Owner.joins(:adverts)
 .includes(:businesses)
 .where(businesses: {owner_id: 1})
 .update_all(name: "blaaaaa") 

This statement translates into this query:

UPDATE "owners" 
SET "name" = 'blaaaaa' 
FROM "businesses" /* missed this */
WHERE "owners"."id" IN 
 (SELECT "owners"."id" FROM "owners" 
 INNER JOIN "adverts" 
 ON "adverts"."owner_id" = "owners"."id" 
 WHERE "businesses"."owner_id" = 1)

You miss the "FROM 'bussinesses'" which causes the error:

missing FROM-clause entry for table "businesses"

My solution is to use joins instead of using includes. It works fine in my machine.

Owner.joins(:adverts)
 .joins(:businesses)
 .where(businesses: {owner_id: 1})
 .update_all(name: "blaaaaa")

=> 1