Hi! I just started using Yii 2 as my first non-custom framework and I’m trying to figure some stuff out, so here we go:
[size=“4”]Where I’m at[/size]
I have a User model and a Visitor model(both ActiveRecord) and would like to log and display when a user last visited a users profile. What I’m doing now is this:
$userObject = new User();
$profileOwner = $userObject->findOne($userId);
if (!$userId || !$profileOwner) {
throw new NotFoundHttpException('Invalid user profile.');
}
// Adds to visitor log
$visitorEntry = new Visitor();
$visitorEntry->profile = $profileOwner->id;
$visitorEntry->viewer = \Yii::$app->user->id;
$visitorEntry->time = new Expression('NOW()');
$visitorEntry->save();
$visitors = $profileOwner->getVisitor()->joinWith('user')->all();
[size="4"]Problem #1[/size]
The add to visitor log has a primary key on (profile, viewer), so what I would like in this situation is to run the save() command as an INSERT <…> ON DUPLICATE KEY UPDATE query. I do realize that I can try to get the row first and then just update if it exists, but from an efficiency standpoint that would be a bad decision.
What is the cleanest and best way to make it use the MySQL on duplicate functionality?
[size="4"]Problem #2[/size]
The joinWith(‘user’) results in these queries:
SELECT `visitor`.* FROM `visitor` LEFT JOIN `user` ON `visitor`.`viewer` = `user`.`id` WHERE `visitor`.`profile`=1
SELECT * FROM `user` WHERE `id` IN (1, 2)
Is there any way to avoid the second select and make it become one query like the one below?
SELECT `visitor`.*, `user`.* FROM `visitor` LEFT JOIN `user` ON `visitor`.`viewer` = `user`.`id` WHERE `visitor`.`profile`=1
SELECT * FROM `user` WHERE `id` IN (1, 2)
I do realize that it supports multiple database systems, but I hope there is still some easy and clean way to use such specific features so one don’t have to compromise on database performance.