SQL difficulty - unique author posting

[spiritquest]

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]

[garvinhicking]

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

[spiritquest]

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.

[garvinhicking]

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

[spiritquest]

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.

[garvinhicking]

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

[spiritquest]

Hi,

I am using: MySQL v 5.0.27

[garvinhicking]

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

[spiritquest]

Sure !!

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

Ket

[garvinhicking]

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

[spiritquest]

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

[garvinhicking]

Hi!

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

Best regards,
Garvin