CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4
Pramod Shinde
Pramod Shinde

Reputation: 1892

Select lower case column with group by or select lower(column) in Rails 4

How to achieve following in rails 4?

Here is my PostgreSQL query and works correctly 

SELECT lower(name) as c_name, count(*) as cc FROM categories  GROUP BY c_name  ORDER BY cc desc LIMIT 3

Ii gives me following results 

 "ecommerce solutions";6
 "vmware";6
 "big data analytics";5

But when I write same query in Rails 4 as 

Category.select("lower(name) as c_name, count(*) as cc").group("c_name").order("cc desc").limit(3)

Above query generates following query which is same as above PostgreSQL query 

SELECT lower(name) as c_name, count(*) as cc FROM "categories"  GROUP BY c_name  ORDER BY cc desc LIMIT 3

gives following results 

[#<Category id: nil>, #<Category id: nil>, #<Category id: nil>]

Why this is happening ? when I cut my query to 

 Category.select("lower(name)").limit(3)

This also does not work, and results into 

 [#<Category id: nil>, #<Category id: nil>, #<Category id: nil>]

I want to do group on lower(name) with count as aggregation, I have many variances of name in Category model

Ex. data set

"Vmware" and "VMware" and "VMWARE" and "vmware"

How can I group on lower(name) in Rails 4?

In simple words, How to write equivalent query of following in Rails 4? 

SELECT lower(name) as c_name, count(*) as cc FROM categories  GROUP BY c_name  ORDER BY cc desc LIMIT 3

Upvotes: 2

Views: 2102

Answers (1)

hedgesky
hedgesky

Reputation: 3311

The result is correct, but Rails displays it wrong. Just access results manually:

results = Category.select("lower(name) as c_name, count(*) as cc").group("c_name").order("cc desc").limit(3) 
results.first['c_name']

Upvotes: 5

Related Questions