Add a helper method for building selectors that match an exact Page Reference field value

[Robin S]

Update: you don't need this method. See my next post below. ?

-----

Suppose you have a Page Reference field "countries" in template "traveller" that contains any countries the traveller has visited.

It's easy to find travellers who have visited Albania and Andorra...

$matches = $pages->find("template=traveller, countries=Albania, countries=Andorra");

But what if you want to find travellers who have only visited Albania and Andorra and not visited any other countries? Then it's not so easy. There's no simple syntax for selectors that allows you to match an exact Page Reference field value, as @adrian highlighted recently. Within your selector you have to include all the countries that you don't want to be in the field value. That's a hassle to do manually, and in some circumstances where new pages are being added all the time you may not know in advance all the pages you need to exclude.

So to make it an easier job to create an exact match selector for Page Reference fields, here is a helper method you can add in /site/ready.php:

// Returns a selector string for matching pages that have the exact supplied value in the Page Reference field
$wire->addHookMethod('Field(type=FieldtypePage)::getExactSelector', function(HookEvent $event) {
	$field = $event->object;
	$value = $event->arguments(0);
	if($value instanceof PageArray) $value = $value->explode('id');
	if(!is_array($value)) throw new WireException('The $value argument supplied to getExactSelector() must be a PageArray or an array of page IDs.');
	$table = $field->getTable();
	$query = $this->database->query("SELECT data FROM $table GROUP BY data");
	$field_values = $query->fetchAll(\PDO::FETCH_COLUMN);
	$exclude_ids = array_diff($field_values, $value);
	$selector = '';
	foreach($value as $id) $selector .= "$field->name=$id, ";
	if(count($exclude_ids)) $selector .= $field->name . '!=' . implode('|', $exclude_ids);
	$event->return = rtrim($selector, ', ');
});

And you use the method like this:

// Get the Page Reference field you want to use in the selector
$field = $fields->get('countries');
// Get the value you want to match (PageArray)
$value = $pages->find('template=country, title=Albania|Andorra');
// Alternatively $value can be an array of page IDs
// $value = [1105, 1107];
// Use the method to get a selector string
$selector = $field->getExactSelector($value);
// Optional: add anything else to the selector that you want
$selector .= ', template=traveller';
// Find the matching pages
$matches = $pages->find($selector);

 

[adrian]

Sensational, Brilliant, Fabulous ?

Now this is something we definitely need in the core - I can't believe someone hasn't realized that we can't already do this.

I think we need to nominate Robin as PW support guru of the year!

[Robin S]

1 hour ago, adrian said:

Sensational, Brilliant, Fabulous

Ha ha, you might have spoken too soon. ?
I knew that as soon as I posted this a much simpler solution would present itself. You don't need to exclude anything to make an exact match - you just need to match all the pages and the count of the pages.

So no helper method is needed really.

$matches = $pages->find("template=traveller, countries=Albania, countries=Andorra, countries.count=2");

Or for a more complex match where the count isn't immediately obvious:

$value = $pages->find('template=country, title=Albania|Andorra'); // imagine a more complex value than this
$selector = $value->each('countries={id}, ');
$selector .= "countries.count=$value->count, template=traveller";
$matches = $pages->find($selector);

 

[adrian]

Nice!

Interesting you figured out using count for an exact match. In the same multi-parameter selector mentioned in the linked post, I use count to find messages with either all the age categories checked, or alternatively, it has at least the user's age category checked. This is used because the user's age is not a mandatory field to be completed so if we don't know their age, we need the message to be for all ages, but if we do know their age, then it must match the ages tagged in the message.

$selector[] = 'ages=(ages.count='.$pages->count('template=age'), ages=(ages='.$user->age.')';

Anyway, obviously getting away from the purpose of the thread, but it's another example of how using a count can be helpful for queries.