FieldTypeOptions only "in use" values

[DL7]

Hi,

I have multiple Options fields on a certain page. On an overview page I like to add a filter, which display`s all the distinct value`s of the optionfield, but only the once that are in use. As an addition I would like to have the amount of times this specific value is in use. So:

Given the following:

1=ABC

2=DEF

3=FOO

4=BAR

I have for examples 5 pages were

Page 1 uses 1

Page 2 uses 1

Page 3 uses 2

Page 4 uses 4

Page 5 uses 4

So on the overviewpage I like to have an overview with (note the 3=FOO is not displayed):

Options:

ABC (1)

DEF (1)

BAR (2)

I know in the API there is a possibility to use $all_options = $field->type->getOptions($field); But I would need the $all_inuse_options ;-)

Next to that It would be a nice to have to add the count.

[Zeka]

Hi @DL7

I think that you should stick with page field instead of options fields. It is much more easier to accomplish what you want with that field.

Make read of this 

https://processwire.com/blog/posts/making-efficient-use-of-fields-in-processwire/#use-page-fields-rather-than-individual-checkboxes

 

[DL7]

Hi @Zeka,

Thanks for your swift reply. As I understand ir correctly I would need to create then a page for each individual value. That would work for instance for 10 values, but lets assume the options are countries, is would be a lot of work to add every single country as page.

Explaining it a bit more: What I`m trying to recreate is sort of a very small version of a so called facatted search functionality like on the sidebar on this page: https://www.laptopshop.nl/category/45702/laptops.html

 

[adrian]

1 hour ago, DL7 said:

but lets assume the options are countries, is would be a lot of work to add every single country as page.

Migrator has a country list complete with ISO country code fields etc.

If you want to go this route, install this commit of Migrator: https://github.com/adrianbj/ProcessMigrator/tree/3e2121b8fdb68e9d9dc0c6aca8aae75e923a2669

Alternatively, use https://processwire.com/talk/topic/4523-page-field-select-creator/ and paste in a list of countries, or even http://modules.processwire.com/modules/batch-child-editor/

A CSV list like this (https://developers.google.com/public-data/docs/canonical/countries_csv) would be helpful for the last two options.

[DL7]

Hi @adrian ,

Thanks for this. Very helpful. But i`m still struggeling with the fact on how to get a list of the relevant items.

As per my example above, how would I check first of all the distinct values used? In either approach (options field or page) I still need to "loop" through the $page->category correct? How from an API call can I make sure only distinct values are shown in the loop?

Next to that the trick would also be to add a additional count. I would know how to do this from a SQL perspective, I`m just not sure on how to do this using an API call.

 

Note: a single page will only have 1 value as a set optionvalue (so no multiple values possible on a single page)

Thanks again!

 

[Zeka]

@DL7

Options ( page with template "options")

- ABS ( page with template "option")
- DEF ( page with template "option")
- BAR ( page with template "option")

Page 1 ( template "page" with single page field "options_field" ) use ABS
Page 2 ( template "page" with single page field "options_field" )  use DEF
Page 3 ( template "page" with single page field "options_field" ) use DEF
Page 4 (template "page" with single page field "options_field" ) use BAR

Then in your output 

$options = $pages->find("template=option, limit=100");

echo $options->each(function($item) {
	$count = $pages->count("template=page, options_filed=$item");
	if($count) {
		return "<li>{$item->title} ({$count})</li>";
	}
});

 

 

[kixe]

You can easily create a select field with countries with FieldtypeSelectExtOption
First install this table countries.sql via MysqlAdmin or another tool. Choose the new table in the settings of your FieldtypeSelectExtOption field. Done!
You have a comfortable access to all the data via API. Read the docs or the related thread https://processwire.com/talk/topic/9320-fieldtype-select-external-option/
 

 

 

[DL7]

Thanks for your help all, I'll give it a try.

[DL7]

Again thanks for your help @Zeka. I wasn't aware of the each() function, so that help alot! Just to be clear on the above reactions, with the country list example I just wanted to point out an example of a large dataset. I wasnt looking for a country list itself. But if I need it in the future, I'll have some great pointers on how to upload them ;-)

Next to that if you are really stubborn person (like I am ;-) and still want to utilize the fieldtype options for this, use this code:

$options = $fieldtypes->get('FieldtypeOptions')->getOptions('xyz'); 
$options->each(function($item) {
	$count = wire('pages')->count('template=basic, xyx='.$item.'');
	if($count) {
		echo "<li>{$item->title} ({$count})</li>";
	}
});

 

[Zeka]

@DL7

Great, if it works for you. 

 

[Robin S]

1 hour ago, DL7 said:

Next to that if you are really stubborn person (like I am ;-) and still want to utilize the fieldtype options for this, use this code:

$options = $fieldtypes->get('FieldtypeOptions')->getOptions('xyz'); 
$options->each(function($item) {
	$count = wire('pages')->count('template=basic, xyx='.$item.'');
	if($count) {
		echo "<li>{$item->title} ({$count})</li>";
	}
});

 

The thing about this approach where you foreach the selectable options (for an Options or a Page field) is that if you have 196 countries (for example) then that is 196 database queries. Not the end of the world or anything but also not very efficient. I like this code from Ryan that requires only a single DB query, and which can be adapted for an Options fieldtype:

$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>';

 

[DL7]

And you are right. In that case I would definitely go for a different approach. The one you mention from ryan is indeed also doing the trick. One thing to mention though on this is that this code isnt taking into account a certain status of the page the Option values it's been used at. I have certain pages that are for instance unpublished, hidden or even have custom status, that i've created myself, those would also be taking into account whereas for the other code I can make that exception on the selector.

 

[Robin S]

1 hour ago, DL7 said:

One thing to mention though on this is that this code isnt taking into account a certain status of the page the Option values it's been used at.

This is true. It starts to get a little more complex but with a few more steps you can filter out the non-public pages:

$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>';