CODEWITHSUNDEEP
mysqlnode.jsormsequelize.js
Ashley Schuett
Ashley Schuett

Reputation: 192

Use MySQL variables with Sequelize

I am trying to grab max of 4 rows for each distinct value. This query I have works.

SET @currcount = NULL, @currvalue = NULL; SELECT * FROM ( SELECT *, @currcount := IF(@currvalue = quantity, @currcount + 1, 1) AS rank, @currvalue := quantity AS whatever FROM wines where restaurant_id = 16 and owner_id is null ORDER BY quantity DESC ) AS x WHERE rank <= 4

But now I can't get it to work with Sequelize. Can I use @variable in Sequelize?

Has anyone done this? Or am I going to have to just use a raw query?

Thanks!

Upvotes: 1

Views: 944

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

I am not sure, but put your 2 queries in one like this and try it with : sequelize.query(.....

SELECT * FROM (
    SELECT
        *, 
        @currcount := IF(@currvalue = quantity, @currcount + 1, 1) AS rank,
        @currvalue := quantity AS whatever
    FROM wines
    where restaurant_id = 16 and owner_id is null 
    ORDER BY quantity DESC
) AS x 
CROSS JOIN ( SELECT @currcount := NULL, @currvalue := NULL) as parameter
WHERE rank <= 4;

Upvotes: 1

Related Questions