WHERE in (subquery) offer yii

Question

I have a request mysqllike 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 this query in yii2, and I don't know how to write a subquery in wherethe section.

So far i have done it

$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);

What generates a command request 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 the correct syntax and also feel free to suggest if wherein can write withinner join

which will simplify the recording of the request

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');