Fetch Categories and Get Their Last Article In Same Query

[ScottLacey]

I am developing a CMS with articles and categories.  I am making a feature for the admin panel that shows all the categories.  In the table, I would like to include each category's latest article.  My traditional method is to select just the categories in one query, loop through the results, and then inside the category loop have a query that gets the information on the latest article... So something like...

$query = "
     SELECT category_id, title, description
     FROM categories
     ORDER BY title ASC";
 
if($query = mysql_query($query)){
 
     while($cat=mysql_fetch_assoc($query)){
 
            $query_article = "
                 SELECT article_id, category_id, title
                 FROM articles
                 WHERE category_id = ".$cat['category_id']."
                 ORDER BY article_id DESC
                 LIMIT 1";
 
            $last = mysql_fetch_assoc($query_article);
 
       }
 
}

          How could I use joins and/or subqueries to achieve my goal of combining these two queries into one?  I have come up with a query which is below:

 

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

 

When I use the above query, I get an error saying "Operand should contain 1 column(s)"

 

I would also like to add something that counts the total number of articles in the category.

 

Thanks for your time!

 

[Barand]

If you need "latest" records then you should be using timestamps rather relying on id sequences, however, given your data, your solution is to use table subqueries to determine the latest id and join on that latest (MAX) id to get the matching article. You can pick up the count of articles at the same time

SELECT c.category_id
, c.title as cat_title
, c.description
, a.article_id
, a.title as article_title
, tot
FROM categories c
    LEFT JOIN 
        (
        SELECT t.category_id
        , t.article_id
        , title
        , t.tot
        FROM articles
            JOIN 
                (
                SELECT category_id
                , MAX(article_id) as article_id
                , COUNT(article_id) as tot
                FROM articles
                GROUP BY category_id
                ) t USING (category_id, article_id)
        ) a USING (category_id)
ORDER BY c.title ASC;

[ScottLacey]

Is there a way that I could also fetch the title of the last article in this query?  

[Barand]

It did when it left the factory.

 

RESULTS

+-------------+-----------+-------------+------------+---------------+------+
| category_id | cat_title | description | article_id | article_title | tot  |
+-------------+-----------+-------------+------------+---------------+------+
|           1 | Cat 1     | Desc 1      |          3 | Art 1 3       |    3 |
|           2 | Cat 2     | Desc 2      |          5 | Art 2 2       |    2 |
|           3 | Cat 3     | Desc 3      |          9 | Art 3 4       |    4 |
|           4 | Cat 4     | Desc 4      |       NULL | NULL          | NULL |
+-------------+-----------+-------------+------------+---------------+------+

My test data

mysql> SELECT * FROM categories;
+-------------+-------+-------------+
| category_id | title | description |
+-------------+-------+-------------+
|           1 | Cat 1 | Desc 1      |
|           2 | Cat 2 | Desc 2      |
|           3 | Cat 3 | Desc 3      |
|           4 | Cat 4 | Desc 4      |
+-------------+-------+-------------+

mysql> SELECT * FROM articles;
+------------+-------------+---------+
| article_id | category_id | title   |
+------------+-------------+---------+
|          1 |           1 | Art 1 1 |
|          2 |           1 | Art 1 2 |
|          3 |           1 | Art 1 3 |
|          4 |           2 | Art 2 1 |
|          5 |           2 | Art 2 2 |
|          6 |           3 | Art 3 1 |
|          7 |           3 | Art 3 2 |
|          8 |           3 | Art 3 3 |
|          9 |           3 | Art 3 4 |
+------------+-------------+---------+