Get last 3 distinct author posts

MrViSiOn · 2015-04-26 18:40:55

Hi

How could I get last 3 distinct author posts?

I don’t want repeated author in that query Is this possible?

Thank you

colak · 2015-04-26 19:59:10

Untested

<txp:article_custom author='<txp:author />' limit="3" />

Cancel this. I re-read your question

Last edited by colak (2015-04-26 20:01:02)

colak · 2015-04-26 20:11:28

this may work

<txp:article_custom sort="AuthorID ASC, Title ASC">
<txp:if_different><txp:author /></txp:if_different>
<txp:title />
</txp:article_custom>

MrViSiOn · 2015-04-26 20:45:33

I thought something like this…

<txp:variable name="authors"><txp:php>
        $sql = "SELECT b.`AuthorID`,b.ID as ID, b.Posted FROM textpattern b  WHERE b.ID IN (SELECT MAX(a.`ID`) AS ID FROM textpattern a WHERE a.`Section` IN ('opinion','blogs') GROUP BY a.`AuthorID`) ORDER BY b.`Posted` DESC LIMIT 3";
        $rs = safe_query($sql);
        $ides = array();
        while ($row = mysql_fetch_assoc($rs)) {
	         $ides[] = $row["ID"];
        }
        echo implode(",", $ides);
</txp:php></txp:variable>
<txp:article_custom id="<txp:variable name="authors" />" form="listafoto-opinion" />

Last edited by MrViSiOn (2015-04-26 20:47:15)

uli · 2015-04-26 21:57:47

I can’t judge what you’re doing in the SQL part, but this
<txp:article_custom id="<txp:variable name="authors" />" form="listafoto-opinion" />
will not work for the wrong sort of quotes around the nested TXP tag. Try single ones:
<txp:article_custom id='<txp:variable name="authors" />' form="listafoto-opinion" />

MrViSiOn · 2015-04-27 08:04:59

Thank you all for answering.

uli… the way I wrote my code is working fine with two doble quotes… ¿?

etc · 2015-04-27 13:34:07

Hi,

if I’m not mistaken, your query assumes that recent Posted dates correspond to highest IDs. If so, it can be simplified:

SELECT MAX(ID) FROM textpattern WHERE Section IN ('opinion','blogs') AND Status = 4 and Posted <= now() and (Expires >= now() or Expires = '0000-00-00 00:00:00') GROUP BY AuthorID ORDER BY MAX(Posted) DESC LIMIT 3

But this assumption is not bullet-proof, since Posted can be changed by an author. This is more robust:

SELECT ID FROM (SELECT ID, AuthorID, Posted FROM `textpattern` WHERE Section IN ('opinion','blogs') AND Status = 4 and Posted <= now() and (Expires >= now() or Expires = '0000-00-00 00:00:00') ORDER BY Posted DESC) t
	GROUP BY AuthorID ORDER BY Posted DESC LIMIT 3

MrViSiOn wrote #290256:

uli… the way I wrote my code is working fine with two doble quotes… ¿?

It works, but only accidentally (on secondpass). This will not work, for example:

<txp:article_custom id="<txp:variable name="authors" />"><txp:title /></txp:article_custom>

So you’d better follow uli’s advice.

Last edited by etc (2015-04-29 10:54:03)

Gocom · 2015-04-28 14:41:27

etc wrote #290260:

But this assumption is not bullet-proof, since Posted can be changed by an author. This is more robust:

You may want to add Expires, Status and Posted to the statement so that you don’t get unpublished or expired articles as a result. E.g.

Status = 4 and Posted <= now() and (Expires >= now() or Expires = '0000-00-00 00:00:00')

etc · 2015-04-29 10:44:25

Gocom wrote #290274:

You may want to add Expires, Status and Posted to the statement so that you don’t get unpublished or expired articles as a result.

Added this abobe, thanks Jukka.

MrViSiOn · 2015-05-01 08:25:53

Yes I need status post in my query

Thank you!

Textpattern CMS

Textpattern CMS support forum

#1 2015-04-26 18:40:55