CODEWITHSUNDEEP
ruby-on-railsactiverecord
dee
dee

Reputation: 1898

How do I translate this SQL to ActiveRecord correctly?

I want to select items that belong to a product. They have to be distinct on items.condition. Here's the query:

SELECT DISTINCT ON (items.condition) items.id, items.condition, items.price, products.name
FROM products
INNER JOIN items ON products.id = items.product_id
WHERE products.id = 1
ORDER BY items.condition, items.price, products.name;

I tried this, but I'm getting StatementInvalid error. I'm using Rails 4.

product = Product.find(1)
product.items.select("DISTINCT ON(items.condition) items.id, products.*, items.*").order('items.condition, items.price, products.name')

Error:

PG::Error: ERROR:  missing FROM-clause entry for table "products"
LINE 1: SELECT DISTINCT ON(items.condition) items.id, products.*, it...
                                                      ^
: SELECT DISTINCT ON(items.condition) items.id, products.*, items.* FROM "items"  WHERE "items"."product_id" = $1  ORDER BY items.condition, items.price, products.name
ActiveRecord::StatementInvalid: PG::Error: ERROR:  missing FROM-clause entry for table "products"
LINE 1: SELECT DISTINCT ON(items.condition) items.id, products.*, it...

Upvotes: 2

Views: 130

Answers (1)

usha
usha

Reputation: 29349

Try this(Not tested)

Product.joins(:items).select("DISTINCT(items.condition), items.id, products.*").order('items.condition, items.price, products.name')

Upvotes: 2

Related Questions