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

[Vigilante]

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

[Vigilante]

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?

[Vigilante]

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

[Robin S]

You might find the Connect Page Fields module useful.

[Vigilante]

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?

[dragan]

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.

[dragan]

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.

[kongondo]

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. 

[Vigilante]

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.

[Vigilante]

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