WHERE in (subquery) clause yii

Question

I have a mysql query like this

 (SELECT 
    `notification`.`id`, `notification`.`user_id` AS `user_id`, `notification`.`activity_type`, `notification`.`source_id`, `event`.`title` as sourceName,concat_ws(" ",v.firstname,v.lastname) as ActorName
 FROM 
    `notification` 
 INNER JOIN 
    `event` 
 ON 
     event.id = notification.source_id 
 INNER JOIN 
     `user` as v
 ON 
      v.id = notification.user_id 
 AND 
      notification.activity_type = "checkin" 
 where 
      user_id in 
      (SELECT friend.friend_id from friend WHERE friend.user_id=1 AND friend.is_active=1)) 
 UNION 
(SELECT 
      `notification`.`id`, `notification`.`user_id` AS `user_id`, `notification`.`activity_type`, `notification`.`source_id`, concat_ws(" ",u.firstname,u.lastname) as sourceName,concat_ws(" ",v.firstname,v.lastname) as ActorName 
 FROM 
      `notification` 
 INNER JOIN 
      `user` as u 
 ON 
       u.id = notification.source_id 
 INNER JOIN 
      `user` as v ON v.id = notification.user_id 
 AND 
       notification.activity_type = "friend" 
 where user_id in 
       (SELECT friend.friend_id from friend WHERE friend.user_id=1 AND friend.is_active=1) )

And i want to write that query in yii2 and i dont know how to write subquery in where in clause.

So far i have done this

$query2 ->select(['notification.id', 'notification.user_id AS user_id', 'notification.activity_type', 'notification.source_id', 'concat_ws(" ",u.firstname,u.lastname) as sourceName','concat_ws(" ",v.firstname,v.lastname) as ActorName','user_image.imagepath as image'])
            ->from('notification' )
            ->innerJoin('user as u', 'u.id = notification.source_id')
            ->innerJoin('user_image','user_image.user_id = notification.user_id')
            ->innerJoin('user as v', 'v.id = notification.user_id AND notification.activity_type = "friend"')
            ->where('user_image.imagetype="profile"')
            ->andWhere(['user_id'=>('SELECT friend.friend_id from friend WHERE friend.user_id='.$id.' AND friend.is_active=1')]);

$query  ->select(['notification.id','notification.user_id AS user_id','notification.activity_type', 'notification.source_id', 'event.title as sourceName','concat_ws(" ",v.firstname,v.lastname) as ActorName','organiser.image as image'])
            ->from('notification')
            ->innerJoin('event', 'event.id = notification.source_id')
            ->innerJoin('organiser','organiser.organiser_id = event.organiser_id')
            ->innerJoin('user as v', 'v.id = notification.user_id AND notification.activity_type = "checkin"')
            ->Where(['in', 'user_id', [488,489]])
            ->union($query2);

Which generates the command query like this

(SELECT 
    `notification`.`id`, `notification`.`user_id` AS `user_id`, `notification`.`activity_type`, `notification`.`source_id`, `event`.`title` AS `sourceName`, concat_ws(" ",v.firstname,v.lastname) as ActorName, `organiser`.`image` AS `image` 
FROM 
    `notification` 
INNER JOIN 
    `event` 
ON 
     event.id = notification.source_id 
INNER JOIN 
    `organiser`
ON   
     organiser.organiser_id = event.organiser_id 
INNER JOIN 
     `user` `v` 
ON 
     v.id = notification.user_id 
AND 
      notification.activity_type = "checkin" 
WHERE 
     `user_id` IN (:qp0, :qp1))
UNION 
( SELECT 
     `notification`.`id`, `notification`.`user_id` AS `user_id`, `notification`.`activity_type`, `notification`.`source_id`, concat_ws(" ",u.firstname,u.lastname) as sourceName, concat_ws(" ",v.firstname,v.lastname) as ActorName, `user_image`.`imagepath` AS `image` 
FROM 
     `notification` 
INNER JOIN 
     `user` `u` 
ON 
      u.id = notification.source_id 
INNER JOIN 
      `user_image` 
ON 
       user_image.user_id = notification.user_id 
INNER JOIN 
      `user` `v` 
ON 
       v.id = notification.user_id 
AND 
       notification.activity_type = "friend" 
WHERE 
      (user_image.imagetype="profile") AND (`user_id`=:qp2) )

But its not working,so what is the right syntax and also feel free to give suggestion if where in could be write with inner join

that would make it very easy to write query

thank you

Answer 1

Try This




$query = (new Query())
            ->select('notification`.`id`, `notification`.`user_id` AS `user_id`, `notification`.`activity_type`, `notification`.`source_id`, `event`.`title` as sourceName,concat_ws(" ",v.firstname,v.lastname) as ActorName')
            ->from('notification')
            ->innerJoin('event' ,'event.id = notification.source_id')
            ->innerJoin('user v' ,'v.id = notification.user_id')
            ->where([
                'user_id' => (new Query())
                    ->select('friend.friend_id')
                    ->from('friend')
                    ->where([
                        'friend.user_id' => 1,
                        'friend.is_active' => 1
                    ])
            ])->andWhere([
                'notification.activity_type' => "checkin"
            ]);

        $query2 = (new Query())
            ->select('`notification`.`id`, `notification`.`user_id` AS `user_id`, `notification`.`activity_type`, `notification`.`source_id`, concat_ws(" ",u.firstname,u.lastname) as sourceName,concat_ws(" ",v.firstname,v.lastname) as ActorName')
            ->from('notification')
            ->innerJoin('user v' ,'v.id = notification.user_id')
            ->where([
                'user_id' => (new Query())
                    ->select('friend.friend_id')
                    ->from('friend')
                    ->where([
                        'friend.user_id' => 1,
                        'friend.is_active' => 1
                    ])
            ])->andWhere([
                'notification.activity_type' => "checkin"
            ])->union($query)->all();

Answer 2

You can use Yii2 Query Builder, here is an example.

$subQuery = (new \yii\db\Query())->select([
                  'users.id as userId', 
                  'users.first_name', 
                  'users.username',
                  'users.last_name', 
                  'users.sector_id',
              ])
              ->from(['users'])
              ->where(['=', 'role_id', 3])
              ->orWhere(['=', 'role_id', 2])
              ->groupBy('users.id');

$query =  (new \yii\db\Query())->select(['user.*', 'user_address.*'])
            ->from(['user'=>$subQuery])
            ->leftJoin('user_address', 'user.userId = user_address.user_id')
            ->where(['between', 'latitude', $min_lat, $max_lat])
            ->andWhere(['between', 'longitude', $min_lon, $max_lon])
            ->groupBy('user.userId');