Need help optimising list of page filters based on page field options (too many queries)

[Marc]

This one is tough to explain in one line so please bear with me. I'm building a list of about 700 (paginated) products. You can filter the list based on the fields of the products' template (they all have the same template). There's a field for color, material, theme, etc. These are page fields, so their options are stored as pages. So page 'color' has child pages blue, red, white, etc. The filter gets all the fields from the template and adds them to a nice PW form automatically, so I don't have to list anything manually. This is the part I have working.

What I am adding is a way to exclude any option that is not currently used by any product. So if no product has the color red in its color field, I want to remove that option. I have managed to get this working also, but the way I'm doing seems wrong to me, as I'm doing a query for every option of every field, so if I have 10 fields with 5 options each, that is 50 queries and it is adding about 150 ms to my page load. So my question is, is there a more efficient way to check which page options are not in use by my product pages? Are there other obvious mistakes I'm making? Here's what I've got so far, it's part of a function so I'm wiring some things in:

$template = wire('templates')->get($page->child()->template);
$fields = $template->fieldgroup;
foreach($fields as $field) {
    $inputfield = $field->getInputfield($p);
    if (in_array($inputfield->name, $ignorefields)) continue;
    if (substr($inputfield->name, 0, 5) !== "meta_") continue; // Only list those whose name starts with "meta_".
    $inputfield->description = ""; // Remove descriptions.

    // Now check if this field is stored in our $inputs array. If it is, check the type of this field and act accordingly.
    if (array_key_exists($field->name, $inputs)) {
        if ($field->type == "FieldtypeCheckbox") {
            if ($inputs[$field->name] == 1) {
                $inputfield->attr('checked', 'checked');
            }
        }
        else if ($field->type == "FieldtypePage") {
            $values = $inputs[$field->name];
            $inputfield->attr('value', $values);
        }
    }

/** THIS IS WHERE THINGS COULD HOPEFULLY BE IMPROVED **/
    if ($field->type == "FieldtypePage") {
        $i = 0;
        foreach (wire('pages')->get($field->parent_id)->children() as $option) { // Parent_id is the parent page whose child pages are listed as options for this field.
            $frequency = wire('pages')->count("{$field->name}={$option->id},template={$page->child()->template}"); // Count pages using the field and current option, and of course the template of the correct product (which is any child of the current page).
            $i = $i+$frequency;
            if ($frequency == 0) $inputfield->getInputfield()->removeOption($option->id); // Remove the option from the field.
        }
        if ($i == 0) continue; // No pages are using any of the option of this field, so don't add it to our form.
    }

    $form->add($inputfield);
}

[BitPoet]

I can think of two possibilites to approach this:

1. Reverse your logic and add the products in a page field to the option page (not really intuitive)
2. Add a counter field to the option pages' template(s) and update the value through a hook on Pages::save for your product template (with a similar looping logic as in your code above). Then you can easily filter with $optionpages->children("usecount>0").

[Webrocker]

hi,

outsourcing the usage-info into a queryable field like bitpoet describes seems a very good idea.

I think you can limit your fields query like this

$fields = $template->fieldgroup->find('name^=_meta');

and save the substr check later on, maybe this also improves performance a bit by reducing the loop iterations. not 100% sure about that, maybe it needs to be

$metaFields = $fields->find('name^=_meta');

and the looping over $metaFields, not $fields

edit: ah, scrap that, I just see that you're using inputField($p) <-- (where does $p come from?)

nonetheless, if you only want the fields whose name starts with '_meta', the above could still work, and the make a check if the current field is not in ignoreFields and then use inputField($p)…?

cheers,

Tom

[Marc]

$fields = $template->fieldgroup->find('name^=_meta');

and save the substr check later on, maybe this also improves performance a bit by reducing the loop iterations. not 100% sure about that, maybe it needs to be

That's a nice little tip, works just as you described. Looks like it shaved off a few milliseconds too 

edit: ah, scrap that, I just see that you're using inputField($p) <-- (where does $p come from?)

$p = new Page(). I do this because I want blanc input fields, not pre-filled inputs from my product pages.

I can think of two possibilites to approach this:

1. Reverse your logic and add the products in a page field to the option page (not really intuitive)
2. Add a counter field to the option pages' template(s) and update the value through a hook on Pages::save for your product template (with a similar looping logic as in your code above). Then you can easily filter with $optionpages->children("usecount>0").

Suggestion nr 1 would be highly unpractical for the person entering new products, but yeah I see how it would speed things up. Your second suggestion sounds like good idea, I will look into it!

[LostKobrakai]

You might take a look at this:

https://processwire.com/talk/topic/9730-get-pages-used-by-a-pagefield/

[Marc]

You might take a look at this:

https://processwire.com/talk/topic/9730-get-pages-used-by-a-pagefield/

I don't think I understand, is that supposed to give you a pageArray with all the pages using the options of a given field?

$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>");

I tried to apply it to my situation but I get an array with the options of my fields. Not sure how to properly implement this:

