Select WHERE IS NULL in Query Builder?

Yii 1.1 General Discussion
1

Using the Query Builder that is CDbCommand, how do I select a value that "IS NULL", in MySQL?

Trying to accomplish the following:


SELECT COUNT(*) FROM `console` a LEFT JOIN `console_images` b ON a.id = b.console_id WHERE b.console_id IS NULL

So my query builder looks like this:


$countConsolesMissingImage = Yii::app()->db->createCommand()

	->select('count(*)')

	->from('console c')

	->join('console_images i', 'c.id=i.console_id')

	->where('i.console_id IS NULL') // This does NOT work!

	->queryRow();

This does not return the results I need, and I couldn’t find an example of the where function defining something as “IS NULL”. Is there no way to do this using the query builder?

2 

SELECT COUNT(*) FROM `console` a LEFT JOIN `console_images` b ON a.id = b.console_id WHERE ISNULL(b.console_id)



$countConsolesMissingImage = Yii::app()->db->createCommand()

        ->select('count(*)')

        ->from('console c')

        ->join('console_images i', 'c.id=i.console_id')

        ->where('ISNULL(i.console_id)') // This should work…

        ->queryRow();
3

That is not valid MySQL syntax.

Try: ‘WHERE column IS NULL’

ISNULL(column) is used in SQL server and Sybase.

4

That’s weird. I’ve just seen your post and ISNULL() and ‘xxx IS NULL’ are comparison operators.

My first attempt was WHERE xxx IS NULL and it didn’t work on a datetime field, and then ISNULL() worked. Is it a coincidence or is it something special with datetime fields?

Edit: I tried with a varchar field (with default NULL value) and it doesn’t return records even when the field is empty. Anyway, on datetime and int fields, it does work on my phpMyAdmin and in Yii.

5

@tackle,

Your code should work imo.

But your SQL query looks strange to me :

You are joining two tables two table together, and check in your where condition if the b.console_id is null. How can you join tables with a NULL FK ?

6

You might be right on the money that I’m not proficient enough with SQL queries.

What I’m trying to do is join two tables together (the ON clause?), based on the console.id correlating to the console_images.console_id.

With that result, check if the console_images.console_id is null. Might not be the right way to go about it though.

A ‘console’ can have zero or one ‘console_images’. I want to see how many console does NOT have one console_images.

The SQL I provided in my first post gives me the exact result I want, so I’m torn on whether I’m failing the SQL part or the PHP part :huh:

7

Hmmm you may then use something like "SELECT * FROM table_1 WHERE id NOT IN (SELECT id FROM table_2)"?

8

Ok, I see.

I missed the LEFT join on my first read.

But I think Yii sql builder ‘join’ method will make a ‘INNER JOIN’ (default mysql join), which won’t return null in second table.

So, you need to change your query to :




$countConsolesMissingImage = Yii::app()->db->createCommand()

        ->select('count(*)')

        ->from('console c')

        ->leftJoin('console_images i', 'c.id=i.console_id')   // <-- Change join type here

        ->where('i.console_id IS NULL') 

        ->queryRow();

I think it should work.

tellibus’ query should also work, but it uses a subquery, which may be less efficient than a join.

9

I’m curious, how could this be done with a CDbCriteria?

I’m a in a situation where this would be very useful as well in my application.

10 

$criteria = new CDbCriteria;

…

$criteria->addCondition('id NOT IN (SELECT table1_id FROM table2)');

table1_id would be the foreign key