Jump to content
Vigilante

Find pages where the page has not been referenced from another parent's pagefield

Recommended Posts

I'm wondering about creating a query that involves looking across two different parent page trees.

One parent is for blog posts and it has each post under that.

Another parent is used for categories and subcategories for the blog.

Then in the blogs there is a page field that can reference the categories. This is all pretty normal setup so far.

I created a navigation that lists the current category and its children, but I want to not show categories that have no posts. For some reason I'm drawing a blank.

So let's say a user clicks category1, and it shows a blog listing for that category. I query for all the child categories of the current one. Now I want to exclude any categories where NO blogs exist that make reference to the category (an unused category). How do I do this in a way that is performant? I don't want to have to query the blogs tree over and over to test for the presence of each category in the list!

Here is the tree:

blogs
 - article1  [pagefieldcat = "cat1"]
 - article2 [pagefieldcat = ""]
categories
- cat1
- cat2

Now when I get all children of categories, I want to exclude cat2 since it's not referenced from any blogs. Can I do this without having to loop and query every category against every blog one by one?

Thanks

Share this post


Link to post
Share on other sites

I figured out how to do it via looping but like I said, I don't want to have to run this many queries, here is the working code:

 

foreach ($catlist as $cat) {
	if (!$pages->count("template=blog, categories=$cat")) { continue; }
	// Some HTML output
}

 

The problem here is that I run $pages->count over and over again. I don't know if that's a performance hit, it might loop anywhere from 5 to 30 times.

My question is, in the selector "categories=$cat", can I do this selector on the entire pagearray? Like "categories=$catlist"?? Will PW do its own internal looping to see if any page in the pagearray is in categories field?

Share this post


Link to post
Share on other sites

I guess my previous question really doesn't matter though. I need to test each individual category to see if it's contained in the field, not just check my entire category pagearray to see if "something" is in there. That doesn't actually help!

So my original question still stands. Can test each category one by one in a more performant way, perhaps in the original query for the categories?

$catlist = $pages->find("template=category, parent=$page->id");

 

Simple query, gets all the category children of currently open category. But can I exclude any that are NOT contained in a page reference field in 'template=blog'?

Share this post


Link to post
Share on other sites

Is that really the way to solve this? 

In normal SQL this feels like it would just be a simple JOIN query. 

SELECT * FROM categories
JOIN blogs ON [???blogs.categories = categories.title???]

 
Is there no way to do a JOIN-like query in PW? With the strict separation of pages in the page tree, I would think this a common need.

I'm only dealing with 20 or 30 categories but I can imagine needing to join up two different parents where there might be thousands and thousands, and that would be a bear to iterate over just to filter it down! Even the connect page fields plugin wouldn't help if you want to cross-related dozens or even hundreds of entries.

Maybe I'm just missing something.

Could I assemble a MySQL query directly to solve it?

Share this post


Link to post
Share on other sites
11 hours ago, Vigilante said:

but I want to not show categories that have no posts

In other words: You have pre-defined categories?

Can't you set it up so that categories are only created, when there actually is a matching post? i.e. use page reference field and check "Allow new pages to be created from field?" in the field settings (tab "input"). That way you never end up with empty/unused categories.

Share this post


Link to post
Share on other sites

You could have a look at pageArray filter: https://processwire.com/apigen/source-class-PageArray.html#329

Also, something you might try: Query all blog posts, get all the categories, then use something like array_unique() to get all used categories. From that array, you can create a "safe" list-link, i.e. list that doesn't show categories without blog posts.

Share this post


Link to post
Share on other sites
12 hours ago, Vigilante said:

Now I want to exclude any categories where NO blogs exist that make reference to the category (an unused category).

Is there a particular reason for this? Couldn't you just show a message such as 'no posts found in this category'?

10 hours ago, Vigilante said:

$catlist = $pages->find("template=category, parent=$page->id");

If the parent of this categories is the current page, why not just use $page->children rather than parent=$page->id?

10 hours ago, Vigilante said:

Simple query, gets all the category children of currently open category. But can I exclude any that are NOT contained in a page reference field in 'template=blog'?

I see no way of doing this using one selector. 

10 hours ago, Vigilante said:

The problem here is that I run $pages->count over and over again. I don't know if that's a performance hit, it might loop anywhere from 5 to 30 times.

$pages->count() is quite efficient actually. I'm no expert but I don't think 5 - 30 will hurt.

5 hours ago, Vigilante said:

Could I assemble a MySQL query directly to solve it?

In some cases (not necessarily the present case) this is the way to go. Have a look at $database. 

  • Like 1

Share this post


