ERROR: syntax error at or near "on" LINE 2: FROM reviews GROUP BY product

how to i translate this into ruby on rails syntax ?

@products = Product.select('* FROM products p inner join (SELECT product_id, AVG(rating) as avg_rating
FROM reviews GROUP BY product_id) as x on p.id = x.product_id ORDER BY x.avg_rating DESC')

error: FROM" LINE 2: ...on p.id = x.product_id ORDER BY x.avg_rating DESC FROM "prod... ^

CodePudding user response：

You can use subquery like this:

 Product.joins(
   "INNER JOIN (#{Review.select('product_id, AVG(rating) AS avg_rating').group('product_id').to_sql}) AS reviews ON reviews.product_id = products.id"
 ).order('reviews.avg_rating DESC')

Or you can use Arel

products = Product.arel_table
reviews = Review.arel_table

subquery = reviews.project(:product_id, reviews[:rating].average).group(:product_id).as('reviews')

products.join(subquery).on(subquery[:product_id].eq(products[:id])).order(reviews[:rating].average.desc)

CodePudding user response：

Can you try below:

Product.joins("INNER JOIN ((SELECT product_id, AVG(rating) as avg_rating FROM reviews GROUP BY product_id) as x on products.id = x.product_id)")
       .order("x.avg_rating DESC")