Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Re: RFC: extracting distinct values
It’ll be MAX(posted) for me. It’s more SQLish and we’re dealing with SQL inside these fields in the same way we can currently use FIELD(column, value1, value2, value3) inside sort. It feels more consistent.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Hire Txp Builders – finely-crafted code, design and Txp
Offline
Re: RFC: extracting distinct values
etc wrote #326324:
posted=max, max(posted) or something else?...
There’s far more prior art for foo="bar" than foo(bar), so given the A/B choice I’d choose the former.
Edit: actually, Stef’s right – ignore me.
Last edited by gaekwad (2020-10-10 18:58:46)
Offline
Re: RFC: extracting distinct values
gaekwad wrote #326326:
There’s far more prior art for
foo="bar"thanfoo(bar), so given the A/B choice I’d choose the former.
Haha! I just chose the opposite. But I get what you mean. Hmmmm. Torn. It’s more attributey but then if we’re not using quotes, it might be confusing for people that are used to using them in regular attributes, to have to omit them here.
Last edited by Bloke (2020-10-10 19:00:36)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Hire Txp Builders – finely-crafted code, design and Txp
Offline
Re: RFC: extracting distinct values
Bloke wrote #326327:
I just chose the opposite. But I get what you mean.
Tag stuff is fairly well-defined with plenty of foo="bar" formatting, but I totally see your point. I guess it depends on whether you want to introduce something that’s a bit different into tag markup that makes it a more complex.
I would wager most people don’t do this kind of stuff, but the kind who do would be comfortable with either option, given sufficient explanation in the relevant doc(s). As you say, it’s database extraction speak, not HTML-like markup…is there a precedent for tag stuff with foo(bar) constructs already?
Offline
Re: RFC: extracting distinct values
gaekwad wrote #326328:
Tag stuff is fairly well-defined with plenty of
foo="bar"formatting…is there a precedent for tag stuff withfoo(bar)constructs already?
Be it posted=max or max(posted), it’s not an attribute but a value:
fields="authorid, max(posted), sum(comments_count)"And while we are there, which other SQL aggregate functions could be useful? Currently we have max, min, sum, avg.
Offline
Re: RFC: extracting distinct values
etc wrote #326329:
which other SQL aggregate functions could be useful? Currently we have
max, min, sum, avg.
Just count to add from that list, I think. Unless you reckon that’s adequately covered elsewhere. The rest are a bit esoteric and statistics-driven to be of much use, imo.
The only other outliers are:
group_concat()but since you have to use groups for that, we’re starting to get into seriously convoluted tag territory.json_*because someone might want to pull out json-formatted content in future. But since we’re only targeting article tags here and don’t store JSON data yet, this might be overkill now.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Hire Txp Builders – finely-crafted code, design and Txp
Offline
Re: RFC: extracting distinct values
Bloke wrote #326330:
Just
countto add from that list, I think. Unless you reckon that’s adequately covered elsewhere.
count is already available, but not as fields value: max(posted) is a date and can be output via <txp:posted />, but not count(posted).
group_concat()but since you have to use groups for that, we’re starting to get into seriously convoluted tag territory.
We actually already do GROUP BY, so adding group_concat() is not a problem, but I doubt of its utility.
json_*because someone might want to pull out json-formatted content in future. But since we’re only targeting article tags here and don’t store JSON data yet, this might be overkill now.
They look promising, but are available in MySQL 5.7.22+ only, do we require this?
Offline
Re: RFC: extracting distinct values
FWIW, GROUP_CONCAT(field SEPARATOR ',') is available now (4.8.5) as list(field). It’s yet time to change list for concat or join or ..?
Offline
Re: RFC: extracting distinct values
Circling back to this feature… I want to build a dropdown list of all values of custom_5, to allow filtering by that field (it’s product “brand”). My understanding:
<article::custom section='<txp:section />' fields="thisid, list(brand)" break="" limit="0">
<custom::field name="brand" />
</article::custom>should build me a unique comma-separated list of custom_5 values from articles in that section. But instead, I’m getting all of them, repeated. It doesn’t seem to matter if I include thisid or not.
What am I missing?
EDIT: duh, it’s simpler than I thought. Swear this didn’t work the first time I tried it, but now it does:
<article::custom section='<txp:section />' fields="brand" break="," limit="0">
<custom::field name="brand" />
</article::custom>Last edited by Bloke (2025-09-23 21:23:49)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Hire Txp Builders – finely-crafted code, design and Txp
Offline
Re: RFC: extracting distinct values
Bloke wrote #340633:
<article::custom section='<txp:section />' fields="thisid, list(brand)" break="" limit="0">...should build me a unique comma-separated list of custom_5 values from articles in that section.
The intended use is fields="section, list(brand)", though this will not remove duplicates.
Since GROUP_CONCAT() supports DISTINCT clause, this is easy to add. It makes sense then to sort by brand too. I wouldn’t like to alter list() behaviour, so a new syntax is needed. Should it be unique() or distinct() or something else?
Offline
Re: RFC: extracting distinct values
etc wrote #340645:
The intended use is
fields="section, list(brand)", though this will not remove duplicates.
Oh. So I need section as well, because I’m filtering by section= in the tag? Maybe that’s where I was going wrong
I wouldn’t like to alter
list()behaviour, so a new syntax is needed. Should it beunique()ordistinct()or something else?
Either works for me. distinct() is more SQLish.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Hire Txp Builders – finely-crafted code, design and Txp
Offline
Offline
Offline