Union in subquery

[kakalos12]

Hi there, I'm facing a problem with union. Here is my code.

        $bounceQuery = (new Query())->select('email')->from('bounces');
        $complaintQuery = (new Query())->select('email')->from('complaints')->where(['user_id' => $this->user_id]);
        $subquery = $bounceQuery->union($complaintQuery);
        return $this->getSubscribers()->andWhere(['status' => Subscribers::SUBSCRIBED]) -> andWhere(['not in', 'email' , $subquery ]);`

It generates something like this :

SELECT * FROM `subscribers` WHERE ((`status`=1) AND (`email` NOT IN ((SELECT `email` FROM `bounces`) UNION ( SELECT `email` FROM `complaints` WHERE `user_id`=3 )))) AND (`list_id`=5)`

Which is invalid. I don't know why it is invalid but if I remove the parenthesis of the 2 select clause of the union clause, it works. Working sql (tested with phpmyadmin):

SELECT * FROM `subscribers` WHERE ((`status`=1) AND (`email` NOT IN (SELECT `email` FROM `bounces` UNION  SELECT `email` FROM `complaints` WHERE `user_id`=3 ))) AND (`list_id`=5)`

Error in the file attachment

Working sql

Any suggestion ?

[cebe]

related to #7992

[SilverFire]

@kakalos12 I've removed your PhpMyAdmin screenshot because of emails disclosure. Please, re-upload it without email column. Thank you.

[kakalos12]

@SilverFire I have re-uploaded it. Any quick solution for this ?

[SilverFire]

Thank you. Unfortunately, no

[kakalos12]

I'm thinking about another solution for my project. Maybe I'll use ActiveRecord's findBySql to get the list, But Will it be able to use with "each()" ? I don't know how to test this.

[cebe]

findBySql() creates a query object just like find() so there should be nothing stopping you from using each(). If you find any issues, please report them.

[kakalos12]

@cebe Thanks so much. I'll try