Thread

Hi everyone,

Stumbled upon an issue and I’m not sure I’m doing anything wrong or EE is not behaving as it should.

I have an existing channel with 3 custom fields. I’ve added a Select Dropdown with manual values “yes” and “no”. There are a number of existing entries in this channel.

In my template, I want to retrieve the items that either have the value “yes” or haven’t been (or won’t be!) edited with this new Select Dropdown in place.

I’ve tried:
search:field_name=“not no”
search:field_name=“yes|IS_EMPTY”

and several combinations, ludicrous as they might seem, without results.

Except for two items that I have updates to have the value “yes”, I have done nothing to the existing entries. The last option “yes|IS_EMPTY” works well in finding these 2 items.

I was expecting “not no” would retrieve non-edited entries. Okay, not so. Why doesn’t “IS_EMPTY” give me the unedited entries?

I’ve read and reread http://ellislab.com/expressionengine/user-guide/modules/channel/parameters.html#par_search, but I must have overlooked sth.

Any clues?

This is Version 2.1.3 Build 20101220.

Thanks!... Nico

Nico,

Can you try the following please. This is an “Exact” match and would return all results where the field_name is empty or is “yes”.

search:field_name="=IS_EMPTY|yes"

But if you think about it your dropdown only has two options so an entry will either be yes or no and never empty

Try this one instead

search:field_name="=no"

Does any of that help?

Note we released EE2.2 yesterday

Hi John,

Thanks for your response!

Nope, that does not help, unfortunately. I still only get the 2 entries I specifically set to “yes”. I’ve checked the database, BTW, and all records contain a NULL value except for these 2.

I’ll see if an upgrade to 2.2 helps!

Thx… Nico

Nico, have you tried resaving all the entries with the appropriate yes/no selection? When you add a select field like this the current entries aren’t automatically updated so the yes/no field will still be empty.

Thx @bluedreamer. 😊

Re-editing and saving all existing entries is exactly what I’m trying to avoid. That’s a lot of work (and it shouldn’t be necessary).

If EE could find empty entries (“empty or yes”), I would be happy. “not no” would seem like “empty or yes” to me, logically, but that’s not working so far.

Will upgrade and see what happens. 😊

A quick note: upgrading to 2.2.0 did not fix this problem. “IS_EMPTY” returns nothing; “not no” only return entries with “yes” in them. “IS_EMPTY|yes” returns nothing.

Is this considered a bug?

Thx… Nico

Hi Nico- it’s the NULL that’s causing the problem. 

The bear being- NULL isn’t going to match an empty string, which is what the search is looking for.  This issue was addressed some time ago- basically it SHOULD be defining it as an empty string before inserting it.  But if you have entries from prior to that change, they may be in as NULL and hence not found.

Probably the easiest solution is to manually swap out any null values in that field for an empty string.  I can show you a query that will do it for you, but are you comfortable doing such a query?  Do you have a current backup of your database and the exp_channel data table and are you comfortable rolling back to the backup if something goes wrong?  Also- what is the field id number for the field in question?  If you go to edit the field, you’ll be able to see the id in the url.

Hi Robin,

Thanks for your response!

Probably the easiest solution is to manually swap out any null values in that field for an empty string.  I can show you a query that will do it for you, but are you comfortable doing such a query?  Do you have a current backup of your database and the exp_channel data table and are you comfortable rolling back to the backup if something goes wrong?  Also- what is the field id number for the field in question?  If you go to edit the field, you’ll be able to see the id in the url.

This is no problem whatsoever. I’m perfectly okay modifying the table and will do shortly.

Still, to me it is not logical that IS_EMPTY does not match both an empty string (field_id_*=’‘) and a NULL value (field_id_* IS NULL). 😉

Thanks again!... Nico

It’s actually mysql rather than our choice.  Which- yes.  Very annoying.  So we basically had two options- change the search code to search for ‘’ OR IS_NULL- which makes an already complex query even more complex, or make sure we’re only entering the data in one format.  For optimization reasons, we didn’t want to add onto that query- so now we clean the data up when it’s entered.  If it’s not set- it should be set as an empty string- avoiding the NULL issue.

But yes- agreed.  It was irksome.

If you do run into any issues on this one?  Yell back.  If not- do let us know whether all is now good to go!