if ($field->type == "FieldtypePage") {
    $table = $field->getTable();
    $query = wire('database')->query("SELECT data FROM $table GROUP BY data");
    $ids = $query->fetchAll(PDO::FETCH_COLUMN);
    $items = wire('pages')->getById($ids);
    echo $items->implode("\n", "<a href='{url}'>{title}</a>");
    
    $i = 0;
    foreach (wire('pages')->get($field->parent_id)->children() as $option) { // Parent_id is the parent page whose child pages are listed as options for this field.
        $frequency = $items->count("{$field->name}={$option->id},template=$template"); // Count pages using the field and current option, and of course the template of the correct product (which is any child of the current page).
        echo "$frequency<br>";
        $i = $i+$frequency;
        if ($frequency == 0) $inputfield->getInputfield()->removeOption($option->id); // Remove the option from the field.
    }
    if ($i == 0) continue; // No pages are using any of the option of this field, so don't add it to our form.
}

[LostKobrakai]

You've a page field for each of your categories ($field in your code). The snippet of ryan then retrieves the database table of that field and does fetch all the saved id's in that table. Meaning all the pages, which are selected in that field on any page with that field. I'm not sure if you're using these page fields in multiple templates. If so you'd need to extend the query to limit the retrieved pages to only those selected on pages of a specific template.

To get the number of uses per option you'd add the following to the query: "SELECT data, COUNT(*) FROM $table GROUP BY data;" This will return a table of id's and their number of occurrences. 

All in all this would limit your queries to one per page-field (per template; if multiple).

[Marc]

You've a page field for each of your categories ($field in your code). The snippet of ryan then retrieves the database table of that field and does fetch all the saved id's in that table. Meaning all the pages, which are selected in that field on any page with that field. I'm not sure if you're using these page fields in multiple templates. If so you'd need to extend the query to limit the retrieved pages to only those selected on pages of a specific template.

To get the number of uses per option you'd add the following to the query: "SELECT data, COUNT(*) FROM $table GROUP BY data;" This will return a table of id's and their number of occurrences. 

All in all this would limit your queries to one per page-field (per template; if multiple).

I had a go and this is what I came up with (I have to take the template into consideration like you said):

$table = wire('fields')->get($field->id)->getTable();
$query = wire('database')->query("SELECT
    tbl.pages_id,
    tbl.data, p.id,
    p.templates_id,
    t.id
    FROM $table AS tbl
    INNER JOIN pages AS p
    ON tbl.pages_id = p.id
    INNER JOIN templates AS t
    ON p.templates_id = t.id
    WHERE t.id = '{$template->id}'
    GROUP BY data"
);
$ids = $query->fetchAll(PDO::FETCH_COLUMN);
$items = wire('pages')->getById($ids);

foreach (wire('pages')->get($field->parent_id)->children() as $option) { // Parent_id is the parent page whose child pages are listed as options for this field.
    $frequency = $items->find("{$field->name}={$option->id}")->count(); // Count pages using the field and current option, and of course the template of the correct product (which is any child of the current page).

    if ($frequency == 0) $inputfield->getInputfield()->removeOption($option->id); // Remove the option from the field.
}

This saves about 20 milliseconds (of 150), so it's not as efficient as I'd hoped. Unless I'm doing it wrong?

[LostKobrakai]

You're still doing all these count()'s. The whole foreach loop isn't necessary if you're also including that COUNT(*) part I wrote above in the mysql query. This will already give you the number of times each option is selected. 

[Marc]

Oh I see, yeah I forgot about that. But how do I use that counter in the context of the $items array? In other words how do I check the counter after the query is done?

[LostKobrakai]

Use PDO::FETCH_NUM and you'll get an multidimensional array of all your selected fields, including the id and the count.

[Marc]

Thanks for the hints (especially FETCH_NUM), I got it working without the extra queries. I will do extensive testing tomorrow and report back 

[Marc]

Revising the problematic part of the code I posted in the OP, here's the new and improved part:

if ($field->type == "FieldtypePage") {

    $table = wire('fields')->get($field->id)->getTable();
    $query = wire('database')->query("SELECT
        tbl.pages_id,
        tbl.data,
        COUNT(*)
        FROM $table AS tbl
        INNER JOIN pages AS p
        ON tbl.pages_id = p.id
        INNER JOIN templates AS t
        ON p.templates_id = t.id
        WHERE t.id = '{$template->id}'
        GROUP BY data"
    );
    $ids = $query->fetchAll(PDO::FETCH_NUM); // Multidimensional array (2 levels) [0] => array(2047, 4419, 12). The second level corresponds to the selected columns in the query.

    // Add options that are in use by our products to a whitelist.
    foreach ($ids as $item) {
        $whitelist[]= $item[1];
    }

    $options = $inputfield->getInputfield()->getOptions(); // Returns simple assoc array [4777] => dieren, [4778] => tweeling, etc.

    // Remove unsued options from the field.
    foreach ($options as $pageID => $pageTitle) {
        if ( !in_array($pageID, $whitelist) ) {
            $inputfield->getInputfield()->removeOption($pageID);
        }
    }

    if (!count($ids)) continue; // No pages are using any of the option of this field, so don't add it to our form.
}

This shaves off about 100 ms of of 150 ms, so it's pretty darn fast compared to the way I did it before. I suppose maintaining a counter as suggested by BitPoet may be even faster, I'll have to compare it someday. Thanks everyone!