Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Pages: 1
#1 2015-04-26 18:40:55
- MrViSiOn
- Member
- Registered: 2014-11-17
- Posts: 26
Get last 3 distinct author posts
Hi
How could I get last 3 distinct author posts?
I don’t want repeated author in that query Is this possible?
Thank you
Offline
Re: Get last 3 distinct author posts
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)
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
Re: Get last 3 distinct author posts
this may work
<txp:article_custom sort="AuthorID ASC, Title ASC">
<txp:if_different><txp:author /></txp:if_different>
<txp:title />
</txp:article_custom>
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#4 2015-04-26 20:45:33
- MrViSiOn
- Member
- Registered: 2014-11-17
- Posts: 26
Re: Get last 3 distinct author posts
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)
Offline
#5 2015-04-26 21:57:47
- uli
- Moderator
- From: Cologne
- Registered: 2006-08-15
- Posts: 4,304
Re: Get last 3 distinct author posts
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" />
In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links
Offline
#6 2015-04-27 08:04:59
- MrViSiOn
- Member
- Registered: 2014-11-17
- Posts: 26
Re: Get last 3 distinct author posts
Thank you all for answering.
uli… the way I wrote my code is working fine with two doble quotes… ¿?
Offline
Re: Get last 3 distinct author posts
Hi,
if I’m not mistaken, your query assumes that recent Posted
dates correspond to highest ID
s. 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)
Offline
Re: Get last 3 distinct author posts
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')
Offline
Offline
#10 2015-05-01 08:25:53
- MrViSiOn
- Member
- Registered: 2014-11-17
- Posts: 26
Re: Get last 3 distinct author posts
Yes I need status post in my query
Thank you!
Offline
Pages: 1