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}]
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}?
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'
})