I am trying to achieve this SQL query in yii2
select u.id,u.username,u.firstname,m.datetime from `accounts` as u
INNER JOIN
(
select max(id) as id,to_id,from_id,datetime from `messages` where to_id = '1' or from_id = '1' GROUP BY to_id,from_id
)
m ON u.id = m.from_id or u.id = m.to_id where (u.id != '1') GROUP BY u.id
ORDER BY m.id DESC
using the code
$ChatMessageRecipientUserTable = 'accounts';
$query = new \yii\db\Query();
$subQuery = (new \yii\db\Query())
->select(['MAX(id) as id','to_id','from_id','datetime'])
->from('messages')
->where(['to_id' => Yii::$app->user->identity->id])
->orWhere(['from_id'=> Yii::$app->user->identity->id])
->groupBy(['to_id', 'from_id']);
$query->from(['u' => $ChatMessageRecipientUserTable])
->select(['u.id','u.username','u.firstname','m.datetime'])
->innerJoin(['m' => $subQuery],['or',
'u.id = m.from_id',
'u.id = m.to_id',
])
->where(['!=', 'u.id', Yii::$app->user->identity->id])
->groupBy(['u.id'])
->orderBy([
'm.id'=>SORT_DESC
])
->all();
$rows = Yii::$app->db->createCommand($query)->queryAll();
while ($row = $rows) {
$id = $row['id'];
}
but I get the error:
Database Exception – yii\db\Exception
SQLSTATE[HY093]: Invalid parameter number: no parameters were bound
The SQL being executed was: O:12:"yii\db\Query":21:{s:6:"select";a:4:{s:4:"u.id";s:4:"u.id";s:10:"u.username";s:10:"u.username";s:11:"u.firstname";s:11:"u.firstname";s:10:"m.datetime";s:10:"m.datetime";}s:12:"selectOption";N;s:8:"distinct";N;s:4:"from";a:1:{s:1:"u";s:14:"accounts";}s:7:"groupBy";a:1:{i:0;s:4:"u.id";}s:4:"join";a:1:{i:0;a:3:{i:0;s:10:"INNER JOIN";i:1;a:1:{s:1:"m";O:12:"yii\db\Query":21:{s:6:"select";a:4:{s:2:"id";s:7:"MAX(id)";s:5:"to_id";s:5:"to_id";s:7:"from_id";s:7:"from_id";s:8:"datetime";s:8:"datetime";}s:12:"selectOption";N;s:8:"distinct";N;s:4:"from";a:1:{i:0;s:12:"messages";}s:7:"groupBy";a:2:{i:0;s:5:"to_id";i:1;s:7:"from_id";}s:4:"join";N;s:6:"having";N;s:5:"union";N;s:11:"withQueries";N;s:6:"params";a:0:{}s:18:"queryCacheDuration";N;s:20:"queryCacheDependency";N;s:27:"yii\base\Component_events";a:0:{}s:35:"yii\base\Component_eventWildcards";a:0:{}s:30:"yii\base\Component_behaviors";N;s:5:"where";a:3:{i:0;s:2:"or";i:1;a:1:{s:5:"to_id";i:1;}i:2;a:1:{s:7:"from_id";i:1;}}s:5:"limit";N;s:6:"offset";N;s:7:"orderBy";N;s:7:"indexBy";N;s:16:"emulateExecution";b:0;}}i:2;a:3:{i:0;s:2:"or";i:1;s:16:"u.id = m.from_id";i:2;s:14:"u.id = m.to_id";}}}s:6:"having";N;s:5:"union";N;s:11:"withQueries";N;s:6:"params";a:0:{}s:18:"queryCacheDuration";N;s:20:"queryCacheDependency";N;s:27:"yii\base\Component_events";a:0:{}s:35:"yii\base\Component_eventWildcards";a:0:{}s:30:"yii\base\Component_behaviors";N;s:5:"where";a:3:{i:0;s:2:"!=";i:1;s:4:"u.id";i:2;i:1;}s:5:"limit";N;s:6:"offset";N;s:7:"orderBy";a:1:{s:4:"m.id";i:3;}s:7:"indexBy";N;s:16:"emulateExecution";b:0;}
Error Info: Array
(
[0] => HY093
[1] => 0
)