Ytukyg

I have a working SQL query and I would like to convert it into an Arel, but I seem to keep on running into the same error.

Working SQL:

Order.find_by_sql(

    "SELECT COUNT(order_number) AS order_count,

    restaurant_id, date_part('month', date) AS date_month,

    date_part('year', date) AS date_year 

    FROM orders

    WHERE restaurant_id is not null

    GROUP BY(restaurant_id, date_part('month', date), date_part('year', date))

    ORDER BY date_part('year', date) desc, date_part('month', date) desc;"

  )

Attempt 1 (not working)

orders = Order.arel_table



Order

    .where.not(restaurant_id: nil)

    .select(:restaurant_id)

    .select(:date)

    .select(Order.arel_table[:order_number].count.as('order_count'))

    .select(

        Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('month'), :date]),

        Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('year'), orders[:date]])

    )

    .group(

        :restaurant_id, 

        Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('month'), orders[:date]]), 

        Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('year'), orders[:date]])

    )

Attempt 2 (not working)

orders = Order.arel_table

query = orders

      .where(orders[:restaurant_id].not_eq(nil))

      .project(

          orders[:restaurant_id],

          Order.arel_table[:order_number].count.as('order_count'),

          Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('month'), orders[:date]]).as('date_month'),

          Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('year'), orders[:date]]).as('date_year'),

      )

      .group(

          orders[:restaurant_id],

          Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('month'), orders[:date]]),

          Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('year'), orders[:date]])

      )

      .order(orders[:date].desc)

      .to_sql

Order.find_by_sql(query)

Both approaches result in the following error:

PG::GroupingError: ERROR: column "orders.date" must appear in the GROUP BY clause or be used in an aggregate function

The problem is that I don't want to group by date, but by month. Any tips on how to solve it?

I also originally tried Arel::Nodes::NamedFunction.new('date_part', ['month', :date]) instead of Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('month'), orders[:date]]) but it resulted with the following:

Unsupported argument type: String. Construct an Arel node instead.

Any help greatly appreciated. Thanks!

So after hours of digging it turns out the big gotcha! of my problem was a default_scope set on my Order model - that's where sorting by :date was happening. In the end I decided to remove the default scope, use sorting where it was actually needed and what I was left it regarding my query was this:

orders = Order.arel_table

query = orders

  .where(orders[:restaurant_id].not_eq(nil))

  .project(

      orders[:restaurant_id],

      Order.arel_table[:order_number].count.as('order_count'),

      Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('month'), orders[:date]]).as('date_month'),

      Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('year'), orders[:date]]).as('date_year'),

  )

  .group(

      orders[:restaurant_id],

      Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('month'), orders[:date]]),

      Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('year'), orders[:date]])

  )

  .order(

      orders[:restaurant_id],

      Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('year'), date]).desc,

      Arel::Nodes::NamedFunction.new('date_part', [Arel::Nodes.build_quoted('month'), date]).desc



  )

  .to_sql



Order.find_by_sql(query)

Lesson learnt: avoid default_scopes whenever possible. If you work alone, you're likely to forget you used it over time. If you work in a team, you might just cost somebody a full day of hair pulling.