CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
Mark
Mark

Reputation: 6445

Rails - joining active record relations

I'm trying to follow through https://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-the-joined-tables

I have a class, ImageCollection, that has a status which can be 'active'. This class belongs to one Image, which has an attribute called 'workflow_state'.

I have the following that works but takes a few seconds to execute:

def self.published
  where(status: STATUS_ACTIVE).select { |x| x.image.workflow_state == Publishable::Meta.published_key }
end

The following does not work:

scope :published, lambda {
  where(status: STATUS_ACTIVE).joins(:image).where(
    'image.workflow_state = ?', Publishable::Meta.published_key
  )
}

And returns:

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'image.workflow_state' in 'where clause': SELECT  `image_containers`.* FROM `image_containers` INNER JOIN `images` ON `images`.`id` = `image_containers`.`image_id` WHERE `image_containers`.`status` = 'active' AND (image.workflow_state = 'published') ORDER BY `image.containers`.`id` ASC LIMIT 1

I'm confused as the image table has a workflow_state

Any help is greatly appreciated

Upvotes: 1

Views: 48

Answers (2)

Ilya Konyukhov
Ilya Konyukhov

Reputation: 2781

I have the following that works but takes a few seconds to execute:

The query from self.published method fetches all record with status: STATUS_ACTIVE, and then scans the resulting array, fetching those ones which have given image.workflow_state. So it fetches results which will be thrown away with select, which is a waste of resources. What is worse, for every row it creates an ActiveRecord object and makes an additional request to the database to fetch its image association. It's notorious N + 1 pattern problem.

To fix it you need to rewrite your query as

  joins(:image).where(status: STATUS_ACTIVE, images: { workflow_state: Publishable::Meta.published_key } )

Upvotes: 2

Roman Kiselenko
Roman Kiselenko

Reputation: 44360

If you're using plain sql you should use pluralize table names.

Change: image.workflow_state to images.workflow_state

Upvotes: 2

Related Questions