latest article per author

colak · 2022-09-19 05:05:58

Is there a way to post the latest article per author without having to repeat the article_custom tag like below?

<txp:article_custom author="auth1" limit="1" />
<txp:article_custom author="auth2" limit="1" />
etc

Dragondz · 2022-09-19 06:28:25

Hi

May be something like this (not tested):

<txp:authors name="auth1, auth2">
<txp:article_custom author='<txp:author />' limit="1" />
</txp:authors>

Look at txp:authors doc to see how to select somoe authors by group or name list

Cheers.

etc · 2022-09-19 09:10:42

The question is natural, but not the answer. Unless you want to pass the whole article list ordered by AuthorID, Posted DESC through <txp:if_different /> filter (can be long), a complex SQL query looks necessary:

<txp:etc_query populate="article" markup="db"
data="SELECT *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod FROM textpattern t1 JOIN 
(
  SELECT AuthorID, max(Posted) AS max_posted
  FROM textpattern WHERE Status >= 4
  GROUP BY AuthorID
) t2
ON t1.AuthorID = t2.AuthorID AND t1.Posted = t2.max_posted ORDER BY t1.AuthorID">
    <txp:title /> by <txp:author /> (<txp:posted />)
</txp:etc_query>

I don’t see how we could pack this monster into <txp:article /> attributes, though it’s tempting.

colak · 2022-09-19 10:35:17

Thanks so much guys.

Will test this afternoon.

etc · 2022-12-10 16:35:17

The following ‘windowed’ query is up to 10 times faster:

SELECT * FROM (SELECT *, (RANK() OVER (PARTITION BY `AuthorID` ORDER BY Posted DESC)) AS `$rank`
    FROM textpattern WHERE ...) textpattern
    WHERE `$rank` IN (1) ORDER BY AuthorID, $rank

I have implemented it in my local cf branch, extending fields attribute as

<txp:article_custom fields="authorid, RANK[1](*)" sort="Posted DESC" />

It works fine and could be merged in 4.9 if we quickly find a clever syntax that would later allow for other SQL window functions.

Bloke · 2022-12-10 16:52:17

Intriguing. If window functions are supported across SQL flavours in PDO (i.e. are not MySQL specific) then they could be mighty handy in querying custom fields!

Hmmm… Syntax. Syntax.

etc · 2022-12-10 18:19:35

Yep, just imagine LEAD(ID) and LAG(ID) instead of two queries for next/prev article id. Window functions are SQL 2003 standard, but require a recent version, whatever SQL flavour. For example, MySQL supports window functions since 8.0 only and MariaDB since 10.2.

Bloke · 2022-12-10 20:07:57

etc wrote #334263:

just imagine LEAD and LAG instead of two queries for next/prev article id.

Mmmm. Bliss.

MySQL supports window functions since 8.0 only and MariaDB since 10.2.

Ooh. How soon before we can bin MySQL 5? :)

colak · 2022-12-11 07:32:20

Bloke wrote #334264:

Ooh. How soon before we can bin MySQL 5? :)

Would txp 4.9 be a good time? You could even make an announcement 4.8.9 that it would be the last MySQL 5 compatible version.

etc · 2022-12-11 10:14:57

We could add some windowed queries ability to 4.9 as a bonus for MySQL 8 users (the others would just not be able to enjoy it), packing them into fields or other attribute. But this way is limited and not very intuitive.

Why wouldn’t we introduce ‘virtual’ custom fields in cf branch? An admin could define via cf meta tab a field, say, posted_per_author like

RANK() OVER (PARTITION BY `AuthorID` ORDER BY Posted DESC)

Users then would just call

<txp:article_custom posted_per_author="1" />

to output only the latest article for each author in one query. Not straightforward, but this looks doable. And we would switch to MySQL 8 that time.

Thoughts?

Bloke · 2022-12-11 11:02:26

etc wrote #334267:

We could add some windowed queries ability to 4.9 as a bonus for MySQL 8 users

That’d be ace.

Why wouldn’t we introduce ‘virtual’ custom fields in cf branch?

Genius. So do we need to add a column to indicate a field is to be ‘hidden’ or ‘internal’ or something like that? Or can we use one of the existing status fields in the meta table to differentiate? Not sure we want such fields to pop up in the UI.

Bloke · 2022-12-11 11:04:36

colak wrote #334266:

Would txp 4.9 be a good time? You could even make an announcement 4.8.9 that it would be the last MySQL 5 compatible version.

We’re kind of thinking that 4.9 (dev branch, as of now) will be the next version. Like, in a few weeks. Unless we can find a way to talk ourselves out of it.

Textpattern CMS

Textpattern CMS support forum

#1 2022-09-19 05:05:58