CODEWITHSUNDEEP
ruby-on-railspostgresqlrequest
alex.bour
alex.bour

Reputation: 2964

GROUP on a column + COUNT in Rails 6 + PostgreSQL

I have built a PosgtreSQL request that works well in my PG client tool, but don't work when transposed for Rails.

The request that works:

SELECT
    usr_drinks.optimized_all
    COUNT(usr_drinks.optimized_all),    
FROM
    usr_seasons
    INNER JOIN usr_drinks ON usr_drinks.id = usr_seasons.drink_id
    INNER JOIN usr_properties ON usr_properties.id = usr_drinks.property_id
        AND usr_properties.winery_id = 526
    INNER JOIN msr_wineries ON msr_wineries.id = usr_properties.winery_id
    INNER JOIN usr_photos ON usr_photos.season_id = usr_seasons.id
        AND(usr_photos.verified_kind = 1
            OR usr_photos.verified_kind = 0)
        AND usr_photos.verified_at IS NOT NULL
WHERE
    usr_drinks.optimized_at IS NOT NULL
    AND usr_drinks.verified_at IS NULL
    AND NOT EXISTS (
        SELECT
            NULL
        FROM
            msr_references
        WHERE
            msr_references.winery_id = msr_wineries.id
            AND msr_references.verified_at IS NOT NULL)         
    GROUP BY
        usr_drinks.optimized_all
    HAVING COUNT(usr_drinks.optimized_all) > 5;

Now, In rails (I don't write the WHERE and JOIN there to clarify the request):

@record_with_minimum_drinks = Season.group("usr_drinks.optimized_all").
select("usr_drinks.optimized_all, COUNT(usr_drinks.optimized_all)").
where(where).
joins(joins).
having("COUNT(usr_drinks.optimized_all) > ?", @split_minimum_drinks).
first

But I obtain the classical error:

PG::GroupingError: ERROR: column "usr_seasons.id" must appear in the GROUP BY clause

What did I miss ? Thanks

Upvotes: 1

Views: 54

Answers (1)

Vasfed
Vasfed

Reputation: 18454

ActiveRecord::FinderMethods#first adds sort by and limit to the query, which obviously will interfere with your request.

Upvotes: 1

Related Questions