Problems with "not equals" operator and multiple page references

[nik]

We've got pages and tags. Pages use template "company" and the template has a Page-field called "tags" (input method is checkboxes, template restricted to "tag"). Like this:

c1 (tags: "t1")

c2 (tags: "t1", "t2")

c3 (tags: "t2", "t3")

c4 (tags: none)

Finding companies having (at least) one of the tags t2 or t3 would be:

$pages->find("template=company, tags.title=t2|t3");

This returns pages c2 and c3 as expected. So far so good.

But when we're trying to find pages without certain tag(s) selected, results aren't what we expected them to be. Like this:

$pages->find("template=company, tags.title!=t1");

This gives again pages c2 and c3. There's obviously at least two things wrong with the resultset:

- page c2 does have tag t1 --> should not be included

- page c4 does not have tag t1 --> should be included

And finally, what we were actually trying to achieve was finding pages with tag t3 but without tag t2:

$pages->find("template=company, tags.title=t3, tags.title!=t2");

And this gives page c3, which is more or less understandable given the previous example of != not working the way it's supposed to be. Still it's wrong.

It seems to me that != operator does not work correctly with fields having more (or less!) than one row per page in their database table. This just isn't covered with the way JOINs are used at the moment (and once again I'm not skilled enough to say how it should be done).

I'm going to be away from the forums for a few days but I'm sure teppo will take over the conversation if there's any details missing regarding the way we're trying to do this. And yes, we're aware using filter() for the != -part would probably solve this, partly. Still the selectors don't work as expected and pagination would have to be handled somehow.

(Edit: fixed a typo 'page t3' -> 'page c3' so that it wouldn't be misleading)

Edited October 25, 2012 by nik

[ryan]

This one has been on the @todo list for a long time, as it's kind of a tricky query to implement. Go ahead and try out the latest dev branch, which should implement this capability. So far it seems to be working here, but could definitely use more testing.

[nik]

Thanks Ryan! Seems to be working here as well. Just had a quick test with those selectors mentioned above, nothing more than that though. Will let you know if anything comes up after trying this out in the actual scenario where there are a couple of more selectors involved too.

That "LEFT JOIN ... WHERE xx IS NULL" -thingie is something I never seem to get right at once. But I'm glad you did.

[teppo]

Ryan: I've run some tests regarding this fix and so far it seems to work as intended. Haven't had an opportunity to really test it thoroughly, though, which is a shame. Anyway, do you have any further information about when this might end up in master -- is that unlikely to happen before 2.3 is out? And as a related question, have you already decided when 2.3 might come out?

In the latter case I might have to copy related changes for this client or make their site use dev branch, though that's not something I'd like to do without a very good reason..

[ryan]

Anyway, do you have any further information about when this might end up in master -- is that unlikely to happen before 2.3 is out? And as a related question, have you already decided when 2.3 might come out?

The current dev branch is meant to be 2.3. I'm planning to add a little more to do it, but will be final before the years end. I do think its safe to use the dev branch if you keep an eye on it. I am currently using the dev branch in all my projects. The only reason I'm not bringing the changes over to master individually is because there are a couple bigger ones in there (database session support and change to blowfish for password hashing). I think those need to accompany a version number for safety. But I could definitely use help testing the dev branch since it will become 2.3 very soon.

[nik]

Look's like this bug has been fixed only partially. Using the examples from my opening post, this selector works now as expected (giving pages c3 and c4):

$pages->find("template=company, tags.title!=t1");

But if page id is being used, the old bug remains. Given that tag page t1 has id 1234, this still returns pages c2 and c3:

$pages->find("template=company, tags!=1234");

And this is dev branch I'm using here.

[ryan]

Thanks for finding that Nik. I've tried to find a simple fix, but no luck so far. It seems that I break a lot of the other tests with nearly any solution I implement, so far. So going to need to find time to get further into this soon. Do you have one of these in the tests?

[nik]

Nope, not yet. But I'll try and find a way to reproduce it with skyscraper data. Should be quite easy.

[nik]

@ryan: Now there's a couple of tests on this issue in the test suite. Looks like the in-memory versions are broken too (both with id and subfield notation).

[ryan]

@nik these should now all be working with the latest version of the test suite and latest commit to ProcessWire dev. Thanks for your help in tracking down these issues.