Jump to content
ScottLacey

Fetch Categories and Get Their Last Article In Same Query

Recommended Posts

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!

 

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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 |
+------------+-------------+---------+

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.