Tips for retrieving a flag when there is a preexisting record within an association in Sequelize

Question

Tips for retrieving a flag when there is a preexisting record within an association in Sequelize

I am working with a model A that has a 1:N association with a model B. My objective is to retrieve all records from A and determine whether there is at least one associated record from B (true) or not (false).

The relationship setup:

ModelA.hasMany(ModelB)
ModelB.belongsTo(ModelA)

The query I currently have is:

const results = await ModelA.findAll({
include: { model: ModelB, required: false }
})

This allows me to retrieve matching records from B when they exist due to the outer join, but my main concern is simply to identify if any records from B are present. Is there a way to customize the SELECT query to directly return true/false based on the result of the join?

My desired custom query would be:

SELECT a.*, CASE WHEN b.id IS NOT NULL THEN true ELSE false END
FROM modelA a LEFT OUTER JOIN modelB b on b.model_a_id = a.id

javascript postgresql express sequelize.js

Answer 1

Answer №1

Sequelize currently does not have built-in support for CASE WHEN, so you will need to utilize the literal function instead.

const results = await ModelA.findAll({
  attributes: {
    include: [
      [Sequelize.literal('CASE WHEN "b"."id" IS NOT NULL THEN true ELSE false END'), 'b_exists']
    ]
  },
  include: { 
    model: ModelB,
    attributes: [],  // add this to remove ModelB data in response
    required: false
  }
})

Answer 2

Sequelize currently does not have built-in support for CASE WHEN, so you will need to utilize the literal function instead.

const results = await ModelA.findAll({
  attributes: {
    include: [
      [Sequelize.literal('CASE WHEN "b"."id" IS NOT NULL THEN true ELSE false END'), 'b_exists']
    ]
  },
  include: { 
    model: ModelB,
    attributes: [],  // add this to remove ModelB data in response
    required: false
  }
})

Tips for retrieving a flag when there is a preexisting record within an association in Sequelize

Answer №1

Similar questions

Using Jquery .ajax to Populate Select Dropdown with JSON Data

After clicking multiple times within the modal, the modal popup begins to shift towards the left side

Refresh an iframe smoothly and without any visual distraction (using JavaScript)

Automating the click of JavaScript buttons with Selenium

Improving efficiency of basic image carousel in Angular 8

Simplify code by eliminating uuid references

Advanced filtering of arrays in JavaScript

How to detect a click event in any area of an Angular2

Optimal approach to managing one-to-many relationships using type-graphql, typeorm, and dataloader

Try out the Jquery Chosen plugin, which allows you to select multiple instances of the same

Retrieving the title value of the parent span element through a child node with the help of JavaScript or

Is there a way to display errors during jQuery validation?

This code snippet, document.location.search.replace('?redirect=', '').replace('%2F', ''), is failing to execute properly in Firefox

Ways to apply CSS to a changing div ID

Ways to obtain an attribute through random selection

Responsive design involves ensuring that web elements such as divs are properly aligned

Does adding the Angular bootstrap script at the end of the body tag impact webpage speed more than using ng-app on the body tag?

Examining the scroll-down feature button

Exploring dependency injection in Angular 1 using a blend of JavaScript and TypeScript

Socket.on seems to be malfunctioning