Sequelize raw include/join

Question

I want to be able to do the following simple SQL query using Sequelize:

     SELECT * FROM one 
     JOIN (SELECT COUNT(*) AS count, two_id FROM two GROUP BY two_id) AS table_two 
     ON one.two_id = two.two_id

I can't seem to find anything about raw include, or raw model

For performance reason, I don't want subselect in the main query (which I know sequelize already works well with) aka:

    SELECT * FROM one, (SELECT COUNT(*) AS count FROM two WHERE one.two_id = two.two_id) AS count

Regarding the following sequelize code (models One and Two exists)

    models.One.findAll({
        include: [
          models: model.Two
          // what to add here in order to get the example SQL
        ]
      })

Really sad from the most used ORM...Would you have another idea for my use case using Sequelize ? — Plotisateur
– Plotisateur, Commented Dec 5, 2022 at 0:50

Plotisateur · Accepted Answer · 2022-12-05 15:54:52Z

1

Seems like I found a somewhat hacky workaround:

You can use fn inside selections to use any SQL word (like JOIN), resulting in something like this for my use case:

models.One.findAll({
  attributes: [
    fn('JOIN', literal('SELECT COUNT(*) AS count FROM two WHERE one.two_id = two.two_id')),
  ],
});

Note you can do that only on the last attribute (else it's a misplaced joint)

edited Dec 5, 2022 at 15:54

answered Dec 5, 2022 at 10:52

Plotisateur

50610 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Sequelize raw include/join

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