I’m having an issue where I’m trying to make something backward compatible with some changes I’ve made so I’m doing this:
$model->filter('my_col', 'IN', array(
null,
0,
''
));
The problem is, when I do this I get no results from rows with null columns . If I change the columns to 0 or empty string, I do get results. However, if I do this:
$model->filter('my_col', null);
I do get results from rows with null columns. So there seems to be some discrepancy here. Any thoughts?
There is currently not an exception with the IN
operator for null handling, which is necessary in MySQL; the IN
operator won’t work with null
as an option, and must be used with the special IS NULL
where clause. E.g.:
WHERE `field` IN (null, 0, '')
Will not return rows where field
is null
, so:
WHERE `field` IN (0, '')
OR `field` IS NULL
Will return all three. Applied to models, that would mean:
$model->filter('field', 'IN', array(0, ''))
->orFilter('field', null);
Though I see that it would be valuable here for the models to handle that for you, further abstracting the logic that is dictated by MySQL, so I’ll bring it up with the team and see if it’s something we could handle in the future.
Oooooo, that makes sense. For whatever reason, it did not occur to me this was a MySQL problem. I guess I’ve never tried to do an IN
query with null before.
I’d love to have the the models handle that, though I’ve actually refactored and changed some schema to get around the issue in this case.
Packet Tide owns and develops ExpressionEngine. © Packet Tide, All Rights Reserved.