Activeadmin: paginate_collection with group by and sort by alias column name

Question

I am using active-admin to show list of users with following query.

SELECT  
agent_id, 
COUNT(*) filter (where price BETWEEN 1 AND 200000) as stat_1, 
from users
group by agent_id
order by stat_1 desc
LIMIT 20 OFFSET 0

Rails Standard:

User
.select('agent_id, COUNT(*) filter (where price BETWEEN 1 AND 200000) as stat_1').page(params[:page]).per(20)
.group(:agent_id)
.page(params[:page]).per(20)
.order('stat_1 desc')

In ActivAdmin

paginated_collection(users) do
  table_for collection, sortable: true do
    ....
    ....
  end
end

But paginate_collection throws error

PG::UndefinedColumn: ERROR:  column "stat_1" does not exist

SELECT  COUNT(*) AS count_all, agent_id AS agent_id FROM "users" 
GROUP BY "users"."agent_id"  
ORDER BY stat_1 desc LIMIT 20 OFFSET 0

How I can resolve this issue?

score 0 · Answer 1 · edited May 23 '17 at 12:22

0

Its not because of Activeadmin but due to the PostgreSQL.

Actually you are trying to use column alias with ORDER BY clause which PostgreSql will not allow and will always throw an error.The same happens with other clause also when you try to use alias like:

GROUP BY, Having, ORDER BY etc

This answer have a good example to do it by defining CASE.

edited May 23 '17 at 12:22

Community

1
1

answered Aug 04 '16 at 11:27

Sonam Shah

41
6

alias column can be used in latest PostgreSql. Issue with paginated_collection where it is not taking all select column for total_pages count. It is just taking 2 columns (COUNT(*) AS count_all and agent_id AS agent_id) so stat_1 column is not exist in that case. – Brijesh Shah Aug 04 '16 at 12:39
@Sonam : I think alias method works fine with latest version. – Arpit Vaishnav Aug 04 '16 at 12:55

score 0 · Answer 2 · answered Aug 05 '16 at 15:23

I fixed this issue by changing in paginated_collection.rb line#131 in ActiveAdmin

from

entries_name = I18n.translate key, count: collection.size, default: entry_name.pluralize

to

entries_name = I18n.translate key, count: collection.total_count, default: entry_name.pluralize

Activeadmin: paginate_collection with group by and sort by alias column name

2 Answers2