Link to post
Share on other sites
On 11/5/2017 at 3:12 AM, dragan said:

In other words: You have pre-defined categories?

Can't you set it up so that categories are only created, when there actually is a matching post? i.e. use page reference field and check "Allow new pages to be created from field?" in the field settings (tab "input"). That way you never end up with empty/unused categories.

That's an idea, but in this case the client sent me an exact list of categories they want. I presume eventually in time all the categories will have something in them. But for now they do want this functionality to ignore the empty ones.

Share this post


Link to post
Share on other sites
On 11/5/2017 at 4:37 AM, kongondo said:

Is there a particular reason for this? Couldn't you just show a message such as 'no posts found in this category'?

Not in this case. They don't want the category to appear in the sidebar at all if it's empty. This way it avoids the frustration of user clicking a category first just to be told it's empty.

 

On 11/5/2017 at 4:37 AM, kongondo said:

If the parent of this categories is the current page, why not just use $page->children rather than parent=$page->id?

Rookie mistake I guess, I've seen this way used in examples. Is $page->children more performant than the $pages->find query? 

I think I did the query this way because once a person clicks on a category, the sidebar will change to show the subcats of that one only. Same diff I guess.

On 11/5/2017 at 4:37 AM, kongondo said:

I see no way of doing this using one selector. 

 

On 11/5/2017 at 4:37 AM, kongondo said:

$pages->count() is quite efficient actually. I'm no expert but I don't think 5 - 30 will hurt.

I'm wondering that myself. I see no noticeable lag but I do cringe at the idea of having to lookup every single page in a loop, it's not a lot of queries now but I really wouldn't want to do it if it were hundreds, or thousands of iterations.

 

I'll keep $database and the connect page fields options in my pocket for later.

Thanks

Share this post


Link to post
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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By snck
      Hi there,
      I have a problem constructing a selector that finds all pages that refer to pages with a specific template.
      I have pages using an event template and I want to show events based on a specific context. In this example I want to filter the results and only show event pages that relate to a specific template (exhibitions) in their page field related_pages.
      What I tried:
      $events = $pages->find("template=event, related_pages.template.name=exhibition"); Unfortunately it does not work (0 results).
      Same with this:
      $events = $pages->find("template=event, related_pages=[template.name=exhibition]"); At the moment I am helping myself with the following lines, but I have a strong feeling that there is a more efficient solution:
      $events = $pages->find("template=event"); foreach($events as $event){ if(!count($event->related_pages->find("template=exhibition"))){ $events->remove($event); } }  
      I really hope that one of you can help me out.
      Thanks in advance!
      Flo
    • By schwarzdesign
      ProcessWire added the numReferences property in 3.0.107, which returns the count of all pages having a reference to the page object. However, apparently it's not possible to use this property inside a selector. I'm trying to find all pages that are referenced at least once:
      $pages->find('template=service, numReferences>0'); This throws an error: "Field does not exist: numReferences".
      Is there another way to filter by the number of references? Of course, I could manually filter the results of the find query, but that feels overly complicated. Ideally, I would also like to filter the number of references from a specific field; that is, find all pages that are referenced at least once in one specific page reference field.
      Is there a way to do this? I guess supporting numReferences in selectors would be a feature request - if so, is it feasible?
      Thanks!
       
    • By pwFoo
      I played with the Selectors object and would like to use it for a special use case...
      That is a custom Selectors object with dummy data converted to an php array to see the structure (Selectors object is a WireArray with "fields" added)
      Array ( [0] => Array ( [0] => Array ( [field] => seg1 [value] => val1 [not] => [group] => [quote] => [forceMatch] => ) [1] => Array ( [field] => seg2 [value] => val2 [not] => [group] => [quote] => [forceMatch] => ) ) )  
      But instead of "seg1" the field name is "field" with value "seg1" (= my field name).
       
      So I can't search the Selectors WireArray (= custom WireArray with added Selectors objects) with PW "find('seg1=val1')", Is there a way to search with "find()" or build a simple wrapper to make the elements searchable / filterable with find()?
    • By celfred
      Hello,
      I'm struggling with this : a 'group' field of 'Page' type.
      The parent of selectable pages is '/groups', the template is 'group', and a member can create some groups in the /groups tree. I would like this user to see only the groups he or she has created so in my 'Find selector' in the backend, I would like to use :

      template=group, created_users_id=$user->id But that doesn't seem to work... I still get the list of all available groups in the /groups tree.
      Any idea ?
      I had a feeling I had already seen that before (something like $user->id must be replaced by users_id or something, but I can't find anything in the Forums... and all my tests keep failing...
×
×
  • Create New...