Sling Academy
Home/Node.js/Sequelize.js: How to Select Distinct Rows

Sequelize.js: How to Select Distinct Rows

Last updated: December 29, 2023

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.

Next Article: How to Use RIGHT JOIN in Sequelize.js

Previous Article: Sequelize.js: “Greater Than or Equal To” Operator Usage

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates