Nested Queries

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

what was the old command!

Doh! Yeah that would be helpful to mention. Also in the update above.

Original Sql Query:

SELECT * FROM sites WHERE id NOT IN(SELECT site_id FROM reservations WHERE arrival <= '$departure' AND departure >= '$arrival')

Sorry for confusing question. I meant what is the original function getAvailable() you are trying to port look like and also its respective/expected SQLquery?

The old project was not oop based and was a project i did in spare time after I learned the basics of php about 7 years ago, it is a disaster in itself, with about 12 files and thousands of lines in each, all with no classes. So there was no getAvailable() function. So its more of a complete overhaul/rewrite than a port over I’d say.

The sql I posted is the original sql query.

A bit more on what im trying to accomplish, think of sites as rooms. I have an actionAvailablesites in my reservation controller which its purpose is to return an array of available sites based on the arrival and departure dates specified. The reservation controller receives the dates via a curl post, it sets the values in the model and then it calls the getAvailability() function, getAvailability() should perform the query with the dates in the model and return a list of all the available sites.

The original sql statement works great, but I cant seem to figure out how to Yii’ify the sql, and get the same results, everything just kept coming back blank, but performing the queries without nesting them would return back results, of course not the results i needed.

Or is the way i did in my update on the first post the best practice way to do this?

Hope that clarifies things up. If not ask away and i’ll try my best.

am doing something else. I’ll be back here!