Im trying to get make an sql query that will return the last blog post for each user. I want to have a list of the latest blog posts, one per user...and I need it to be able to be paged as obviously if there are alot of users I dont want to display all the results on one page.
Here's what I have -
SELECT * FROM `node` WHERE type = 'blog' ORDER BY created DESC.
This is no good to me though as it returns all the blog entries for each user whereas I only want their latest entry.
I think if you want to use drupals pager_query function you have to do it all in one sql statement so is it possible just to get their last entry using a single sql query?
Here's a site that has exactly the functionality Im looking for - http://www.leggopoker.com/blogs/
It lists the latest blog post by each user and is pageable. Can this by done in Drupal?
Comments
May I ask why you're not
May I ask why you're not using the Views module? I'm not 100% sure but I think Views can do that.
I dont see how Views can just
I dont see how Views can just select the last post of a content type, in this case 'blog', for a particular user so Views isn't able to do it.
_
You can get close by grouping on the user name field, but it will show every post by that author. You'd then have to using theming to control just outputting the single most recent post.
Try this: SELECT * FROM
Try this:
SELECT * FROM `node` WHERE `type` = 'blog' AND `nid` IN (SELECT DISTINCT MAX(`nid`) FROM `node` WHERE `type` = 'blog' GROUP BY `uid`);
This will search through blog nodes for distinct user id's and give you back the max number from all of that users' blog nid's.
Then the results are pushed into your select * statement using IN to grab the latest nid for all distinct users. Since it's distinct per uid, it's only grabbing 1. :)
Try it out in an sql browser and see if it's the results you are looking for.
thanks mate, that seems to do
thanks mate, that seems to do exactly what i need, cheers!
Ok so SELECT * FROM `node`
Ok so
SELECT * FROM `node` WHERE `type` = 'blog' AND `nid` IN (SELECT DISTINCT MAX(`nid`) FROM `node` WHERE `type` = 'blog' GROUP BY `uid`);selects the latest blog post for each user.
I want to order this by when this blog post was created but when I make it
SELECT * FROM `node` WHERE `type` = 'blog' AND `nid` IN (SELECT DISTINCT MAX(`nid`) FROM `node` WHERE `type` = 'blog' GROUP BY `uid`) ORDER by 'created'it doesn't change the result at all. Can someone try it in phpmyadmin and see if they can get it to order by 'created'
My mistake was the quotation
My mistake was the quotation marks around 'created'