0

I have articles, which belong to article_types, which belong to categories. Each article has a format.

I want to list all articles published by a particular author, grouped by category.

I want to show all categories, even the ones with no articles.

All articles have an article_type and all categories contain article types.

I have a query that works perfectly on MySQL 5.5.x but not on 5.0. Is this a MySQL bug, or should the query be changed or both?

SELECT 
    a.article_id, a.article_time, a.article_notes f.format_name, at.article_type_name, c.category_name, c.category_description 

FROM 
    article AS a 
    LEFT JOIN article_type AS at ON at.advice_id = a.advice_id 
    LEFT JOIN format AS f ON f.format_id = a.format_id 
    RIGHT JOIN category AS c ON c.category_id = at.cateory_id 

WHERE 
    (a.author_id = 5 AND a.published = 1) 
    OR (a.author_id IS NULL) 

ORDER BY 
    c.category_name ASC, a.article_time DESC

Desired Output (after simple php loop):

Category 1
  Article A (Article Type I) (Digital)
  Article B (Article Type I) (Print)
  Article E (Article Type II) (Digital)
Category 2
Category 3
  Article H (Article Type V) (Audio)
  Article M (Article Type IV) (Print)
afsk
  • 81
  • 4
  • I only get one of the empty categories. In 5.5 I get all the empty categories. Exactly the same data and query used. – afsk Aug 21 '12 at 11:57

1 Answers1

0

It was not possible to achieve a full list of categories with multiple authors due to this part of the WHERE clause:

OR (a.author_id IS NULL)

I have had to remove that and include a second query to retrieve the complete list of categories.

I then use PHP (or similar) to loop through each category and within that loop, loop through each article, creating a new array/object as I go that contains all categories and and articles for that particular author:

if(is_array($categories)) {
    if(is_array($articles)) {
        foreach($categories as $ck => $cv) {
            $category_set = false;
            foreach($article as $ak => $av) {
                if($av['category_name'] == $cv['category_name']) {
                    $articles_by_category[] = $article[$ak];
                    $category_set = true;
                    unset($article[$ak]);
                }
            }
            if($category_set == false) {
                $articles_by_category[] = $categories[$ck];
            }
        }
        $articles = $articles_by_category;
        unset($articles_by_category);
    } else {
        $articles = $categories;
    }
}

It would be interesting to see if anyone has a pure MySQL solution though.

afsk
  • 81
  • 4