RFC: extracting distinct values

etc · 2020-10-08 10:56:22

Many popular questions on the forum can be formulated as ‘how to extract distinct values from articles lists’. For example, you might want to output all authors having published in a given section during the last year.

The typical solution is using if_different tag (I omit break etc for simplicity):

<txp:article_custom section="articles" time="-1 year" sort="AuthorID">
    <txp:if_different>
        <txp:author />
    </txp:if_different>
</txp:article_custom>

This works, but is not optimal, because

even if only one author was active, the parser must loop through all matched articles, consuming time;
while we need only AuthorID here, all article fields will be retrieved from db, unnecessarily consuming memory and time;
one can not control the number of authors via limit and offset attributes, which will act on articles instead.

Whence my proposal (actually already implemented in 4.8.4, but can yet be revoked): add an attribute that will serve to extract only desired fields from the db, ensuring that the extracted rows are distinct. Currently this attribute is called fields and works like this:

<txp:article_custom section="articles" time="-1 year" fields="authorid">
    <txp:author />
</txp:article_custom>

You can test it on demo site. If judged useful, I will explain the feature more in depth.

gaekwad · 2020-10-08 11:07:34

+1 for useful. I won’t be able to use it in anger right now, but I can see a need for a future project I’m spec’ing out.

Pat64 · 2020-10-08 13:44:45

+1

Myusername · 2020-10-08 15:06:41

+1

etc · 2020-10-09 11:18:42

As by-product, including thisid in fields list allows for processing all matching articles (since thisid is unique), but only with given fields populated from db. This makes menus constructions less time/memory expensive, since we don’t need to extract articles body etc:

<txp:article_custom fields="thisid, title, section, url_title">
    <txp:permlink><txp:title /></txp:permlink>
</txp:article_custom>

This can become even more important in the custom-fields branch, where the custom fields are stored in separate tables.

Pat64 · 2020-10-09 15:59:04

Gorgeous!

Is that affect only article (& article custom) tags?

etc · 2020-10-09 19:34:30

Pat64 wrote #326283:

Is that affect only article (& article custom) tags?

Yes, at the moment, but it should be straightforward to extend it to other content types.

etc · 2020-10-09 20:44:00

Here is a little syntactic question to answer.

Suppose we want to output recently active authors with their latest article publish dates. The straightforward

<txp:article_custom time="-1 year" fields="authorid" sort="Posted DESC">
    <txp:author /> (<txp:posted />)
</txp:article_custom>

will not work, since posted field is not populated. If we add it

<txp:article_custom time="-1 year" fields="authorid, posted" sort="Posted DESC">
    <txp:author /> (<txp:posted />)
</txp:article_custom>

all distinct (authorid, posted) pairs will be output, not only the most recent one. Currently the problem is solved via

<txp:article_custom time="-1 year" fields="authorid, posted=max" sort="Posted DESC">
    <txp:author /> (<txp:posted />)
</txp:article_custom>

The question is: is posted=max syntax clear enough? What would you suggest otherwise?

zero · 2020-10-10 09:59:26

max isn’t so clear to me. Perhaps late and early?

etc · 2020-10-10 10:03:25

zero wrote #326310:

max isn’t so clear to me. Perhaps late and early?

No :-) because it should be applicable to any field, not only dates, e.g. max(comments_count).

zero · 2020-10-10 10:06:59

etc wrote #326311:

No :-) because it should be applicable to any field, not only dates, e.g. max(comments_count).

The only other one I can think of is fresh and stale

gaekwad · 2020-10-10 18:07:05

etc wrote #326298:

all distinct (authorid, posted) pairs will be output, not only the most recent one. Currently the problem is solved via

<txp:article_custom time="-1 year" fields="authorid, posted=max" sort="Posted DESC">...

The question is: is posted=max syntax clear enough? What would you suggest otherwise?

If only the most recent value is desired, could limit be deployed in article_custom? The fields pair is defined, the sort gets the most recent and limit does its thing…or am I missing something?

Textpattern CMS

Textpattern CMS support forum

#1 2020-10-08 10:56:22