Sequelize Raw query not returning array for has many

Question

I am trying to use a raw sql query in sequelize and have this code. My table structure is an external_profile that has many connections.

const users = await models.sequelize.query("SELECT `External_Profile`.*, AVG(Connections.rating) AS rating, `Connections`.`id` AS `Connections.id`, `Connections`.`known_type` AS `Connections.known_type`, `Connections`.`rating` AS `Connections.rating`, `Connections`.`createdAt` AS `Connections.createdAt`, `Connections`.`updatedAt` AS `Connections.updatedAt`, `Connections`.`UserId` AS `Connections.UserId`, `Connections`.`ExternalProfileId` AS `Connections.ExternalProfileId`, `Connections->User`.`id` AS `Connections.User.id`, `Connections->User`.`first_name` AS `Connections.User.first_name`, `Connections->User`.`last_name` AS `Connections.User.last_name`, `Connections->User`.`email` AS `Connections.User.email`, `Connections->User`.`password` AS `Connections.User.password`, `Connections->User`.`linkedinUrl` AS `Connections.User.linkedinUrl`, `Connections->User`.`createdAt` AS `Connections.User.createdAt`, `Connections->User`.`updatedAt` AS `Connections.User.updatedAt` FROM (SELECT `External_Profile`.`id`, `External_Profile`.`profile_data`, `External_Profile`.`name`, `External_Profile`.`headline`, `External_Profile`.`image`, `External_Profile`.`linkedinUrl`, `External_Profile`.`createdAt`, `External_Profile`.`updatedAt` FROM `External_Profiles` AS `External_Profile`) AS `External_Profile` LEFT OUTER JOIN `Connections` AS `Connections` ON `External_Profile`.`id` = `Connections`.`ExternalProfileId` LEFT OUTER JOIN `Users` AS `Connections->User` ON `Connections`.`UserId` = `Connections->User`.`id` GROUP BY External_Profile.name HAVING AVG(Connections.rating) > 3",  
        { 
            type: models.sequelize.QueryTypes.SELECT,
            model: [models.External_Profile, models.Connection],
            mapToModel: true,
            nest: true,
            raw: true
        })

However this is only returning to me an object for the connection on the external profile. Thats a 1 to many relationship so it should be returning an array. Any ideas on why it wouldn't return all records?

Miter

Answer 1

Don't use raw: true That will cause lots of serialization problem.

use toJSON() instead

const usersDao = await models.sequelize.query("SELECT `External_Profile`.*, AVG(Connections.rating) AS rating, `Connections`.`id` AS `Connections.id`, `Connections`.`known_type` AS `Connections.known_type`, `Connections`.`rating` AS `Connections.rating`, `Connections`.`createdAt` AS `Connections.createdAt`, `Connections`.`updatedAt` AS `Connections.updatedAt`, `Connections`.`UserId` AS `Connections.UserId`, `Connections`.`ExternalProfileId` AS `Connections.ExternalProfileId`, `Connections->User`.`id` AS `Connections.User.id`, `Connections->User`.`first_name` AS `Connections.User.first_name`, `Connections->User`.`last_name` AS `Connections.User.last_name`, `Connections->User`.`email` AS `Connections.User.email`, `Connections->User`.`password` AS `Connections.User.password`, `Connections->User`.`linkedinUrl` AS `Connections.User.linkedinUrl`, `Connections->User`.`createdAt` AS `Connections.User.createdAt`, `Connections->User`.`updatedAt` AS `Connections.User.updatedAt` FROM (SELECT `External_Profile`.`id`, `External_Profile`.`profile_data`, `External_Profile`.`name`, `External_Profile`.`headline`, `External_Profile`.`image`, `External_Profile`.`linkedinUrl`, `External_Profile`.`createdAt`, `External_Profile`.`updatedAt` FROM `External_Profiles` AS `External_Profile`) AS `External_Profile` LEFT OUTER JOIN `Connections` AS `Connections` ON `External_Profile`.`id` = `Connections`.`ExternalProfileId` LEFT OUTER JOIN `Users` AS `Connections->User` ON `Connections`.`UserId` = `Connections->User`.`id` GROUP BY External_Profile.name HAVING AVG(Connections.rating) > 3",  
        { 
            type: models.sequelize.QueryTypes.SELECT,
            model: [models.External_Profile, models.Connection],
            mapToModel: true,
            nest: true,
            raw: true
        })

const cleanUser = usersDao.toJSON()

Buckle