ProcessWire - SQL nutzen

Siehe auch:

ProcessWire - Datenbank manuell abfragen

Manchmal kann es sinnvoll sein direkte SQL Abfragen auf die Datenbank zu machen. Vor allem lassen sich so manchmal unnötige Schleifen mit denen man viele Anfragen erzeugt mit einer Abfrage ersetzen (z.B. ein schöner JOIN )

Beispiel von Ryan: Wie oft wurden Optionen eines Option Select Feldes ausgewählt.

$field = $fields->get('my_options_field');
$table = $field->getTable();
$query = $database->query("SELECT data FROM $table");
$ids = $query->fetchAll(PDO::FETCH_COLUMN);
$count_values = array_count_values($ids); // count the frequency of values in the array
arsort($count_values); // sort highest to lowest
// use the option IDs and counts as needed, for example:
$all_options = $field->type->getOptions($field);
echo '<ul>';
foreach($count_values as $option_id => $count) {
    $option = $all_options->get($option_id);
    echo "<li>{$option->title} ({$count})</li>";
}
echo '</ul>';

Wie oben aber - Non Public Pages herausfiltern

$field = $fields->get('my_options_field');
$table = $field->getTable();
$query = $database->query("SELECT data, pages_id FROM $table");
$results = $query->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP); // get multidimensional array of results
$results_page_ids = array_unique(call_user_func_array('array_merge', $results)); // get all the unique page IDs in the results
$public_ids = $pages->find(['id' => $results_page_ids])->explode('id'); // find out which ones are public
$filtered_results = [];
foreach($results as $option_id => $page_ids) {
    // remove any page IDs that are not public
    $filtered_value = array_filter($page_ids, function($value) use ($public_ids) {
        return in_array($value, $public_ids);
    });
    // for each option, count the public pages it is selected on
    if($filtered_value) $filtered_results[$option_id] = count($filtered_value);
}
arsort($filtered_results); // sort highest to lowest
$all_options = $field->type->getOptions($field);
echo '<ul>';
foreach($filtered_results as $option_id => $count) {
    $option = $all_options->get($option_id);
    echo "<li>{$option->title} ({$count})</li>";
}
echo '</ul>';