WHERE NOT EXISTS and cross-joins

[overlookmotel]

I've recently come up against a couple of cases where I can't work out how to do something in Sequelize that I quite commonly do in SQL:

WHERE NOT EXISTS

SELECT *
FROM t1
WHERE NOT EXISTS (
    SELECT *
    FROM t2
    WHERE t2.id = t1.id
)

Cross joins

SELECT *
FROM t1
    INNER JOIN t2 ON t2.x = t1.x
    INNER JOIN t3 ON t3.y = t1.y
WHERE t2.z = t3.z

Are these things possible in Sequelize without resorting to using query()?

[mickhansen]

We generally have really lousy support for doing non-include joins.
Your where not exists can be done by a literal where.

Both are things i'd like users to be able to do in Sequelize without resorting to writing raw queries.
An issue with joins is that we expect the query results do be in a specific format for building instances.

[overlookmotel]

Your where not exists can be done by a literal where

How do you mean? Sorry if I'm being dense...

By the way, are the docs on sequelize.js now reflecting the v2 API or still referencing v1.7?

I can look into implementing the cross-join. But not now! My first node/sequelize-based app has just gone into production and I have my hands rather full!

[mickhansen]

Something like where: sequelize.literal('NOT SUBQUERY')

Docs on RTD are v2 but not fully updated

[mickhansen]

And cool, what's the app?

[overlookmotel]

@mickhansen sequelize.col() doesn't work in this context does it? Using .literal() is a bit too close to coding the whole query in SQL for my liking. I'm running this on both MySQL and SQLite so would like to keep all my queries running through Sequelize to ensure they run everywhere.

[joebnb]

@overlookmotel in 2022 cross join still not have solution，im waiting for your conturbuting

[overlookmotel]

Wait away @joebnb! I'm afraid my focus is elsewhere these days. Perhaps give it a go to implement yourself?