How to find elements with empty field

[elmr]

Hi, I have a problem with selecting all the pages whose field, for example "color" has empty value. Is it possible with find() method or do I have to select all pages and check in loop if this field is empty or not?

[Pete]

I think you could just do this:

$pages->find('color=');

[elmr]

Unfortunately this one doesn't work for me. Just no results.

[Soma]

Just wondering, are you using the ColorPicker module for this field? Just because you mention color as example.

If that's the case it returns "FFFFFF" as default value, if nothing else yet saved. Considering it, it may not the best default value for a colorfield.

[elmr]

No no, color was just an example It's text field.

[Soma]

Ok, then Pete's example should work. Can you provide more informations what you're trying to do? What kind of textfield? Make sure there's really no value. Can you show some code?

[apeisa]

It depends on what kind of field your "color" is.

If it is a textarea, integer or other "basic", then you can check it like this:

color=0

If it is page field, then this will do the job:

color.count=0

EDIT: Leaving it empty just like Pete and Some suggested works too,

[apeisa]

Little OT: How can I choose integer fields with value 0, but not empty? (not that I need that, just realized that color=0 does match both, empty and with value 0.

[Soma]

For text fields, yes color=0 will also work. [Edit: Maybe not the best one]. Thanks apeisa!

I got another one also possible:

$pages->find("color=''");

[apeisa]

Hmm.. number=0 actually chooses pages that have no value at all (even if number is integer field). Only way that I have found to choose number fields that have explicitly set 0 as their value is number>-1, number<1

So it seems that 0 is universal and meaning more like a NULL there.

[Soma]

Little OT: How can I choose integer fields with value 0, but not empty? (not that I need that, just realized that color=0 does match both, empty and with value 0.

Edit: I think it's not possible

Edit: or is it "color=null" ? Hmm

[apeisa]

Yes, that works! Not something that I would have tried. More likely color='0'. Does parenthesis have any other uses in selectors?

elmr: sorry to taking over your topic. It was too interesting

[elmr]

None of above examples works for me :-/

I'm trying to get all the movies which "youtube_id" field is empty (in fact I want to get all movies which youtube_id is not empty, but also != selector doesn't work for me and as I can see it's not implemented here https://github.com/r...ectFulltext.php while it exists in documentation, so I will stick to get all pages with empty youtube_id fields).

So what I'm doing is :

$movies = $pages->get('/'.$lang.'/movies')->find('youtube_id=, limit=10');

or

$movies = $pages->get('/'.$lang.'/movies')->find('youtube_id=0, limit=10');

etc.

That's the SQL query that shows up in debug mode :

SELECT SQL_CALC_FOUND_ROWS pages.id,pages.templates_id FROM `pages` JOIN pages_parents ON (pages_parents.pages_id=pages.parent_id AND (pages_parents.parents_id=1723 OR pages_parents.pages_id=1723 )) LEFT JOIN field_youtube_id ON field_youtube_id.pages_id=pages.id WHERE field_youtube_id.pages_id IS NULL AND (pages.status<1024) GROUP BY pages.id LIMIT 0,10 /* Selector: has_parent=1723, youtube_id=0, limit=10, status<1024 */ [0 rows] [0.0004s, 0.0836s, 0.21818995475769s]

Also I've checked in database and this field is really empty. Type of this field is just "Text". I'm using processwire 2.1

[Soma]

Just tested and it works fine in 2.2. Also if you use "youtube_id=, limit=10" you should use "youtube_id='', limit=10". Otherwise it assumes the limit is the value to be checked and won't work (in my local install)

I don't think it's any different in 2.1. Would have to test.

You can just use "youtube_id!='', limit=10" and it should work fine, I remember using it even in 2.1. Maybe Ryan got some more insights.

[Soma]

Yes, that works! Not something that I would have tried. More likely color='0'. Does parenthesis have any other uses in selectors?

elmr: sorry to taking over your topic. It was too interesting

yup it seems to work.

ehrm paranthesis would be for things like ("body='no, I can'") ?

[apeisa]

Updating to latest commit is very fast and should be safe (always backup or make a copy where you test) from 2.1 to 2.2. Just remember to update index.php and .htaccess also, other than that it is just replacing the wire folder.

Also: I have had once or maybe twice in after one year of developing with pw strange issues, when selectors didn't work. Saving certain pages (like /lang/movies/ or /lang/ or / in your case) resolved everything and selectors started working. I don't have a clue why or how that occurs.

[elmr]

Just tested and it works fine in 2.2. Also if you use "youtube_id=, limit=10" you should use "youtube_id='', limit=10". Otherwise it assumes the limit is the value to be checked and won't work (in my local install)

I don't think it's any different in 2.1. Would have to test.

You can just use "youtube_id!='', limit=10" and it should work fine, I remember using it even in 2.1. Maybe Ryan got some more insights.

I was also trying

find("youtube_id='', limit=10");

but it didn't work.

Now it's getting strange because for few minutes != operator was working (but still without empty records) and then again it's throwing errors

Fatal error: Exception: Unimplemented operator in DatabaseQuerySelectFulltext::match() (in \wire\core\DatabaseQuerySelectFulltext.php line 77) #0

Updating to latest commit is very fast and should be safe (always backup or make a copy where you test) from 2.1 to 2.2. Just remember to update index.php and .htaccess also, other than that it is just replacing the wire folder.

I'm afraid to update to 2.2 because I'm using Languages module and it's not compatible with new language features in 2.2. But for a test I replaced for a moment "wire" directory for the new one, from 2.2 and problem still existed for me...

[Soma]

elmr, I just tested for empty textfields in my local pw2.1 install, and it works fine. There must be something strange going on, like apeisa said.

Edit:

Maybe try a table database repair? Not sure what that error is all about. Waiting for Ryan is always the last resort

Edit:

Possibly also try to check if you got mal formed code, or any hidden chars. Even if you think its ok, it good to write new code in another file and try.

The error suggests that you use a selector that isn't matched like "*=, %=, = ..."

[elmr]

elmr, I just tested for empty textfields in my local pw2.1 install, and it works fine. There must be something strange going on, like apeisa said.

Edit:

Maybe try a table database repair? Not sure what that error is all about. Waiting for Ryan is always the last resort

Edit:

Possibly also try to check if you got mal formed code, or any hidden chars. Even if you think its ok, it good to write new code in another file and try.

The error suggests that you use a selector that isn't matched like "*=, %=, = ..."

Database repair done, code also checked, even in new file.

I'm using != operator so it should work, but when I look into the source (even the newest one on github : https://github.com/ryancramerdesign/P21/blob/dev/wire/core/DatabaseQuerySelectFulltext.php) it doesn't really exists, so I'm confused...

[Soma]

Strange as trying to track it down, I can't really tell why you get this error as to my understanding it isn't where it looks for if there's a "!=" in a selector string in PW.

Here is the selector class: https://github.com/ryancramerdesign/P21/blob/dev/wire/core/Selector.php

[Soma]

code also checked, even in new file.

Only new file and copied over? Have you tried rewrite the line of code that causes the issue from scratch? You laugh, but I got an situation where it just wouldn't work with a selector, turned out there was a hidden char not visible at all even in "showinvisible" mode.

[elmr]

Soma I've rewritten the code and it was the same but...another strange thing.

I thought it may be connected with pager so I removed rendering pager, disabled page numbers for this particular page and changed code to :

$pages->find('template=movie, limit=10')->not("youtube_id=''");

But it didn't show any results.

So I removed limit=10 and then it worked properly. Movies with empty fields didn't show up. But still I didn't have pager functionality, so I brought back rendering pager, enabled page numbers and added limit=10 again... and now it's working right... with the code and the same settings with which it was not working few minutes ago.

It's really strange and I have no idea what can be the reason of this behaviour :-/

[ryan]

It's really strange and I have no idea what can be the reason of this behaviour

Any time when I've had behavior like this it's because I was using either template caching or markup caching, and didn't realize I was seeing cached versions. So make sure you are logged in when changing around stuff (or that you aren't using caching).

We've also noticed that TinyMCE can store characters in text fields that aren't otherwise visible (even looking directly in the database). I had to go in with a hex editor before I could see the UTF-8 non-breaking space TinyMCE had inserted. So if your text field has been used with TinyMCE at some point, that's something to consider.

Table repair is also something to consider when you see strange results. That will cause MySQL to rebuild the index for the table. But I wouldn't expect this to be necessary unless someone has changed a MySQL setting (like ft_min_word_len) and not rebuilt all the database indexes.

[owzim]

I was just encountering the same problem but with another root of evil =)
 
I used it on a date field like: 

$pages->find("date_field!=''")

for finding pages with non empty date_fields, but it turns out, the selector engine is using the unformatted value of the fields (what else, silly me), in this case the value of an unformatted date_field is 0 and not "" (empty) which is what it looks like on the admin page.

So

$pages->find("date_field!=0")

works just fine.

[Yannick Albert]

Does someone know how to match a single image field in connection with showIf? To be a bit more specific, there's an image-field with "Maximum files allowed" set to "1" and a checkbox-field which should be only visible if image.count!=0. Both fields exist on the same page and aren't wrapped in a PageTable or Repeater (But in a Fieldset, does it matter?)...

All I tried did not work:

cover!=''
cover.url!=''
cover.count=1
cover.count>0
cover.count!=0
cover.filename!=''

The fun-fact: InputfieldSelector could find all those pages containing empty image fields with the tested selectors above.

Any ideas?

BTW: PW 3.0.11