Serendipity Forums

Posted: **Thu May 03, 2007 4:02 pm**

HI,

I've been trying to get a query running that would select the the last 3 authors that have posted to the blog, so i can have a most recent author system running.

the SQL I am using is here:

Code: Select all

SELECT DISTINCT 
	serendipity_authors.authorid AS authorID, serendipity_authors.realname AS authorName, serendipity_entries.isdraft, serendipity_entries.timestamp
FROM serendipity_groups, serendipity_authorgroups, serendipity_authors,serendipity_entries
WHERE serendipity_groups.name='Featured Author' AND serendipity_authorgroups.groupid='4' AND serendipity_authorgroups.authorid=serendipity_authors.authorid AND serendipity_authors.authorid=serendipity_entries.authorid AND serendipity_entries.isdraft='false'  ORDER BY serendipity_entries.timestamp DESC limit 0,3

but it doesn't work, I get duplicated authorIDs so if an author has published twice in close succession, the query will display them twice

here is an output from the mysql shell for the query above:

Code: Select all

+----------+---------------------+---------+------------+
| authorID | authorName          | isdraft | timestamp  |
+----------+---------------------+---------+------------+
|        4 | Jane Thurnell-Read  | false   | 1178184131 | 
|       12 | Bonnie Siefers      | false   | 1178109789 | 
|       12 | Bonnie Siefers      | false   | 1178065291 | 
+----------+---------------------+---------+------------+

.. any help would be appreciated.

BTW - Featured Author, and groupid 4 relate to one another in the [/code]

Posted: **Thu May 03, 2007 4:22 pm**

Hi!

Basically I would make the joins differently and start from the 'entries' DB table instead of the other way round. Then you don't need to join the 'groups' table if you already know the ID you restrict by.

Plus, in the end you need to group by the author ID to get proper distinct results.

This query seems to work for me:

Code: Select all

  SELECT e.authorid AS authorID, 
         a.realname AS authorName, 
         e.timestamp

    FROM serendipity_entries AS e

    JOIN serendipity_authors AS a
      ON e.authorid = a.authorid

    JOIN serendipity_authorgroups AS ag
      ON ag.authorid = a.authorid
  
   WHERE ag.groupid = 4
     AND e.isdraft  = 'false'

GROUP BY e.authorid
ORDER BY e.timestamp DESC
   LIMIT 0, 3

Posted: **Thu May 03, 2007 5:19 pm**

Hi Garvin,

Thanks for the quick reply.

It certainly pulls distinct results and is much cleaner than my messy query, but it doesn't pull the most recent results.

for some reason it selects entries with timestamps weeks ago, the latest post does not come through at all.

your query:

Code: Select all

+----------+-------------------+------------+
| authorID | authorName        | timestamp  |
+----------+-------------------+------------+
|       14 | Ben West          | 1175176440 | 
|       12 | Bonnie Siefers    | 1174471905 | 
|        9 | Bibi van der Zee  | 1172834096 | 
+----------+-------------------+------------+

my alteration to your query changing the GROUP By to

Code: Select all

GROUP BY e.authorid,e.timestamp

to see if it would resepct the lastest timestamp entry

Code: Select all

+----------+---------------------+------------+
| authorID | authorName          | timestamp  |
+----------+---------------------+------------+
|        4 | Jane Thurnell-Read  | 1178184131 | 
|       12 | Bonnie Siefers      | 1178109789 | 
|       12 | Bonnie Siefers      | 1178065291 | 
+----------+---------------------+------------+

You can see the differences in the timestamp values .. i'll try and do some research into this. Thanks again.

Posted: **Fri May 04, 2007 12:55 pm**

Hi!

What happens if you remove the "group by"?

Actually the query only fetches entries written by usergroup #4...that was the intention, right?

Regards,
Garvin

Posted: **Fri May 04, 2007 3:47 pm**

Hi,
Removing group by produces the same results:

Code: Select all

+----------+---------------------+------------+
| authorID | authorName          | timestamp  |
+----------+---------------------+------------+
|        4 | Jane Thurnell-Read  | 1178184131 | 
|       12 | Bonnie Siefers      | 1178109789 | 
|       12 | Bonnie Siefers      | 1178065291 | 
+----------+---------------------+------------+

Yes, all members of author group 4 required. Its just as you can see above in this result author 12 is repeated twice, she needs to be found once and the next most recent author returned to the result set.

Posted: **Fri May 04, 2007 4:27 pm**

Hi!

You are using MySQL, right? Which version?

On postgresql this whole query might indeed not work as intended, and I only have MySQL in mind.

Regards,
Garvin

Posted: **Mon May 07, 2007 5:45 pm**

Hi,

I am using: MySQL v 5.0.27

Posted: **Tue May 08, 2007 9:44 am**

Hi!

Could you maybe provide a SQL dump file for download which holds your tables:

* serendipity_entries
* serendipity_authors
* serendipity_authorgroups

? You can strip the 'body' and 'extended' colums of your entries table, and remove the 'password' column from serendipity_authors.

With that data I could test the SQL queries and see if I can come up with a solution. Without actual test data it's sadly hard for me to reproduce

Regards,
Garvin

Posted: **Tue May 08, 2007 12:42 pm**

Sure !!

I was thinking along the same lines. I will organise that today, thanks !

Ket

Posted: **Wed May 16, 2007 4:59 pm**

Hi!

Does this SQL work for you:

Code: Select all

SELECT e.authorid AS authorID, a.realname AS authorName, MAX( e.timestamp ) AS timestamp
FROM serendipity_entries AS e
JOIN serendipity_authors AS a ON e.authorid = a.authorid
JOIN serendipity_authorgroups AS ag ON ag.authorid = a.authorid
WHERE ag.groupid =4
AND e.isdraft = 'false'
GROUP BY e.authorid
ORDER BY MAX( timestamp ) DESC
LIMIT 0 , 3

(I just introduced ordering by MAX(timestamp) instead of "timestamp" only, because the grouping will affect mutlpiple entries by the same author and by default return the last found entry, not the "latest" one...)

Regards,
Garvin

Posted: **Wed May 16, 2007 5:41 pm**

Garvin !

Nice one... Yes this pulls the latest information as required. I would not have known to look for the MAX function.

Thanks, works a treat.

Regards,

Ket

Posted: **Thu May 17, 2007 2:40 pm**

Hi!

You're welcome, very glad that that worked out for you. Have fun!

Best regards,
Garvin

Serendipity Forums

SQL difficulty - unique author posting

SQL difficulty - unique author posting

Re: SQL difficulty - unique author posting