Problem with OR statement and native page fields

[MadeMyDay]

Hello everybody,

following selector doesn't act as expected:

created_users_id|modified_users_id=1009

while these two do:

modified_users_id=1009

result (page id): 1002,1004,1006

created_users_id=1009

result (page id): 1004

complete statement:

wire('pages')->find('created_users_id|modified_users_id=1009')

result: 1004

I want to display all pages, which where created OR last modified by the user with ID 1009. For me it seems that this filter runs an AND filter, I just get one result where the user created and modified the page.

[Soma]

Not sure what's wrong. It works as it should for me... I would get "1002,1004,1006", it actually xor I think.

[diogo]

AND would get all the pages, while OR gets the pages from the first part, and if there isn't any, it gets the pages from the second. It seems to work as expected.

You should read it like this:

created_users_id|modified_users_id=1009 // get all pages that were created or the pages that were modified by this user
// (if the first is true, the second doesn't run)

created_users_id=1009, modified_users_id=1009 // get all pages that were created by this user, and that were also modified by this user
// (both must be true)

Use AND for what you want.

[MadeMyDay]

aaaaaaargh

[place Homer Simpson DOH here]

Thanks Diogo, that's it!

[Soma]

AND would get all the pages, while OR gets the pages from the first part, and if there isn't any, it gets the pages from the second. It seems to work as expected.

You should read it like this:

created_users_id|modified_users_id=1009 // get all pages that were created or the pages that were modified by this user
// (if the first is true, the second doesn't run)

created_users_id=1009, modified_users_id=1009 // get all pages that were created by this user, and that were also modified by this user
// (both must be true)

Use AND for what you want.

You're almost right. But it's not what he wants actually.

He wants to have all pages that where created XOR modified. Using "," will only return pages that are modfied and also created by the user, like you mention (both must be true). So it's "only possible" with splitting it in two separate queries and merge them.

[ryan]

The problem here is that ProcessWire doesn't actually support OR selectors for fields native to the 'pages' table (id, modified, created, modified_users_id, created_users_id, status, name). You can use OR in the value, just not in the field. So the query is just getting converted to this:

created_users_id=1009

The reason there isn't OR support for those 7 native fields is just that it hadn't come up as a need before. However, I just added it after seeing this message. I want to test it for a day or two before committing to the source to be safe. But attached is the updated file (/wire/core/PageFinder.php) if you want to test it out.

PageFinder-php.zip

[MadeMyDay]

Hey Ryan, thanks! How is the selector supposed to look like?

created_users_id=1009|modified_users_id=1009

produces the same result as above (like where created_users_id=1009 AND modified_users_id=1009)

created_users_id|modified_users_id=1009

seems to skrew up everything (identically for all results without filter)

edit: Not true, generates the same as above.

Problem is: I have a more complex environment which also produces weird results when I set a get('/') before filtering.

This is the code, I hope it is not too weird, PHP skills are a bit rusty (if ever existed):

<?php
/**
* ProcessWire 'Hello world' demonstration module
*
* Demonstrates the Module interface and how to add hooks.
*
* ProcessWire 2.x
* Copyright (C) 2010 by Ryan Cramer
* Licensed under GNU/GPL v2, see LICENSE.TXT
*
* http://www.processwire.com
* http://www.ryancramer.com
*
*/
class WidgetListPages extends Widget {
/**
 * getModuleInfo is a module required by all modules to tell ProcessWire about them
 *
 * @return array
 *
 */
public static function getModuleInfo() {
 return array(
  // The module'ss title, typically a little more descriptive than the class name
  'title' => 'ListPages Widget',
  // version: major, minor, revision, i.e. 100 = 1.0.0
  'version' => 001,
  // summary is brief description of what this module is
  'summary' => 'Widget which shows pages in a table with optional edit and view buttons.',
  // singular=true: indicates that only one instance of the module is allowed.
  // This is usually what you want for modules that attach hooks.
  'singular' => false,
  // autoload=true: indicates the module should be started with ProcessWire.
  // This is necessary for any modules that attach runtime hooks, otherwise those
  // hooks won't get attached unless some other code calls the module on it's own.
  // Note that autoload modules are almost always also 'singular' (seen above).
  'autoload' => false,
  );
}
public function __construct() {
 $this->set('sortby', '-created');
 $this->set('parentid','/');
 $this->set('limit', 10);
 $this->set('lable', 'title');
 $this->set('ownCreated', false);
 $this->set('ownModified', false);
 $this->set('restriction', 'page-edit');
 $this->set('createNew', false);
 $this->set('createNewLabel', $this->_('Create new page here'));
 $this->set('createNewParent', $this->parent);
}
public function init(){

}
public function render(){
 $adminUrl = $this->config->urls->admin;
 $this->filter = 'sort='.$this->sortby.',';
 $table = $this->modules->get("MarkupAdminDataTable");
 $table->setEncodeEntities(false);
 //show only documents of viewing user
 if($this->ownCreated===true && $this->ownModified===true){
  $this->filter.='created_users_id|modified_users_id=' . $this->user->id . ',';
 } else {
  if($this->ownCreated===true) $this->filter.='created_users_id=' . $this->user->id . ',';
  if($this->ownModified===true) $this->filter.='modified_users_id=' . $this->user->id . ',';
 }
 //debug
 echo $this->filter.'<br>';

 // this is the next weird thing: If I apply a parent some pages are missing (the ones directly under the root)
 $res = wire('pages')->get($this->parentid)->find($this->filter);
 foreach ($res as $result){
  //$output .= $result->title.'<br>';
  $table->row(array('<a href="'.$adminUrl.'page/edit/?id='.$result->id.'">'.$result->title.'</a>',$result->modified));
 }
 // apply create new page button?
 if($this->createNew){
  $button = $this->modules->get("InputfieldButton");
  $button->type = 'submit';
  $button->id = 'submit_new';
  $button->value = $this->createNewLabel;
  $table->action(array('create New' => $adminUrl.'page/add/?parent_id='.$this->createNewParent));
 }
 $this->content = $table->render();
 $out = $this->renderWidget();
 return $out;
}

}

