how to add Union in Sequelize js on existing WHERE condition?-CodePudding

The where condition in seqlelize with node below WHERE A->

where: {
            [Op.or]: [{
                ownerid: {
                    [Op.in]: rows.map(row => row.ownerid),
                }
            }, {
                ownerid: {
                    [Op.notIn]: rows.map(row => row.ownerid),
                },
             shop: 1
            }]
        }

will generate

select *
    from users 
    WHERE (ownerId in ('12', '166') )  or    
        (ownerId not IN ('12','166')  and shop = 1 )

in postgresql

I want to add another condition to where ( UNION condition )to this where CONDITION : LIKE BELOW GIVEN

WHERE B->

where: {
        ownerid: {
                    [Op.eq]: 13 
                }
             shop: 1
        }

I want to generate

(
    select *
    from users
    where ownerId = 13 and shop = 1
)
union
(
    select *
    from users 
    WHERE (ownerId in ('12', '166') and `delete` is null)  or    
        (ownerId not IN ('12','166')  and shop = 1 and `delete` is null)
)

DESIRED RESULT

where: {
            [Op.or]: [{
                ownerid: {
                    [Op.in]: rows.map(row => row.ownerid),
                }
            }, {
                ownerid: {
                    [Op.notIn]: rows.map(row => row.ownerid),
                },
             shop: 1
            }]
        }

PLUS

where: { ownerid: { [Op.eq]: 13 } shop: 1 }

TO CREATE

(
        select *
        from users
        where ownerId = 13 and shop = 1
    )
    union
    (
        select *
        from users 
        WHERE (ownerId in ('12', '166') and `delete` is null)  or    
            (ownerId not IN ('12','166')  and shop = 1 and `delete` is null)
    )

PS : ID 13 is dynamic , the ownwer id 13 can change to another numbers.

CodePudding user response：

There is no need to use union if you want to join records from the same table but only with different conditions. You can just join them by OR.
The SQL might look like this:

select *
from users 
WHERE (ownerId in ('12', '166') and `delete` is null)  or    
  (ownerId not IN ('12','166')  and shop = 1 and `delete` is null) or
  (ownerId = 13 and shop = 1)

Sequelize query condition might look like this:

where: {
  [Op.or]: [{
    ownerid: {
      [Op.in]: rows.map(row => row.ownerid),
    }
  }, {
    ownerid: {
      [Op.notIn]: rows.map(row => row.ownerid),
    },
    shop: 1
  }, {
    ownerid: 13,
    shop: 1
  }]
}