I've finally got the blogs database calls down to just one, and here is the SQL...
SELECT blogs.*, threads.id, COUNT(posts.id)-1
FROM blogs
LEFT JOIN threads ON blogs.date = threads.date AND threads.title LIKE 'Blog Entry for %'
LEFT JOIN posts ON threads.id = posts.threadId
GROUP BY blogs.date
ORDER BY blogs.date DESC
LIMIT $blogOffset, 10;
Before today, each blog entry had another SQL statement to determine the number of comments made, making eleven database calls in total. Now there's just one.
I understand that joining a blog and forum together in this manner isn't terribly robust. Using the date (accurate to only one second) and that the thread's title starts with "Blog Entry for", will have to do until I can come up with something better.
SELECT blogs.*, threads.id, COUNT(posts.id)-1
FROM blogs
LEFT JOIN threads ON blogs.date = threads.date AND threads.title LIKE 'Blog Entry for %'
LEFT JOIN posts ON threads.id = posts.threadId
GROUP BY blogs.date
ORDER BY blogs.date DESC
LIMIT $blogOffset, 10;
Before today, each blog entry had another SQL statement to determine the number of comments made, making eleven database calls in total. Now there's just one.
I understand that joining a blog and forum together in this manner isn't terribly robust. Using the date (accurate to only one second) and that the thread's title starts with "Blog Entry for", will have to do until I can come up with something better.
"Prepare for an oblivion for which there is no preparation." - O'Malley (Red Vs Blue)
