CODEWITHSUNDEEP
mysqlcategories
John Tangale
John Tangale

Reputation: 435

MySQL Query to list multi-category articles by a specific category, while still retrieving primary cat

I have a article system where articles that can be in different sections such as recipes, howto, editorial, news, etc.

Each of these articles can also belong to a single or multiple categories. For example, a recipe can be in the muffin category and the blueberry category.

Then when I generate the url, it would be in the format of: 

example.com/{post_section}/{primary_post_category_slug}/{post_title}/

When listing the articles, above the title I show each category that the article is part of. Each of these categories are a link that when clicked on show only the articles that are part of that category.

My problem is that when listing all the articles in a category, if an article has multiple categories, I can't figure out how to retrieve the primary one. For example, if I have an article that is a recipe for blueberry muffins I would put it in the primary category of muffins and a secondary category of blueberry.

Now if a visitor browses the blueberry category, it should list all articles related to blueberrys, but the link should still contain whatever the primary category slug is. My problem is retrieving the primary category when it is different than the category we are listing.

Below is the example query I am using and the table structure:

select p.*, c.*, t.* from posts p 
  left join post_category_selected a on a.post_id = p.post_id
  left join post_category c using (post_category_id)
  left join post_section t using (post_section_id)
    and c.post_category_slug = "blueberry"
    and t.post_section_slug = "recipes"
  order by p.post_publish_date desc

This does return all articles that have the blueberry category, but I can't figure out how to retrieve the primary_category of muffins so I can use it when I generate my link.

Hope this makes sense.

Table structure:

posts
 |- post_id
 |- post_title
 |- post_section
 |- post_slug
 |- post_publish_date

post_category
 |- post_category_id
 |- post_section_id
 |- post_category
 |- post_category_slug

post_category_selected
 |- post_category_selected_id
 |- post_id
 |- post_category_id
 |- is_primary (enum Y,N)

post_section
 |- post_section_id
 |- post_section_title
 |- post_section_slug

Upvotes: 3

Views: 118

Answers (1)

Alan Hadsell
Alan Hadsell

Reputation: 470

You need to join to post_category and post_category_selected twice each, once for the selected category and once for the primary category. Like this:

select p.*, c1.*, t.*, 
c2.post_category_slug as primary_category_slug 
from posts p 
  left join post_category_selected a1 on a1.post_id = p.post_id
  left join post_category c1 using (post_category_id)
  left join post_section t using (post_section_id)
    and c.post_category_slug = "blueberry"
      and t.post_section_slug = "recipes"
  left join post_category_selected a2 on a2.post_id = p.post_id
    and a2.is_primary = 'Y' 
  left join post_category c2 on c2.post_category_id = a2.post_category_id
  order by p.post_publish_date desc

You might need to add some additional columns from c2, depending on your other requirements.

Upvotes: 1

Related Questions