Having trouble finding distinct records using sequelize-CodePudding

let's assume i have a table with records like [{fruit : apple, colour : green, price : 100 },{fruit : apple, colour : red, price : 75},{fruit : banana, colour : yellow, price : 50}].

What would be the sequelize command if i wanted to fetch unique fruits, but i want to fetch only the colour field along with it, and i want only 1 occurance of a fruit... So for this table, i want my output to be [{fruit : apple, colour : green},{fruit : banana, colour : yellow}]

This is what I tried :

fruitVendor.findAndCountAll({
attributes:[[Sequelize.fn('DISTINCT', Sequelize.col('fruit')),'FRUIT'],['colour','COLOUR']]
})

But i got [{fruit : apple, colour : green},{fruit : apple, colour : red},{fruit : banana, colour : yellow}]

And then i removed colour as one of the attributes just to check if distinct was working fine:

fruitVendor.findAndCountAll({
attributes:[[Sequelize.fn('DISTINCT', Sequelize.col('fruit')),'FRUIT']
})

And i got [{fruit : apple},{fruit : banana}]

So even if i mention distinct with one specific attribute, it applied it to the others as well?

Can anyone help me get [{fruit : apple, colour : green},{fruit : banana, colour : yellow}]

CodePudding user response：

This is how SELECT DISTINCT works with SQL. It evaluates all the combinations of the columns you chose to select from.

If it didn't do it this way, how would it know that you wanted {fruit: apple, color: green} instead of {fruit: apple, color: red}?

CodePudding user response：

If you are okay with any color, you can try this.

fruitVendor.findAndCountAll({
    attributes: [['fruit','FRUIT'], [Sequelize.fn('MIN', Sequelize.col('colour')), 'COLOUR']], // Or MAX
    group: 'fruit'
})