sub-query in where conditions

[kirill-zhirnov]

Hi!
Is it possible to specify sub-query for where?
For example I need a final query be:

select 
    * 
from 
    characteristic 
where
    characteristic_id = 1
    and characteristic_id in (
        select characteristic_id from characteristic_variant_val where rel_type = 'variant'
    )

I tried to do:

    db.model('characteristic').find({
        where :
            characteristic_id : 151980
            $and : {
                characteristic_id : {
                    $in : 'select characteristic_id from characteristic_variant_val limit 1'
                }
            }
    })

How can I properly specify sub-request? How can I properly specify params?

[bobby77]

I would be very interested by the answer, as I did not find a solution.
Have you found a way to do this?
Thanks a lot

[muhammaddadu]

A sub query can be done like this...

characteristic_id : {
$in: [clout.sequelize.literal('select characteristic_id from characteristic_variant_val'))]
}

[theTestTube]

I've got three models to represent any supported language in a translation app, any message that could be translated and any actual translation of a message in a language.

I want to have a pending translations query that could be represented with,

sequelize.query('SELECT lang, msgid FROM language, message WHERE (lang, msgid) NOT IN (SELECT lang, msgid FROM translation) ', { model: translation }).then(function(translations){});

or

sequelize.query('SELECT lang, msgid FROM language, message WHERE NOT EXISTS (SELECT * FROM translation WHERE translation.lang = language.lang AND translation.msgid = message.msgid)', { model: translation }).then(function(translations){});

Theese two queries work perfectly on PostgreSQL but don't preserve cross-database modeling. How could I represent the pending translations (messages and their language that actually haven't yet a translation instance) using Sequelize data models?

I thought a Sequelize scope could serve for this pourpose but it actually represents a query applied on real instances.

[ShaharHD]

I have encountered a similar issue in my project. The way I choose to implement it is a bit different for two reasons:

1. If at one point in time Sequelize decides to implement sub queries - the syntax is ready.
2. Use Sequelize protection agains SQL injection.
   Here is my code snippet, hope it helps.

const tempSQL = sequelize.dialect.QueryGenerator.selectQuery('MyOtherTable',
    attributes: ['fkey'],
    where: {
         field1: 1,
         field2: 2,
         field3: 3
    })
    .slice(0,-1); // to remove the ';' from the end of the SQL

MyTable.find( {
    where: {
        id: {
             $notIn: sequelize.literal('(' + tempSQL + ')'),
        }
    } 
} );

Some people might choose to not use the tempSQL variable and simply build the SQL inside the find structure (maybe using a helper method?)

I also think this might be the basis for a sub queries extension for sequelize as it uses the same syntax almost.