Hello all,
I am in the process of porting an old booking api project of mine over to Yii2 and I have to say, I am loving this framework.
I’m now converting my old sql queries in string form to either ActiveRecord or QueryBuilder and im not having much luck with this one part. I keep getting blank responses back. So I decided to see what the sql command being generated was and it is failing miserably. I have a feeling im missing something but I cant figure it out. Been at this same section for 3 days now, with absolutely no progress.
Here is inside the Reservations model:
public static function getAvailable()
{
$query = new Query();
$subQuery = (new Query())->select('site_id')
->from('reservations')
->where('arrival <= :departure AND departure >= :arrival')
$subQuery->addParams([':arrival' => $this->arrival, ':departure' => $this->departure]);
$query->select('*')
->from('sites')
->where(['not in', 'id', $subQuery]);
$command = $query->createCommand();
return $command->sql;
}
Outputs:
SELECT * FROM `sites` WHERE `id` NOT IN (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
So my thinking was that my subQuery is the problem, I changed the command to output the subquery string.
Which returned:
SELECT `site_id` FROM `reservations` WHERE ((arrival <= :arrival AND departure >= :$arrival) OR (arrival <= :departure AND departure >= :$departure)) OR (arrival >= :arrival AND departure <= :departure)
Looks like the $subQuery->addParams is not replacing the content as i was convinced it should, so to test even further I hardcoded values in replace of :arrival and :departure and tested the subquery and I got results.
Thinking I was making some kind of progress I reverted back to $command = $query->createCommand(); to check the sql string, still with the hardcoded values in the subQuery.
My Result:
SELECT * FROM `sites` WHERE `id` NOT IN (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
I tried the same using ActiveRecord and got the same responses as above. Wth am I doing wrong?
UPDATE:
I’m now using the solution below as a workaround to let me continue, but im not happy having this code in multiple places. If anyone knows of a solution or where I might have went wrong, please give my hair some relief by giving me a hand. xD
$sql = "SELECT * FROM sites WHERE id NOT IN(SELECT site_id FROM reservations WHERE arrival <= '$departure' AND departure >= '$arrival')";
return sites::findBySql($sql)->all();