CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecord
denispolicarpocampos
denispolicarpocampos

Reputation: 69

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list using order by with uniq

I have this scope on my product model:

scope :all_products, lambda {
        joins(:prices)
        .where('prices.start_date <= ? and products.available = ?', Time.current, true)
        .uniq
        .order('CASE WHEN products.quantity >= products.min_quantity and (prices.finish_date IS NULL OR prices.finish_date >= now()) THEN 0 ELSE 1 END, prices.finish_date asc')
    }

I get the follow error when I try to run it: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

How I can use my order by and the query be uniq? I use rails 4.

Upvotes: 0

Views: 2783

Answers (1)

Thang
Thang

Reputation: 841

  1. You need to select the columns first so you can order them later in .order
  2. The result will still have duplicated records in spite of using .uniq or .distinct because the generated query is SELECT DISTINCT products.*, prices.finish_date, ... tries to find all the combination of products.*, prices.finish_date and the special column that has a unique value (in this case you only want the products.id to be unique)

DISTINCT ON is the solution but using it is a little bit tricky for postgres because of SELECT DISTINCT ON expressions must match initial ORDER BY expressions.

Please try:

sub_query = Product.joins(:prices)
  .select("DISTINCT ON (products.id) products.*, CASE WHEN (products.quantity >= products.min_quantity) AND (prices.finish_date IS NULL OR prices.finish_date >= now()) THEN 0 ELSE 1 END AS t, prices.finish_date AS date")

query = Product.from("(#{sub_query.to_sql}) as tmp").select("tmp.*").order("tmp.t, tmp.date ASC")

Upvotes: 1

Related Questions