Sorry for just pasting it, but have to go now. Perhaps you see something I totally miss (probably )

[ryan]

created_users_id|modified_users_id=1009

This is the correct syntax. It seems to work for me here when I test it. If you aren't getting the results you want, you might try adding "include=all" to the selector, as hidden and unpublished pages aren't included otherwise. If you find it still isn't working, paste in the result you are getting here. Then go back and edit those pages and click on the 'settings' tab. Click the 'info' fieldset to open it. It will say who created the page and who is the last modified user. Make note of which ones are incorrect, and whether it was the created or modified user that's incorrect, and include that information. This may help me to reproduce it.

[Soma]

Ryan, I also tested this and we were utterly confused. (in the irc)

"|" means OR, and it was working as it should (or is it different with textfields?) the first to find will be returned. Can you put some light into this?

Now after your change, it returns all pages that are found for both fields (created and modified). And this is confusing as I assume it's an OR. But that's not what he wants. He wants ALL pages that are either modified or changed by the user.

At the end we ended up again to what I suggested to split the find and merge them. Because it can't be done with one selector like "created=uid, modified=uid" as it will only return the pages both are true.

[MadeMyDay]

what Soma says ;-)

Problem is that "OR" can have a different meaning, depending of your are talking or if you are writing code. And there is also a difference between a SQL-"OR" and the field1|field2=x meaning.

I was looking for "either created or modified" by a user. While in SQL a created = userid OR modified=userid would be the result I was after, it is something different in PW with field1|field2=x. And it is also something different to use the filter field1=x,field2=x which would be a SQL-"AND". So a solution which would make it possible to use $pages->find(field1=x|field2=x) would be nice (or if it exists, an implementation for native fields).

[ryan]

"|" means OR, and it was working as it should (or is it different with textfields?) the first to find will be returned.

1. That behavior is only useful when doing something like $page->get("headline|title"); where you'd like the headline, but will settle for the title if there's no headline. Literally saying I'll take headline OR title, but I prefer headline." That behavior works there because we're not talking about retrieving pages, we're just talking about retrieving the first non-empty value. Order matters here because we're stating a preference for headline since it is mentioned first.

2. When you use OR "|" in a selector to retrieve pages, you can use "|" between field names, like "title|body*=something". That literally translates to "Find all pages that have the word 'something' in either the title OR body. Order here means nothing, because if it matches in either title or body, you are going to get the page in the results either way. It may match the word 'something' in the title field on some pages, and in the body field on others.

3. You can also use OR "|" in the selector value, like "body*=this|that". That translates to "Find all pages that have the word 'this' OR 'that' in the body field. Some of the returned pages will match 'this' and some will match 'that'. It doesn't matter what the order is either (I'm not sure how it could).

Getting back to this: "modified_users_id|created_users_id=1020" -- that translates to exactly the same thing as #2 above, as it should. Literally meaning "Find all pages that were either modified OR created by user 123". When I execute that find on my machine, here is what I get:

• page: 1, created_user_id: 1020, modified_user_id: 2
  
• page: 4459, created_user_id: 1020, modified_user_id: 41
  
• page: 4601, created_user_id: 41, modified_user_id: 1020
  
• page: 4602, created_user_id: 1020, modified_user_id: 1020
  
• page: 4615, created_user_id: 1020, modified_user_id: 41
  
• page: 6674, created_user_id: 1020, modified_user_id: 40
  
• page: 6683, created_user_id: 1020, modified_user_id: 41
  
• page: 6705, created_user_id: 1020, modified_user_id: 1020
  

