How to select pages by a specific relation between two of their field values?

[Oliver]

Currently I'm facing the problem that I need to select pages by a specific relation between two field values. Is it somehow possible to do something like

$result = $pages->find('template=something, field1>field2');

as it would be possible to do within an SQL query?

If this isn't possible, yet, I would think about ways to implement something like this e.g.

• by - in case of a string value - checking if the value matches one of the matched pages' field names
  
• or - to be sure it is meant to be treated as a field name - by introducing a notation like “field1=self.field2” similar to “children.count”
  

[Soma]

It is possible

$field2 = $page->field2;
$result = $pages->find("template=something, field1>$field2");

Edit:

ah hehe, ok this is what you're looking for.

$results = new PageArray();
foreach( $pages->find('template=something') as $p) {
if($p->field1 > $p->field2) $results->import($p);
}

Anyway have you tried your version if it would work?

[Oliver]

Yeah, thanks for this approach. That would be the solution of choice if there is no alternative way to go without causing numbers and numbers of SQL queries depending on the number of matched pages. I guess, for performance reasons it would be a pretty nice enhancement, if there was the possibility to compare fields within one selector, resulting in a corresponding SQL query after the selector has been processed. If it doesn't go against the logic of PW's selector engine.

Edit: I also should have read your code twice before answering. Of course there are no multiple queries here. But it could lead to a whole bunch of pages being loaded for nothing. I'd still be a fan of a pure selector based approach, if there was one.

[ryan]

I like the idea too, but don't think this could be accomplished with a single selector, or even with a single SQL query. At least, not one I can think of, though my mind has been away from SQL queries and such for a week, so I could be missing something. PW's DB structure is pretty non-traditional in that a template (fieldgroup) does not map to a single table of fields that can be compared against each other. So we have to know the value we are comparing against before we attempt a comparison.

[Hani]

Dredging up an old topic - but I'm facing this situation right now.  Like Oliver, I'm looking for a way to compare two fields in a selector statement to avoid looping through pages and "collecting" only the ones that have the mismatched field values.  It's agreeably inefficient when there are thousands of pages to go through.

but don't think this could be accomplished with a single selector, or even with a single SQL query

I'm definitely not a SQL guru by any means, so I had to turn to Google for this one - just in case someone had a solution.  Surely, others have faced the same SQL problem before.

Lo' and behold, I think there IS a way to do it!  Here's an example of the SQL statement that takes advantage of MySQL's CONCAT function:

SELECT  *
FROM    movie_musicals
WHERE   NOT (year_one LIKE CONCAT(`year_two`))

And here's an example fiddle I threw together:

http://sqlfiddle.com/#!2/3bed6/1/0

What are the chances of figuring out how to do this with just one PW selector statement? *crossing my fingers*.

Or even just a SQL statement to run and have page id's of pages with mismatches being returned ?

[Soma]

@hani, not sure about your query. But it is possible to write a SQL query that does compare two fields on pages.

I'm no SQL guru either but I've done this in the past but didn't post. Here a example with two joins comparing two date fields on page:

$query = "SELECT id FROM pages p
    LEFT JOIN field_mydate m1 ON p.id = m1.pages_id
    LEFT JOIN field_mydate2 m2 ON p.id = m2.pages_id
    WHERE m1.data > m2.data
    AND p.status < 2048";
    
$res = $db->query($query);
$ids = array();
while($r = $res->fetch_array()) $ids[] = $r['id']; // fetch the ids
$pa = $pages->getById($ids); // get pages 

foreach($pa as $p){
    echo "<p>$p->title - $p->mydate - $p->mydate2</p>";
}

[Hani]

That's fantastic! Exactly what I needed. Thank you, Soma!

[Torsten Baldes]

Hi,

I was looking for something similar and found this thread.

@ryan Is there meanwhile a better/other way to do this or is this still the way to go?

 

My use case is the following:

I create users and the users have to update their profiles with additional information. Now I want to find all users which didn't modify their profile.
So I wanted to build a list of all users where the created and modified date is the same.

 

Thanks!

[szabesz]

5 hours ago, Torsten Baldes said:

I want to find all users which didn't modify their profile.

Have you already considered using the Changelog module by @teppo ?

 http://modules.processwire.com/modules/process-changelog/

It might help.

[Torsten Baldes]

Thanks for the hint, but I want to automate it and send these users an email to remind them. So I have to get them via the api.

[Violet]

On 4/9/2012 at 7:20 AM, Soma said:

ah hehe, ok this is what you're looking for.

$results = new PageArray();
foreach( $pages->find('template=something') as $p) {
if($p->field1 > $p->field2) $results->import($p);
}

 

Perfect thank you @Soma ! I had the exact same problem so I came across this thread and I used your code. It worked beautifully.