I have this query

documents = Broker.find(12).clients.left_joins(:documents).select("clients.id, count(documents.expiration) FILTER (WHERE documents.expiration < '#{Date.today}') AS expired").group("1")

And it returns me a table like this:

And I want to add an order after the query. For example:

documents.order("expired")

But I got this error:

NoMethodError (undefined method `order' for []:array)

What can I do to get an ActiveRecord instead of an Array?

CodePudding user response：

It's because of .group invocation at the end of the expression. Documentation says:

Returns an array with distinct records based on the group attribute

So, in order to get an activerecord relation you should eliminate usage of .group.

CodePudding user response：

It looks like the documents variable is an array and not an ActiveRecord relation, which means that it does not have an order method.

To add an order clause to the query, you can modify the select method like this:

documents = Broker.find(12).clients.left_joins(:documents).select("clients.id, count(documents.expiration) FILTER (WHERE documents.expiration < '#{Date.today}') AS expired").group("1").order(expired: :desc)

This will add an order clause to the end of the query, ordering the results in descending order based on the expired column.

if you want to keep the documents variable as an array, you can use the sort_by method to sort the array in descending order based on the expired column:

documents = documents.sort_by { |doc| doc.expired }.reverse

This will sort the documents array in descending order based on the expired column.