This is the expected behavior. At least, it is on my machine. Before yesterday (or if you haven't replaced your PageFinder.php file with the one attached above), you would have gotten this:

• page: 1, created_user_id: 1020, modified_user_id: 2
  
• page: 4459, created_user_id: 1020, modified_user_id: 41
  
• page: 4602, created_user_id: 1020, modified_user_id: 1020
  
• page: 4615, created_user_id: 1020, modified_user_id: 41
  
• page: 6674, created_user_id: 1020, modified_user_id: 40
  
• page: 6683, created_user_id: 1020, modified_user_id: 41
  
• page: 6705, created_user_id: 1020, modified_user_id: 1020
  

Note it would only catch the 'created_user_id' part (or whichever you specified first), because fields native to the 'pages' table didn't consider ORs in the fields part of the selector.

If you are getting something other than the expected result, can you post the selector you are using and the list of matching pages like above? Here's the code I used to do it if it helps:

$results = $pages->find("created_users_id|modified_users_id=1020"); 
echo "<ul>";
foreach($results as $result) {
   echo "<li>page: {$result->id}, created_user_id: {$result->created_users_id}, modified_user_id: {$result->modified_users_id}</li>";
}
echo "</ul>";

Also just to reiterate, this will only work if you are running the latest PW and have installed the PageFinder.php file attached above.

[Soma]

Ryan thanks for the throughout explanation. That all makes sense.

Now testing again with and with your new PageFinder.php it seems to make a difference and should work. Maybe I mixed something up with the new php file yesterday...

But one thing that caught me is that when I test with your code. In the output the user id's are switched. I tracked 2 pages and edited with admin and an editor user.

• page: 1240, created_user_id: 1007, modified_user_id: 41
  
• page: 1257, created_user_id: 41, modified_user_id: 1007
  

Actually 1240 was created by admin (41) and modified by editor (1007)... ? Same with the page 1257, it's created by 1007 and modified by 41.

When I look at the info on page edit screen it shows me the correct values. I double checked everything and tried everything (removed PageFinder.php) etc but can't figure out why.

[Soma]

Ryan, I was testing yesterday with a different code. While testing with this, can you explain why the following happens:

When I use this:

$results = $pages->get("/")->find("modified_users_id|created_users_id=41");
echo "res: " . $results . "<br/>";

I get this:

res: 1002|1004|1008|1010|1112|1143|1161|1163|1170|1193|1194|1196|1240|1257

When I use:

$results = $pages->find("modified_users_id|created_users_id=41");
echo "res: " . $results . "<br/>";

I get this:

res: 1|1001|1002|1003|1004|1005|1008|1010|1112|1143|1161|1163|1165|1170|1176|1177|1188|1193|1194|1196|1197|1202|1240|1257

I think that's why I got so confused.

This does only happen when echo the result, it is not this way when using foreach... (ok forget this, I must be dreaming)

Edit: sorry changed first post code to correct.

[apeisa]

Soma, I don't spot any difference between those two.

[apeisa]

Definitely something strange going on. I get these results:

without ->get("/"):

res: 1|3|6|8|9| ... |1018|1029|1031|1032|1033|1034|1035|1036|1037|1038|1051|1053|1054|1055|1056|1059|1064|1065|1066|1067|1068

with ->get("/"):

res: 3|6|8|9| ... |1018|1029|1031|1051|1053|1054|1055|1056|1064|1065|1066|1067|1068

It seems that those pages missing with ->get("/") are all direct children for homepage... At least on my test site.

Edited May 24, 2012 by apeisa
added ... for clarity

[Soma]

Thanks for testing antti!

I was also thinking this, but my testsite has only 10 direct children in "/"...

EDIT:

*Logs out with editor and logs in with admin*

It's actually 14 including trash and admin etc, same like the amount of the result.

[ryan]

$results = $pages->get("/")->find("modified_users_id|created_users_id=41");

ProcessWire doesn't have an index for this operation because there's no reason to do a $pages->get('/')->find(); because it's the same as a $pages->find(). The person behind the code of it (me), was smart enough to avoid to unnecessary index, but not smart enough to account for it. So it's still trying to execute the find despite there being no index for home and root-level pages. That's why it's only returning pages that aren't home or rootParents, because it does have indexes for those. I've just updated it to redirect a $pages->get('/')->find to just a $pages->find, so will commit that update shortly. Thanks for finding this.

[Soma]

Thanks Ryan!

Then you have to take look at the $page->created_users_id, it returns the modified user id and modifiied_users_id the created user id... Think you missed it in the previous posts. Is this a bug?

[ryan]

Then you have to take look at the $page->created_users_id, it returns the modified user id and modifiied_users_id the created user id... Think you missed it in the previous posts. Is this a bug?

It's a bug, thanks Soma. Thankfully a simple one to fix, and just committed it. Also pushed the PageFinder updates after running through a lot more tests, so all should be in the live source now.