Introduction
Sequelize.js is a popular promise-based Node.js ORM (Object-Relational Mapping) that supports the SQL dialect for many databases such as PostgreSQL, MySQL, SQLite, and MSSQL. One of the tasks you’ll often need to perform when working with databases is selecting distinct rows to ensure that the data you retrieve is not duplicated. In this tutorial, we’ll explore how you can use Sequelize to select distinct rows, starting with basic examples and moving to more advanced scenarios.
Basic Usage
To select distinct rows in Sequelize, you typically use the distinct option in your queries. Here’s a basic example:
const { User } = require('./models');
// Select distinct usernames from the User model
c
User.findAll({
attributes: [[Sequelize.fn('DISTINCT', Sequelize.col('username')), 'username']]
}).then(users => {
// Handle the distinct users list here
}).catch(error => {
// Handle error
});
It’s important to note that when using distinct, you should specify the column you want to be distinct.
Advanced Filtering
What happens if you want to select distinct rows based on specific conditions? Sequelize provides a way through the where clause:
// Select distinct usernames of active users only
c
User.findAll({
attributes: [[Sequelize.fn('DISTINCT', Sequelize.col('username')), 'username']],
where: { isActive: true }
}).then(users => {
console.log(users);
n}).catch(error => {
console.error(error);
});
Working with Joins
Often you’ll be working with joins to fetch data from related tables. Here’s how you can select distinct values from a join:
const { User, Profile } = require('./models');
// Fetch distinct user IDs from users that have a profile
User.findAll({
attributes: [[Sequelize.fn('DISTINCT', Sequelize.col('user.id')), 'id']],
include: [
{
model: Profile,
attributes: []
}
]
}).then(users => {
// Handle the distinct user IDs here
}).catch(error => {
// Handle error
});
When using joins, it’s crucial to specify the attributes for the main model and to include an empty array for the attributes of the joined model unless you need to select certain fields from it.
Conclusion
In this tutorial, we covered how to retrieve distinct rows using Sequelize.js. We looked at basic distinct queries, filtering with conditions, and applying distinct selections to joins. Sequelize’s API provides the flexibility needed to tackle this database task effectively. The key is to define your attributes carefully and make use of the ORM’s functions and methods for specifying conditions and handling joins.
Remember to thoroughly test your queries to ensure they return the expected results and to optimize your database’s performance. With the techniques discussed in this guide, you should now have a good foundation for working with distinct values in Sequelize.
