Converting PostgreSQL subquery statement into Sequelize query

Question

I have two tables: User and Publications. User has different roles like admin and subscriber. There is a User.hasMany(Publication) and Publication.belongsTo(User) association to allow an admin to create Publications associated to their userId.

Here's the tricky part. I have an array of subscribedPublications for each User with a role of 'subscriber'. This is so a subscriber can always have access to Publications included in their subscription.

I want to build a query for a subscriber to access an index of their available publications and the relevant columns for it. My problem is being unable to do it in Sequelize for my node/express app. Here's how I am able to get what I want in SQL:

SELECT * 
FROM "Publications"
WHERE "id" IN (
  SELECT unnest("subscribedPublications") as "subscribedPublications"
  FROM "Users"
  WHERE "id"='f3fc2553-699c-49e0-8ad5-5a4692af1473'
);

This delivers a table with a row of details for each publication (since I unnest my array here).

Please note I use a specific userId in my subquery for this. I will be passing req.params.id in my route for the authorized subscriber so I can use it to retrieve their collection of subscribedPublications.

So far, I've only had success retrieving just this particular user's publications by calling:

return User.findById(id, {
  attributes: ['subscribedPublications']
})

However, this does not give me details of Publications - just publication IDs.

I also tried this, with no success:

const Sequelize = require("sequelize");
const sequelize = require("../../src/db/models/index").sequelize;
const Op = Sequelize.Op;
const Publication = require("./models").Publication;
const User = require("./models").User;

return Publication.findAll({
  where: {
    id: {
      [Op.in]: sequelize.query(
        '(SELECT unnest("subscribedPublications") as "subscribedPublications" FROM "Users" WHERE "id" = :id)',
        {replacements: {id: id}},
        { type: Sequelize.QueryTypes.SELECT}
      )
    }
  }
})
.then((publications) => {
  console.log(publications);
})
.catch((err) => {
  console.log(err);
})

I get a blank [] for a result.

Other notes:

I had to include the above sequelize definitions because I was getting errors otherwise.
for sake of simplicity here, I have just added console.log for my Promise.
I don't know if the Array type is causing further complications.

TL;DR

Need to create a Sequelize query that produces results I can get from a SQL subquery statement. The column/field I need is of type Array in PostgreSQL - don't know if that needs to be mapped in Sequelize.

aizkhaj · Accepted Answer · 2018-09-05 06:17:00Z

2

I managed to figure it out by using sequelize.literal instead of sequelize.query + allowing my id parameter to pass thanks to string concatenation:

return Publication.findAll({
  attributes: ['id', 'title', 'fileLocation', 'tickerSymbol', 'recommendedPrice', 'createdAt', 'updatedAt'],
  where: {
    id: {
      [Op.in]: sequelize.literal(
        '(SELECT unnest("subscribedPublications") as "subscribedPublications" FROM "Users" WHERE "id" ='+ '\'' + id + '\')'
      )
    }
  }
})
.then((publications) => {
  console.log(publications);
})
.catch((err) => {
  console.log(err);
});

answered Sep 5, 2018 at 6:17

aizkhaj

1451 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aizkhaj Over a year ago

I'd still be open to getting feedback or answers on how you may tackle this one...if there's a better way!

Collectives™ on Stack Overflow

Converting PostgreSQL subquery statement into Sequelize query

TL;DR

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

TL;DR

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related