How to get all unique values for a field?

[The G]

Hi,

What's the processwirish way of doing:

SELECT DISTINCT `pages`.`name`, COUNT(*)
FROM `field_myfield`
LEFT JOIN `pages` ON (`field_myfield`.`data` = `pages`.`id`)
GROUP BY `pages`.`name`

?

[LostKobrakai]

I'll try it, but it won't be as efficient as your mysql statement. 

// This is optional if you know the templates
$t = new TemplatesArray();
foreach($templates as $template{
  if($template->hasField("myfield")) $t->add($template)
}

$ps = $pages->find("template=$t");
$list = new PageArray();
foreach($ps as $p){
  $list->import($p->get("myfield"));
}
$list = $list->unique();
$list->explode("name");

Edit: getting the templates could be more efficient like this, but I'm not sure about this.

$myfield = $fields->get("myfield");
$fgs = $myfield->getFieldgroups();

$t = new TemplatesArray();
foreach($fgs as $fg){
  $t->import($fg->getTemplates());
}

[The G]

Thanks for your answer.

I realize I should have given more information.

The fields are video clip parameters, like format and length. I made the format a Page field and the length (duration) an integer field. All fields are included in a single template, so I already know the template.

I want to make a search filter with every clip parameter as an option, each one displaying its video clips count, like this:

Problem is, there are more than 20000 video clips and potentially many more, so I cannot do find() without limit. Right now I'm using

SELECT DISTINCT `pages`.`name`, COUNT(*)
FROM `field_format`
LEFT JOIN `pages` ON (`field_format`.`data` = `pages`.`id`)
GROUP BY `pages`.`name1

for Page fields and

SELECT DISTINCT `data` as name, COUNT(*) as count
FROM `field_length`
GROUP BY name

for scalar fields.

I was wondering if there's some ProcessWire API magic for this.

[LostKobrakai]

Can you elaborate why find() doesn't work for you? It's made for that job.

Edit: Just read that you need the count.

Use count() instead of find(). It's exactly the same, but does not load the data and just returns the number of returned pages.

[The G]

Using find() without limit on a large number of pages wil eat all memory and bring your site down sooner or later, depending on your server configuration.

Actually, I need to get all unique values from each field. Yes, I need the count for each value, but I need the values first.

[LostKobrakai]

Getting the different values is easy for the pagefields, as you know all the possible values (the pages). 

$formats = $pages->find("template=format");
foreach($formats as $format){
  $num = $pages->count("template=video, format={$format->id}"); // Count all videos where format is $format
  if($num) echo $num;
}

For the other informations I'd suggest going the same way. E.g. for clip length:

• Add pages to your tree for the different clip length options (5-10, 10-15, …; you don't need the exact values for the count).
• Add a pagefield to the video's template: "clip_length_option"
• Use a hook to update this field on page save (example) dependent on the field that holds the real length of a clip.
• Use mysql to update existing video entries with the new field data (i think that's the fastest way as you're already using mysql).

Hope this helps.

[The G]

Wow, thanks! Lots of good ideas! This forum is out of this world, I tell you.

I replaced

        $pdostmt = wire('database')->prepare('
            SELECT DISTINCT field_'.$dbtable.'.data as value, field_title.data as label, COUNT(*) as count
            FROM field_'.$dbtable.'
            LEFT JOIN field_title ON (field_title.pages_id = field_'.$dbtable.'.data)
            GROUP BY field_'.$dbtable.'.data
        ');
        $pdostmt->execute();
        while ($row = $pdostmt->fetch(PDO::FETCH_ASSOC))
            $filter_options[$criterion][] = array(
                'name'  => $row['name'],
                'label' => $row['label'],
                'count' => $row['count']
            );

with

      $values = wire('pages')->find('parent=/search/'.$criterion);
      foreach ($values as $value)
          $filter_options[$criterion][] = array(
              'name'  => $value->name,
              'label' => $value->title,
              'count' => wire('pages')->count('parent=1020,'.$criterion.'='.$value),
          );

where $criterion is from a 4 fields array, and 1020 is the video clips container id.

I like that the synthax is much cleaner and I can further glue selectors to the initial selector, But it works slower, as in 0.6 seconds slower with 4 fields and 20k video clips. It was surely expected and I think that the delay will increase linearly along with the video clips count.

So I'll stay with the SQL version, unless there's possible to further optimize the API way.

Thank you.


 

[LostKobrakai]

Directly fetching only the cells you really need will always be faster than the api. It's the nature of having an additional abstraction layer between mysql and php.

[diogo]

Just a thought — you could also keep those numbers in dedicated fields in a page and update them every time a video is created or deleted.

edit: the famous Eiffel Tower looks unrecognisable in that clip

[horst]

...

I like that the synthax is much cleaner and I can further glue selectors to the initial selector, But it works slower, as in 0.6 seconds slower with 4 fields and 20k video clips. It was surely expected and I think that the delay will increase linearly along with the video clips count.

So I'll stay with the SQL version, unless there's possible to further optimize the API way.

It is mostly possible to cache values or even markup. Don't know how your site works, but if it isn't updated every minute there is a good chance to cache and re-use the markup of your filter form. This would speed up things a lot.

[The G]

@diogo: nice idea to store the numbers in a page. Problem is, the numbers should reflect the actual selection count, so after selecting an option every count is different. And then there are the variants with two options selected and then with three... Seems like a lot. I'm sure your idea will haunt me, though.

That clip has "artistic perspective". It also happens that is the default "no-video" clip .

@horst: it is all very true. At the same time caching the markup obtained using SQL would be the same, only with a faster first access. Wouldn't it?