yii2 mysql in_mysql – WHERE in(子查询)子句yii

我有这样的mysql查询

(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) )

我想在yii2中编写该查询,我不知道如何在where子句中编写子查询.

到目前为止我已经这样做了

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

这会生成如下命令查询

(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) )

但它不起作用,那么正确的语法是什么

如果在哪里可以用内连接写,也可以随意提出建议

这样可以很容易地编写查询

谢谢

解决方法:

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

来源：https://www.icode9.com/content-2-305201.html