Rails PostgreSQL Group BY query

05.51 Muhamad Akbar Bin Widayat 0 Comments

The blue/white elephant logo of the PostgreSQL...
The blue/white elephant logo of the PostgreSQL RDBMS in SVG format. (Photo credit: Wikipedia)
Hi Everyone,

Currently I use PostgreSQL in my apps. When I starting query on PostgreSQL is difference with mysql.
I often had error like this "ActiveRecord::StatementInvalid: PG::Error: ERROR:  column "tabulars.year" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT year FROM "tabulars"  GROUP BY id".

So I try to revised my query on below.
For instance:
1. When I want to group by:
    on MySql :
    @tabulars = Tabular.search(params[:tabular]).joins(:user).group([:year, :user_id]).paginate(per_page: DEFAULT_PER_PAGE, page: params[:page])

     on PostgreSql:
     @tabulars = Tabular.search(params[:tabular]).select([:year, :user_id]).joins(:user).group([:year, :user_id]).paginate(per_page: DEFAULT_PER_PAGE, page: params[:page])

2. On PostgreSql the select column is required on grouping table and the group by column should be any on the select.

I hope this article can help you guys.
Enhanced by Zemanta

0 komentar:

terimakasih komentarnya