Retrieve unique Auto-tag TV values for use in another TV

Support
1

Hi all,

I have an Auto-tag TV field which stores the name of Authors.

I want to generate a list of these values, without duplicates, to populate a Listbox TV or perhaps as a setting in a Content Blocks layout.

I’ve tried using RowBoat, which works to a point, but I can’t find a way to select DISTINCT on the value column:

[[!Rowboat?
   &table=`mdx2023_site_tmplvar_contentvalues`
   &tpl=`cb-list-authors-tpl`
   &limit=`0`
   &sortBy=`value`
   &columns=`{"value":"author","id":"authorid"}`
   &where=`{"tmplvarid:=":"73"}`
   &cache=`0`
   &debug=`1`
]]

This will return a list of all entries, including duplicates. Adding DISTINCT to the value column doesn’t work …

   &columns=`{"DISTINCT value":"author","id":"authorid"}`

I think RowBoat reads “DISTINCT value” as the table name.

Does anyone know of a way to get distinct values via RowBoat?

Thinking more about it I guess I could add an additional snippet and use array_unique() to reduce the RowBoat result down to unique values.

It’s a bit clunky though - any thoughts v welcome.

2

I don’t think you can use “distinct” in RowBoat.
But you can always create your own xPDO query in a snippet:

$c = $modx->newQuery('modTemplateVarResource');
$c->where(['tmplvarid' => 73]);
$c->sortby('value','ASC');
$c->select(['value']);
$c->distinct(); //to query only distinct values

// or use $modx->getCollection('modTemplateVarResource', $c); instead of the code below
$c->prepare();
$c->stmt->execute();
$values = $c->stmt->fetchAll(PDO::FETCH_COLUMN, 0);
return implode(',', $values);
1 Like
3

Thanks so much @halftrainedharry

That cuts the corner :+1:

I did start to look down that road - but I was missing modTemplateVarResource and didn’t know where to find out what it should be!

Much appreciated.

4

The MODX schema is often a good place to find such information.

1 Like
5

That’s exactly what I needed and will need again! A great help as always. Thanks.

6

Keep in mind that any TVs set to their default value will not be in the modTemplateVarResource table.

1 Like
7

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.