Simple page selector doesn't work - leads to MySQL timeout

[Gadgetto]

Hello,

I'm just trying to run this simple pages selector but it always leads to a DB timeout:

$homePage = $pages->get('/'); // <-- this part works
$sectionPages = $homePage->and($homePage->children()); // <-- leads to MySQL timeout

// this also works (but then of course the home page is missing in the PageArray)
$sectionPages = $homePage->children();

I'd like to select the visible tree parts of this:

 

This is the full error message:

[31-May-2023 21:49:52 Europe/Vienna] PHP Fatal error:  Exception: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away (in /wwwroot/wire/core/WireDatabasePDOStatement.php line 168)

#0 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/WireDatabasePDOStatement.php(168): PDOStatement->execute(NULL)
#1 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/WireDatabasePDOStatement.php(148): ProcessWire\WireDatabasePDOStatement->executeDebug(NULL)
#2 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/WireDatabasePDO.php(936): ProcessWire\WireDatabasePDOStatement->execute()
#3 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/PagesLoader.php(1309): ProcessWire\WireDatabasePDO->execute(Object(ProcessWire\WireDatabasePDOStatement))
#4 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/Pages.php(1193): ProcessWire\PagesLoader->getById(Array, Object(ProcessWire\Template), 29)
#5 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/PagesType.php(397): ProcessWire\Pages->getById(Array, Array)
#6 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/Users.php(77): ProcessWire\PagesType->get(41)
#7 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/Session.php(210): ProcessWire\Users->get(41)
#8 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/ProcessWire.php(601): ProcessWire\Session->__construct(Object(ProcessWire\ProcessWire))
#9 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/wire/core/ProcessWire.php(315): ProcessWire\ProcessWire->load(Object(ProcessWire\Config))
#10 /Users/mgartner/ProjekteWEB/bitego_2023/wwwroot/index.php(52): ProcessWire\ProcessWire->__construct(Object(ProcessWire\Config))
#11 {main} in /wwwroot/index.php on line 64

I'm using:

• ProcessWire 3.0.218 dev
• PHP 8.1.13
• MySQL 5.7.39
• the Database format is InnoDB

What could be wrong here??

Greetings,
Martin

[BrendonKoz]

I don't see anything wrong with the logic used, so there might be some underlying issue with how you're using it in this context. If you definitely want a full WireArray class object to hold this particular structure, would this work for your use-case instead?

<?php 

$homePage = $pages->get('/');
$sectionPages = $homePage->children();
$sectionPages->prepend($homePage);

This takes advantage of the WireArray method "prepend" instead of the WireData method of "and". If you're looking to generate a breadcrumb (edit: $page->parents for breadcrumbs) or navigation menu, or sitemap, the example code from the sitemap.php template in one of the default profiles might come in handy (included below in its entirety):

<?php

/**
 * Site map template
 *
 */

include("./head.inc"); 

function sitemapListPage($page) {

	echo "<li><a href='{$page->url}'>{$page->title}</a> ";	

	if($page->numChildren) {
		echo "<ul>";
		foreach($page->children as $child) sitemapListPage($child); 
		echo "</ul>";
	}

	echo "</li>";
}

echo "<ul class='sitemap'>";
sitemapListPage($pages->get("/")); 
echo "</ul>";

include("./foot.inc"); 

[Gadgetto]

@BrendonKozthanks for your hint! Prepending the page even seems to be a bit more performant.

Regarding the MySQL timeout - it was another problem which I could fix in the meantime.

[Gadgetto]

11 hours ago, BrendonKoz said:

If you definitely want a full WireArray class object to hold this particular structure, would this work for your use-case instead?

Is there another way to render a menu (based on the pages tree) without fetching an array of pages?

[BrendonKoz]

If you know you're going to loop through a structure anyway, even if it might look a little bit cleaner by storing things in a variable first, the descriptive nature of ProcessWire's methods (API) usually provide more than enough context-via-code to not really warrant setting up variables first. I just checked my own navigation structure and although I (ironically, because I had forgotten) created nearly the exact same code as above (get homepage, then get children of homepage, then prepend homepage to children variable), I didn't use it. Homepage is always page ID #1, or (nearly always) "/". We can test numChildren, and if so loop through the children, then as we get to the children we can also test each of their numChildren and if > 0 then loop through their children, and so on.

ProcessWire's structure essentially already is hierarchical (array-like). Trying to recreate it in variables might end up duplicating the order of magnitude by 2 (loop via the API behind the scenes to create an array for a variable, then loop through the variable --- or just use the API to loop right in our code).

Example:

<?php
	// We assign $homepage to a variable since we refer to it more than once
	$homepage = $pages->get('/');
	if ($homepage->numChildren(true)) {
      # code goes here to create link structure
      
      foreach ($homepage->children() as $main_link) {
        # code goes here to create link structure
        
        if ($main_link->numChildren(true)) {
          # code goes here to create link structure
          
          foreach ($main_link->children() as $secondary_link) {
            // If we go deeper than this, a recursive function
            // is likely better, so this example will stop here
            # code goes here to create link structure
          }
        }
      }
    }

I used foreach here, but ProcessWire also has an each() method that you could substitute in, if you'd prefer.

NOTE: I don't remember why I tested if there were numChildren first; I might get an error in PHP v8+ without it, I can't remember. I think the ProcessWire each() option would circumvent the need for that check, unless you need to do something in an else block.