Shrolox Shrolox - 1 year ago 64
Ruby Question

"Missing FROM-clause entry" when using nested merges with ActiveRecord

I'm trying to use ActiveRecord queries to select devices belonging to specific users in my DB (devices are just models containing push notifications keys), regarding how many orders users made, or other things.

(In all models I removed non relevant or private stuff)

Here is my Device model:

class Device < ActiveRecord::Base
belongs_to :user

User model:

class User < ActiveRecord::Base
has_many :order
has_one :device

Order model:

class Order < ActiveRecord::Base
belongs_to :user
belongs_to :order_status
scope :valid, -> { joins(:order_status).merge(OrderStatus.valid) }

OrderStatus model:

class OrderStatus < ActiveRecord::Base
has_many :orders
scope :valid, -> { where.not(name: "processing") }

So, with all these, i'm trying to get Devices belongings to users who have made more than x valid orders.
Here is where i am:

Device.all.joins(:user).merge(User.joins(:order).merge(Order.valid).group("").having("count( > ?", [NB_ORDERS]))

This generates this request:

: SELECT "devices".* FROM "devices" INNER JOIN "users" ON "users"."id"
= "devices"."user_id" LEFT OUTER JOIN "order_statuses" ON "order_statuses"."id" = "orders"."order_status_id" LEFT OUTER JOIN "orders" ON "orders"."user_id" = "users"."id" WHERE ("order_statuses"."name" != $1) GROUP BY HAVING count( > 10

And gives me this error:

Hirb Error: PG::UndefinedTable: ERROR: missing FROM-clause entry for
table "orders" LINE 1: ... JOIN "order_statuses" ON
"order_statuses"."id" = "orders"."...

What i really don't understand is that when i just do

User.joins(:order).merge(Order.valid).group("").having("count( > ?", [NB_ORDERS])

It gives me the right result. It seems like nesting merges messes up with Postgres.

is there a way to solve this problem ? (I have a great amount of records so using SQL/ActiveRecord is essential)

Answer Source

Try to use merge as Proc, like

Device.joins(:user).merge(-> { User.joins(:order)... })

Sorry, right not I cannot give any explanations because I don't know much about merge method.
I just read the docs and saw Proc option.

Maybe soon I will dive into the problem and be able to help you with something more than this.