CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Alex Antonov
Alex Antonov

Reputation: 15216

Rails can't understand association when querying using activerecord

I have a models, Task, Taskable & Supply. They have this associations relationship.

class Task < AR::Base
  has_one :taskable, inverse_of: :task, autosave: true
  enum status: { :awaiting, :restarting, ... }

class Taskable < AR::Base
  belongs_to :task, inverse_of: :taskable, autosave: true
  has_many :supplies

class Supply < AR::Base
  belongs_to :taskable

I need to find supplies, which are connected to tasks with some enum value. Here's how I construct a query:

Supply.where(
  taskable: {
    task: {
      status: [ :awaiting, :starting ]
    }
  })

But I catch an exception:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "taskable"
LINE 1: ...upplies" WHERE "supplies"."component_id" = $1 AND "taskable"...
: SELECT  "supplies".* FROM "supplies" WHERE "supplies"."component_id" =  $1 AND "taskable"."task_id" = '---
:status:
- :awaiting
- :starting
'  ORDER BY "supplies"."id" ASC LIMIT 1
(0.1ms)  ROLLBACK
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "taskable"
LINE 1: ...upplies" WHERE "supplies"."component_id" = $1 AND "taskable"...                                                             ^
: SELECT  "supplies".* FROM "supplies" WHERE "supplies"."component_id" = $1 AND "taskable"."task_id" = '---
:status:
- :awaiting
- :starting
'  ORDER BY "supplies"."id" ASC LIMIT 1

How can I query exactly what I want w/o exceptions?

UPDATED

db/schema.rb

create_table "tasks", force: :cascade do |t|
  t.string   "title"
  t.datetime "created_at",      null: false
  t.datetime "updated_at",      null: false
  t.integer  "responsible_id",  null: false
  t.integer  "status",          null: false
end

create_table "taskables", force: :cascade do |t|
  t.integer  "task_id",    null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

create_table "supplies", force: :cascade do |t|
  t.integer  "taskable_id",  null: false
  t.datetime "expired_at"
  t.datetime "created_at",   null: false
  t.datetime "updated_at",   null: false
end

Upvotes: 0

Views: 273

Answers (2)

Glupo
Glupo

Reputation: 529

As far as I know where cannot go deep through relations. What it should look like from my point of view is:

class Task < AR::Base
  has_one :taskable, inverse_of: :task, autosave: true
  enum status: { :awaiting, :restarting, ... }
class Taskable < AR::Base
  belongs_to :task, inverse_of: :taskable, autosave: true
  has_many :supplies
class Supply < AR::Base
  belongs_to :taskable
  has_many :supplies, through: :taskable

And to get what you want you'll be able to use: Supply.joins(:tasks).where(tasks: {status: [:awaiting, :starting]})

Also pay attention to status values in where clause. If you are using rails 4.1 you'll have to pass numeric values of statuses.

UPDATED

Or the simpler way without has_many :through

Supply.joins(taskable: :tasks).where(tasks: {status: [:awaiting, :starting]})

Upvotes: 1

Frost
Frost

Reputation: 11977

You are getting the PG::UndefinedTable error because when querying on where-clauses on associations, you need to specify the table names, and not the association name.

This might give you a better result:

Supply.where(
  taskables: {
    tasks: { 
      status: [:awaiting, :starting]
    }
  }
)

You might also have to include or join in the actual associations, depending on whether you only want the join query, or if you are looking to preload the associations. Use .includes for preloading, and .joins for just the join query.

Supply.includes(taskable: :task).where(...)

or

Supply.joins(taskable: :task).where(...)

EDIT: thanks @Pavling for mentioning the difference between .joins and .includes.

Upvotes: 1

Related Questions