Sequelize: join two times the same table

Question

Background:

I have a table Users with a one-to-many relationship to Userlang. For instance:

 userlang.id userlang.userid userlang.lang     
+-----------+---------------+-------------+
 1            1              EN
 2            1              FR
 3            1              IT
 4            2              EN
 5            2              DE
 6            3              IT

I want to select all users and their userlangs that have at least one userlang.lang=EN.

So, I cannot use the following query:

Select USERS.id from USERS inner join USERLANG on USERS.ID=USERLANG.USERID where USERLANG.LANG="EN"

because it will select only the Userlangs row with a lang="EN", while I want to select all userlang rows of each user, where at least one has lang="EN"

I am already able to write such a query in two ways with SQL:

using sub queries:

Select * 
from USERS inner join USERLANG on USERS.ID=USERLANG.USERID
where USERS.ID in (
    Select USERS.id 
    from USERS inner join USERLANG on USERS.ID=USERLANG.USERID
    where USERLANG.LANG="EN"
)

While the inner query will select only userlang rows with lang="EN", the outer one will select all userlangs for such users.

using two inner joins of the same table:

Select * 
from USERS innerjoin USERLANG as FILTER on FILTER.userid=USERS.userid
inner join USERLANG on USERLANG.userid=FILTER.userid
where FILTER.lang="EN"

The problem: porting them on sequelize

I am completely unable to write a subquery on sequelize: I want to avoid to write pure SQL as it would be open possible injections attack.

This is my failed attempt that gives error becasue of no relation of 'FILTER' with USer:

models.User.findAll({
    include:[models.Userlang,{model:models.Userlang,as:'FILTER'}] 
})

Answer 1

This is a very old post but hopefully this gives a usable answer for someone. There are numerous ways to achieve this, including some raw sequel options.

But, assuming this is a reusable reference, I would try putting an association on [USERLANG] to [USERLANG] and query based on that. This would modify the model, so it might be a bit much for a one-off query.

models.UserLang.hasMany(models.UserLang, {as: 'Filter', foreignKey: 'UserId', targetKey: 'UserId'});

models.User.findAll({
    include:[{ model: models.Userlang,
        required: true,
        include:[{ model: models.Userlang,
            required: true, // redundant
            as: 'Filter',
            where: { lang: 'EN' }
        }]
    }]
})

You could also place a raw SQL subquery in your attributes list (using sequelize.literal()), and use your where clause to filter based on the results. That would be a more of a one-off solution that would not require modifying the model. But that does go back to your aversion to raw SQL.

Answer 2

Try to define your associations with some alias:

            List.belongsTo(Task, {
              as: 'dailyTask',
              foreignKey: 'dailyTask'
            });

            List.belongsTo(Task, {
               as: 'weeklyTask',
               foreignKey: 'weeklyTask'
            });

The example from the creator is in this link: https://github.com/sequelize/sequelize/issues/3678

Answer 3

       select userid,lang 
       from t 
       where userid in ( 
                          select userid 
                          from t  where lang="en" 
                          group by userid having count(lang)>=1)

    select a.userid,a.lang 
    from t a left join ( select userid,lang from t where lang ="en") b  
    on a.userid=b.userid 
    where b.lang is not null

Not sure what your problem is but alternatively could get the same results by doing it in two steps; replacing the sub-query with a temptable.