Jump to content

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


Oliver
 Share

Recommended Posts

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”
Link to comment
Share on other sites

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?

  • Like 3
  • Thanks 1
Link to comment
Share on other sites

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. ;)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 year later...

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.

  Quote
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 ?

Link to comment
Share on other sites

@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>";
}
  • Like 6
Link to comment
Share on other sites

  • 4 years later...

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!

Link to comment
Share on other sites

  • 3 years later...
  On 4/9/2012 at 11: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);
}

 
Expand  

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...