Model filter 'IN' with null not working
Developer Preview
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 NULLWill 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.