How would I get articles from a certain category and its children?

Question

I'm developing an article system that uses categories and child categories.

Basically, if the category has a parent_id value, it's a child of that category.

I would like to be able to get the most recent articles from a category and articles from its child categories.

For example: I have a category called "Gaming Articles" and several child categories under that called Xbox, PlayStation, Nintendo, and PC. My system makes it possible to post articles in the parent categories such as Gaming Articles as well as in the child categories.

So this would have to include articles that are in either the parent category or the child categories of that parent.

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,
  `date_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(1) NOT NULL,
  `attachment_id` int(15) 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,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

The query I have so far...

SELECT article_id, category_id
FROM articles
WHERE category_id = 1
ORDER BY article_id DESC
LIMIT 10

Of course, this only gets articles under that category, not from both the category and that category's child categories.

Answer 1

As your table structure stands, this query will work assuming only 1 level of nesting (ie children don't themselves have children):

SELECT a.*
FROM articles a
JOIN article_categories ac ON a.category_id = ac.category_id
WHERE 1 IN (a.category_id, ac.parent_id)
ORDER BY a.article_id DESC
LIMIT 10

Note the "reversed" style IN to neatly capture what is effectively an OR.

If your nesting is deeper, simply add another join for each level, for example if you have up to 4 levels (2 more than the above query):

SELECT a.*
FROM articles a
JOIN article_categories ac1 ON a.category_id = ac1.category_id
LEFT JOIN article_categories ac2 ON ac1.parent_id = ac2.category_id
LEFT JOIN article_categories ac3 ON ac2.parent_id = ac3.category_id
WHERE 1 IN (a.category_id, ac1.parent_id, ac2.parent_id, ac3.parent_id
ORDER BY a.article_id DESC
LIMIT 10

In the second case, the use of left joins is necessary to still return articles that don't have so many levels above.

Answer 2

In this structure is inposible in one query. (I assume that there are many levels of categories)

You can:

• recursively search child categories (ex. in php) and then

  SELECT * FROM articles WHERE category_id IN ( $categories );

• change db structure and use tree structure try: The Nested Set Model in article : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

• add new column to categories table, and store full path to category, ex.:

  category 1->2, category_path 1,2 category 1->2->3, category_path 1,2,3 category 1->4, new category_path 1,4

If You looking all data in category 1 and children, try:

SELECT 
    a.* 
FROM articles a
INNER JOIN categories c
    ON a.category_id = c.category_id
WHERE c.category_path LIKE '1,%'