Sequelize JS custom query with sub selects

Question

I'm new to ORM and I don't know to do this using an ORM. Considering that i have 5 tables:

tag, auction, user, user_tag and auction_tag

and I'm using SequelizeJS, how can I get a query like this?

select  A.id, A.title, A.content, A.createdAt, U.id, U.picture, U.name
from    Auction A, User U, User U2
where   A.userId = U.id and U2.id = x
and     exists (select  AT.id
            from    Auction_Tag AT
            where   AT.auction = A.id
            and     AT.tag in ( select  T.id
                                from    Tag T, User_Tag UT
                                where   U2.id = UT.user
                                and     UT.tag = T.id
                              )
           );

Any ideas? Thank you guys

I found the answer. : sequelize.query("SELECT * FROM users", { type: sequelize.QueryTypes.SELECT}) .then(function(users) { // We don't need spread here, since only the results will be returned for select queries }) — João R.
– João R., Commented Feb 18, 2015 at 15:30
Depending of the relations you have with the tables you can achieve that in more cleaner way (at least for a ORM), than using queries like that. — dege
– dege, Commented Feb 18, 2015 at 17:30

Yeray Cabello · Accepted Answer · 2015-02-19 11:30:46Z

1

Using an orm is suppose to abstract you from the query language itself. If you are willing to do a nested request, you should use the "where" property of the options object you have to pass to the findAll method. I'm unable to deduce your structure from the information you gave, so I'm putting an example with the best of my guesses (being Auction your auction model and userid the id relating it to user):

Auction.findAll({where: { userid: 8 }}).done(callback);

If, however, you are going for a related entity parameter, let's say User.name, go for it:

Auction.findAll({where: { 'User.name': 'John' }}).done(callback);

If you see yourself using SQL queries, maybe you should consider not using an ORM.

answered Feb 19, 2015 at 11:30

Yeray Cabello

4213 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Sequelize JS custom query with sub selects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related