Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#13 2020-10-10 18:17:01

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: RFC: extracting distinct values

gaekwad wrote #326319:

If only the most recent value is desired, could limit be deployed in article_custom?

We want the most recent value for each author:

Donald Duck (2020-02-02)
Donald Swain (2002-02-20)
...

Offline

#14 2020-10-10 18:23:50

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,134
GitHub

Re: RFC: extracting distinct values

etc wrote #326322:

We want the most recent value for each author:

Ah. Yes, of course.

The type of person doing this kind of tag soup is not the average Textpattern-er, so personally I’d err towards going with what terminology you think is most appropriate given your intentions for other article data, and we can explain it with examples in the docs pages.

(My 2 shekels.)

Offline

#15 2020-10-10 18:48:52

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: RFC: extracting distinct values

gaekwad wrote #326323:

The type of person doing this kind of tag soup is not the average Textpattern-er

Oh, this beverage is rather popular: limit categories to the current section, etc. Anyway, the question is not semantics (max is a right name, I think) but syntax:

posted=max, max(posted) or something else?

Offline

#16 2020-10-10 18:57:17

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

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.

Txp Builders – finely-crafted code, design and Txp

Offline

#17 2020-10-10 18:57:38

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,134
GitHub

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

#18 2020-10-10 18:58:26

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: RFC: extracting distinct values

gaekwad wrote #326326:

There’s far more prior art for foo="bar" than foo(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.

Txp Builders – finely-crafted code, design and Txp

Offline

#19 2020-10-10 19:04:08

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,134
GitHub

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

#20 2020-10-10 19:12:26

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

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 with foo(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

#21 2020-10-10 19:17:41

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

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.

Txp Builders – finely-crafted code, design and Txp

Offline

#22 2020-10-10 19:31:52

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: RFC: extracting distinct values

Bloke wrote #326330:

Just count to 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

#23 2021-01-04 17:22:24

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

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

Board footer

Powered by FluxBB