Is it possible to join against a derived table (subquery) using Sequelize?

Question

I'm working with an example schema like the following (apologize for not inlining, don't meet the reputation requirement): https://i.sstatic.net/27zU1.png

There's a many-to-many relationship between Authors and both Editors and Books. If you attempt to write a query such as:

SELECT "authors"."author_id", array_agg("books"."name"), array_agg("editors"."name"), array_agg("books"."release_date") 
FROM "authors"
INNER JOIN "author_book" ON "authors"."author_id" = "author_book"."author_id"
INNER JOIN "books" ON "book"."book_id" = "author_book"."book_id"
INNER JOIN "author_editor" ON "authors"."author_id" = "author_editor"."author_id"
INNER JOIN "editors" ON "editors"."id" = "ParentToChild"."ChildId"
GROUP BY "authors"."author_id"
LIMIT 10 OFFSET 0

The joins here result in a cartesian product which has numerous issues:

1. Duplicate results in the aggregate
2. Performance issues at scale. Adding in another relationship further multiplies the results and depending on how many connected rows each relation has it can quickly spiral out of control and crash the database.

It would be great if I could write a query like the following using Sequelize:

SELECT "authors"."author_id", "authorsbooks"."book_names", "authorseditors"."editor_names"
FROM "authors"
INNER JOIN (SELECT "author_book"."author_id" AS "author_id", array_agg("books"."name") AS "book_names"
            FROM "author_book"
            INNER JOIN "books" ON "books"."book_id" = "author_book"."book_id"
            GROUP BY "author_book"."author_id") AS "authorsbooks" ON "authorsbooks"."author_id" = "authors"."author_id"
INNER JOIN (SELECT "author_editor"."author_id" AS "author_id", array_agg("editors"."name") AS "editor_names"
            FROM "author_editor"
            INNER JOIN "editors" ON "editors"."editor_id" = "author_editor"."editor_id"
            GROUP BY "author_editor"."author_id") AS "authorseditors" ON "authorseditors"."author_id" = "authors"."author_id" 
LIMIT 10 OFFSET 0

Here we are doing the aggregation in a derived table and then joining the against the relevant author ids. I've played around with many Sequelize properties on includes such as separate, duplicating, limit, etc and I have not been able to produce a query like the one above.

Sequelize does support aggregate subqueries in the SELECT statement https://sequelize.org/master/manual/sub-queries.html but I find myself needing multiple aggregates from each table so joining a derived table subquery would be preferable.

Is there a more obvious solution to this problem that I'm missing? Is there a way to make this query with Sequelize? If not I'm going to strongly consider using Knex or some other query builder that's less of a black box and gives more control over querying.

Answer 1

Unfortunately (and I suppose even intentionally) Sequelize is not about such complex queries using its models.

It's about following ORM principles and for such custom queries you just need to use a raw SQL query using sequelize.query.

If you have a lot of such queries try to think about choosing another ORM or ORM-like package to work with a DB.