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.

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

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

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Similar Content

    • By Mika
      Hello, can I use the selector to extract only pages with page titles longer than 10 characters?
      I'm looking for a way to search directly without using the php foreach statement, as shown below.
              $result = $this->pages->find("template!=admin, has_parent!=2, include=all");
              foreach ($result as $page) {
                  if (strlen($page->title) > 10) {
                      $this->table[] = $page;
                  }
              }
       
    • By Guy Incognito
      I'm trying to build a search selector that includes a checkbox multiple option field (clinicdetails_specialisms). The whole selector works fine if I don't include the checkbox field. With it included I get errors like "Error: Exception: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters (in wire/core/PageFinder.php line 627)".
      This is the selector as it currently stands.
      subscription_status=active,(template=user,clinicdetails_description|clinicdetails_short_description|clinicdetails_clinic_name|clinicdetails_first_name|clinicdetails_last_name*=$query),(clinicdetails_specialisms.title=$query) I've also tried setting the specialisms field in one string rather than groups and have also tried with/without the '.title' property.
      Oddly the selector works if specialisms is the only field being searched. It seems to fail when mixed with other selectors.
      Can anyone advise what stupid mistake I'm making!
    • By Ksenia
      Hello! 
      I have a weird situation going on with my selector field, which I can't really get. 
      I create an array of titles as filter using this logic:
      firstname=Mike|Steve id=123|124|125 title*=Red|Blue|Green This is the field I am logging: 

      It goes in a for loop till it looks like what you see on the screen.
      So, my code regarding the selector string looks like this:
      $output = ""; foreach ($allorganisations as $item){ $output .= "$item | "; echo "<div style='color:red;'>//output String://</div>"; echo $output; } $selector_org .= ", title=$output"; I also log the output just after the matched pages are selected to be sure:
      $matches = $pages->find($selector_org); echo $output; foreach ($matches as $match) { echo " <li><a href='$match->url'>$match->title</a></li>"; } But you can see that it doesn't select all four needed pages, only selects one of them.
      Yet when I copy this exact selector text and manually put it in my selector, it works great... I even copy extra space and "|" and all that, it is supposed to be identical. I am very confused, what is the difference between the code? 
      foreach ($allorganisations as $item){ $output .= "Institute for Scientific Research in Cosmic Anthropoecology | Institute for Scientific Research in Cosmic Anthropoecology | Stanford Research Institute | Institute for Scientific Research in Cosmic Anthropoecology | Stanford Research Institute | Institute of Clinical and Experimental Medicine | Institute for Scientific Research in Cosmic Anthropoecology | Stanford Research Institute | Institute of Clinical and Experimental Medicine | Cosmists | "; echo "<div style='color:red;'>//output String://</div>"; echo $output; } $selector_org .= ", title=$output"; } ------>
      If you see where I'm going wrong, please enlighten me. I am very puzzled with this behaviour . :--)
      Best,
      Ksenia
    • By prestoav
      Hi all,
      I could have sworn I used to be able to use the site generic 'title' field as a sub field of a repeater field. However I've tried to do this on two 3.0.165 sites recently and, while it will add the title field in the repeater field setup, it wont save the repeater title sub field's content when the repeater is used in a page template and edited.
      It;'s not a big issue but I wondered if this was a known restriction?
    • By spercy16
      I was previously using individual fields to capture and output multiple cards onto my web page but after recently learning about repeaters would prefer to use this field type, as it will be a lot more user friendly for end users to create new cards. I tried to modify my code to grab the fields from the new Repeater field(s) but am getting this error message on the page:
      Here is the code I'm currently trying to use for the cards:
      <?php // https://processwire.com/api/arrays/ // check if the array of images has items foreach($page->tall_card as $card) { $count = 0; $count++; $options = array( 'quality' => 70 ); $image = $card->image; $img = $card->image->size(550, 400, $options); $cardHeading = $card->title; $cardBody = $card->plain_paragraph; $raised = $card->raised_amount; $goal = $card->goal_amount; $link = $card->link; if ($raised == 0 ) : $percent = 0; else: $percent = $raised_amount / $goal_amount * 100; endif; if ($percent <= 5) : $percent = 0; endif; ?> <span id="card<?php echo $count?>" class="card"> <img class="cardThumb" src="<?php echo $img->url; ?>" alt="<?php echo $image->description; ?> Thumbnail" /> <div class="cardBody"> <div class="cardText"> <h2><?php echo $cardHeading; ?></h2> <?php echo $cardBody; ?> </div> <div class="progressBarSection"> <progress class="progressBar" id="progressbar<?php echo $count; ?>" value="<?php echo $percent; ?>" max="100"></progress> <span class="raisedAmount"> $<?php echo $raised; ?> usd </span> <span class="goalAmount"> $<?php echo $goal; ?> usd </span> </div> <div class="primaryBtn"> <a href="https://www.paypal.com/donate?hosted_button_id= <?php echo $link; ?> &source=url"> <button> <i class="fas fa-donate"></i> Donate </button> </a> </div> </div> </span> <?php } ?> Thanks in advance for any help!
×
×
  • Create New...