CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecordgroup-by
edudepetris
edudepetris

Reputation: 714

Rails 4 + ActiveRecord + postgresql + group by do not work?

I want to make the following query by ActiveRecord but I cant't.

SELECT
  catalog_product_id,
  COUNT (catalog_product_id)
FROM
  sc_items
WHERE sc_items.shopping_cart_id = 34
GROUP BY
  catalog_product_id;
BEGIN

Table: 
scItems(catalog_product_id, shopping_cart_id)
[1,1]
[2,1]
[2,1]
[2,1]

I'm doing the following code ruby code:

sc_items.select("catalog_product_id, count(catalog_product_id) as count").group("catalog_product_id")

That generate this sql:

SELECT catalog_product_id, count(catalog_product_id) as count FROM "sc_items" WHERE "sc_items"."shopping_cart_id" = $1 GROUP BY catalog_product_id  [["shopping_cart_id", 34]]

And I get this result:

#<ActiveRecord::AssociationRelation [#<ScItem id: nil, catalog_product_id: 1>, #<ScItem id: nil, catalog_product_id: 3>]>

By other hand if I execute the above sql:

res = ActiveRecord::Base.connection.execute(sql)
res.class
res.to_a

I get this:

#<PG::Result:0x007fcb3720bea8 status=PGRES_TUPLES_OK ntuples=2 nfields=2 cmd_tuples=2> 
PG::Result 
[{"catalog_product_id"=>"2", "count"=>"3"}, {"catalog_product_id"=>"1", "count"=>"1"}]

This is the correct result, How can I make this query using ActiveRecord?. And why the activeRecord lacks the id of catalog_product_id if the sql is the same?

Additional information:

Upvotes: 2

Views: 2049

Answers (2)

Suganya
Suganya

Reputation: 652

To Group entire table by some attribute i have used

ScItem.unscoped.group(:catalog_product_id).count

Upvotes: 0

cristian
cristian

Reputation: 8744

I believe you have a model named ScItem

ScItem.where(shopping_cart_id: 34).group(:catalog_product_id).count

You can find more details about group by and count here

Upvotes: 2

Related Questions