How would I avoid using two separate queries for this purpose?

Question

I am using MySQL to create a database of articles and categories. Each article has a category. I would like to make a feature for the admin panel that lists all the categories, but also includes the latest article for each category. The method I usually use is to fetch rows from the category table, loop through the results, and then create another query using something like FROM articlesWHERE category_id = {CATEGORY_ID} ORDER BY article_id DESC LIMIT 1. That method just seems like overkill to me and I am wondering if it can be done in one query(Maybe with joins and subqueries?).

This is the current query I have that fetches categories:

SELECT * FROM article_categories ORDER BY category_title ASC

These are my tables:

CREATE TABLE IF NOT EXISTS `articles` (
  `article_id` int(15) NOT NULL AUTO_INCREMENT,
  `author_id` int(15) NOT NULL,
  `category_id` int(15) NOT NULL,
  `modification_id` int(15) NOT NULL,
  `title` varchar(125) NOT NULL,
  `content` text NOT NULL,
  `type` tinyint(1) NOT NULL,
  `date_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(1) NOT NULL,
  `attachment_id` int(15) NOT NULL,
  `youtube_id` varchar(32) DEFAULT NULL,
  `refs` text NOT NULL,
  `platforms` varchar(6) NOT NULL,
  PRIMARY KEY (`article_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;       

CREATE TABLE IF NOT EXISTS `article_categories` (
  `category_id` int(15) NOT NULL AUTO_INCREMENT,
  `parent_id` int(15) NOT NULL,
  `title` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `attachment_id` text NOT NULL,
  `enable_comments` tinyint(1) NOT NULL,
  `enable_ratings` tinyint(1) NOT NULL,
  `guest_reading` tinyint(1) NOT NULL,
  `platform_assoc` tinyint(1) NOT NULL,
  `allowed_types` varchar(6) NOT NULL,
  PRIMARY KEY (`category_id`,`title`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

This is the query I have come up with so far:

            SELECT
                c.category_id, c.title, c.description,
                a.article_id, a.category_id, a.title, COUNT(a.article_id) AS total_articles
            FROM article_categories AS c
            LEFT JOIN articles AS l ON (
                SELECT
                    article_id AS article_id, category_id, title AS article_title
                FROM articles AS l
                WHERE l.category_id = c.category_id
                ORDER BY l.article_id
                DESC LIMIT 1)
            LEFT JOIN articles AS a ON (c.category_id = a.category_id)
            GROUP BY c.category_id
            ORDER BY c.title ASC

The above query gives me the following SQL error:

Operand should contain 1 column(s)

Why is this happening?

Answer 1

You can return list of all the categories and recent article in each category using one query, Try this

SELECT C.*, A.*
FROM article_categories C
LEFT OUTER JOIN articles A ON c.category_id = A.category_id
WHERE 
(
 A.category_id IS NULL OR 
 A.article_id = (SELECT MAX(X.article_id)
                 FROM articles X WHERE X.category_id = C.category_id)
)

Answer 2

This will restrict the articles to just the highest article_id per category and make use of the indexes on those tables:

select
      ac.category_id, ac.title, newa.article_id, newa.title article_title
from article_categories ac
left join articles newa on ac.category_id = newa.category_id
left join articles olda on newa.category_id = olda.category_id
                    and olda.article_id > newa.article_id
where olda.article_id is null
;

See this Demonstrated at SQLFiddle

Answer 3

Shoelace, I was browsing your other questions and saw that this was unresolved so I've decided to take a crack at it.

This is a little tricky, but I don't think it's too bad, assuming I understand your question correctly. First, get the latest article date for each category:

SELECT a.category_id, MAX(a.date_posted)
FROM articles a
JOIN article_categories c ON c.category_id = a.category_id
GROUP BY a.category_id;

Then, join that with your articles table on the condition that the category_id and date are equal and you have what you need:

SELECT ar.*
FROM articles ar
JOIN(SELECT a.category_id, MAX(a.date_posted) AS latestDateForCategory
    FROM articles a
    JOIN article_categories c ON c.category_id = a.category_id
    GROUP BY a.category_id) t
ON t.category_id = ar.category_id AND t.latestDateForCategory = ar.date_posted;

SQL Fiddle.