CODEWITHSUNDEEP
mysqlsequelize.js
praxus
praxus

Reputation: 508

Sequelize - return single column from associated table as custom column

I have two models: User and Images. User has profile_image_id column.

When i get the user with include {model:Images, as:'profileImage', attributes:['filename']} I get profileImage as object with filename as property.

Is there a way in Sequelize to get that 'filename' as a property of User model? Meaning to execute 

SELECT u.id, u.name, i.filename 
FROM users u 
LEFT JOIN images i ON i.id = u.profile_image_id 
WHERE u.id = 1

What works for now is defining VIRTUAL attribute profileImageFIlename on user and then populating it in afterFind function of User model. But it seems like a lot of extra work and unnecessary data.

Is there a better way except raw query?

Upvotes: 1

Views: 8584

Answers (1)

doublesharp
doublesharp

Reputation: 27599

The short answer is that there isn't a way to do it that would be "less work". Even in your example SQL query you reference i.filename using the alias that you created for the related images table. This effectively maps to User.images.filename, which is just as usable as User.profile_image_file.

If you would like profile_image_id to exist as a VIRTUAL field on User then you are doing it the correct way - VIRTUAL fields won't be persisted to the database schema, so you would need to set them from some other source. In this case, the related images table provides the value and you will need to set it in the afterfind() hook.

If you don't care about it being on the User Instance or Model and just want to access the value in the results without having to traverse the object, you can use something like the following to alias the column by leveraging Sequelize.literal().

User.findById(1, {
  attributes: {
    include: [[Sequelize.literal('images.filename'), 'profile_image_file']],
  },
  include: [{ model: Images, as: 'images', attributes: [] }]
})
.then((user) => {
    // There will be a user.dataValues.profile_image_file value populated
    // but not a user.profile_image_file unless you set it in afterFind()
    console.log(user.dataValues);
});

This will result in SQL of

SELECT `user`.`id`, `user`.`name`, images.filename AS `profile_image_file`
FROM `user` AS `user` 
LEFT OUTER JOIN `images` AS `images` ON `user`.`profile_image_id` = `images`.`id` 
WHERE `user`.`id` = 1;

Upvotes: 7

Related Questions