CODEWITHSUNDEEP
mysqlruby-on-railsdatabasepostgresqlactiverecord
madhermit
madhermit

Reputation: 367

Converting Rails ActiveRecord GROUP query from MySQL to PostgreSQL

I have a ActiveRecord query I’ve been working with (history here) that works great in MySQL, but now I need to convert my database to PostgreSQL and I'm getting an error. Here is the query:

class Shoe < ActiveRecord::Base
  has_many :purchases

  def self.available_shoes
    #show all shoes that have been purchased less than num_in_stock
    num_in_stock = 3
    Shoe.includes(:purchases)
        .group("purchases.shoe_id")
        .having("COUNT(purchases.shoe_id) < ?", num_in_stock)
  end
end

Simply switching the gems and adapters to postgres is not enough: I now get the following error:

ActionView::Template::Error (PG::Error: ERROR: column "shoes.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "shoes"."id" AS t0_r0, "shoes"."created_at" AS ...

I tried changing

.group("purchases.shoe_id")

to

.group("purchases.shoe_id, shoes.id, purchases.id")

and while this got rid of the error, it also changed the sql and broke some of my tests.

I have read many stackoverflow questions regarding this error but I was unable to find a solution. What do I need to change in my ActiveRecord query to make this work in postgres?

Thanks in advance!

Upvotes: 1

Views: 595

Answers (1)

sufleR
sufleR

Reputation: 2973

Did you found solution? If not try:

Shoe.where("not exists ( select 1 from purchases where shoe_id = shoes.id offset ? limit 1)", num_in_stock - 1)

It will be slow for big num_in_stock and you should have index on purchases.shoe_id column.

I think that there is no fast solution on large tables unless you have already counted purchases.

EDIT

Consider using counter_cache or counter_culture gem

Upvotes: 1

Related Questions