Get pages used by a pagefield

[LostKobrakai]

In the last few weeks I've notices some request (e.g. here and here) to be able to get pages based on if they are selected in page fields of other pages. I think adding a method for this would be a nice addition to ProcessWire, as it's often the case that the pages itself are options we just want to get, if they are used somewhere. 

Currently the task "Get all tags used by some blogposts" has to be done manually like this:

$tags = $pages->find("template=tags");

foreach($tags as $tag){
  // Filter unavailable
  if(! $pages->count("template=posts, tags=$tag") ) continue;

  // Do stuff with it
}

Now it would be nice to have something like this, where we don't need to have a selector for tags (this is done by the pagefield already).

// Find all pages, which are selected in the "tags" field of the selected posts
$available_tags = $pages->findSelectedPages("template=posts", "tags");

I'm not that big a MySQL guy, but I can imagine this not only improving readability, but also reducing database calls.

[ryan]

Sounds interesting. Here's one way you can do it:

$table = $fields->get('your_page_field')->getTable();
$query = $database->query("SELECT data FROM $table GROUP BY data");
$ids = $query->fetchAll(PDO::FETCH_COLUMN);
$items = $pages->getById($ids); 

// $items is a PageArray
echo $items->implode("\n", "<a href='{url}'>{title}</a>");

If we were to add this to the API, I think I'd want to make it accessible from a regular find() selector, rather than as a separate findSelectedPages method. That way it could be used with things like InputfieldSelector. Perhaps something like this:

$pages->find("your_page_field=:selected");

...where ":selected" is a keyword is would recognize to execute this behavior. 

[LostKobrakai]

Just noticed your answer by accident, don't know why I didn't see it earlier. I like your idea, but I don't know if this wouldn't be confusing. You're not actually selecting posts, but rather tags, which are used in posts.

$pages->find("template=posts, tags=:selected");

That's why I thought a new function could be more appropriate for this, but maybe there can be descriptive enough syntax found to be used by Pages::find. 

Edit: Looking at it a bit a tad longer it doesn't look as strange anymore. But thinking about the usage, would it be possible to also filter the results of such a query, like getting only tags which are categorized as "tech"? I imagine it somewhat like the sub-selectors.

[thomas]

I think something like "find in" would be great

$pages->find("PageField in Selector");

This would return a PageArray of the pages which are selected in the Pages found by the selector. 

[mr-fan]

Wanna say  Thank you Ryan for this great piece of code!

Is something like this in the actual core now? I've read the changelog but don't see seomthing like this...or overread it...

Best regards mr-fan

Edited December 29, 2015 by mr-fan

[LostKobrakai]

How about such a syntax? I think this would be flexible enough for any kind of tagging structure.

// Get me the tags,
// which start with "Tech_",
// are used in the tags field of "templates=posts, date>today"
// and sort them by number of occurences (maybe named count)
$pages->find("template=tag, title^=Tech_, in.tags=[templates=posts, date>today], sort=occurences");

// Not limiting to upcoming posts or even posts
$pages->find("template=tag, title^=Tech_, in.tags=[*], sort=occurences");

[thetuningspoon]

I enthusiastically approve of this feature request 

[hettiger]

Just in case someone's looking for an example to get all tags that are referenced by pages of specific template types, ordered by count, limited to 10:

if ( ! $config->ajax) throw new Wire404Exception();

$input->whitelist('query', $sanitizer->selectorValue(urldecode($input->get('query'))));
$query = $input->whitelist('query');

$typeTemplateIds = join(', ', $templates->find('name=templateNameA|templateNameB')->getKeys());
$proposalTable = $fields->get('tagsPageReferenceFieldName')->getTable();
$titleTable = $fields->get('title')->getTable();

$sql = <<<SQL
SELECT proposals.name, {$titleTable}.data as title FROM pages
INNER JOIN {$proposalTable} ON pages.id = {$proposalTable}.pages_id
INNER JOIN pages as proposals ON {$proposalTable}.data = proposals.id
INNER JOIN {$titleTable} ON proposals.id = {$titleTable}.pages_id
WHERE pages.templates_id IN ({$typeTemplateIds}) AND {$titleTable}.data LIKE :query
GROUP BY {$proposalTable}.data
ORDER BY count({$proposalTable}.data) DESC
LIMIT 0, 10;
SQL;

$statement = $database->prepare($sql);
$statement->bindValue(':query', '%' . $query . '%', PDO::PARAM_STR);
$statement->execute();
$proposals = $statement->fetchAll(PDO::FETCH_ASSOC);
  
header('Content-Type: application/json; charset=utf-8');
echo json_encode($proposals);

Enjoy 

[thetuningspoon]

This can now be done with owner selectors (http://processwire.com/blog/posts/processwire-3.0.95-core-updates/)

 

$tagsThatHaveBeenUsedOnPosts = $pages->find('template=tag, tags.owner.template=post');

 

Where tags is the name of the field on the post template that holds the tag pages.

 

Also, for any given tag page you can check how many post pages reference it with $page->references('template=post')->count();

https://processwire.com/blog/posts/processwire-3.0.107-core-updates/#what-pages-point-to-this-one