Setting up taxonomy to relate various custom post types - advice on structuring?

admin管理员组

文章数量:1405739

I have been using post meta to relate various custom posts types and recently realized, based on various discussions here with wiser developers, that taxonomies are probably the way to go.

My site is organized around post type course. Perhaps of importance, I will not have many courses. Maybe 4-8. (Also of note - I don't want taxonomies becoming routable - I have course, lesson and topic templates, and routes to those pages are what visitors see, just that I need the functionality of taxonomies to tie everything together behind the scenes so in various views I can extract relational data.)

Under a given course, I have

Course 1
   Lesson 1.1
      Topic 1.1.1
         Quiz 1.1.1.1
         Quiz 1.1.1.2
         Forum Topic 1.1.1.1
      Topic 1.1.2
         Quiz 1.1.2.1
         Exercise 1.1.2.1
      etc

Course 2
   Lesson 2.1
      Topic 2.1.1
         Quiz 2.1.1.1
         Forum Topic 2.1.1.1
      Topic 2.1.2
         etc
      etc

I also use a course forum (BBPress, which has its own taxonomy system based on post_parent), and at times need to tie individual topics to lesson topics.

The types of queries I need to do (beyond the obvious, such as get me all courses...)

1. Get me all lessons for a course
2. Get me all topics for a lesson
3. Get me all quizzes for a topic
4. Get me all quizzes for a lesson (which means quizzes under topics for the lesson)
5. Get me all quizzes for a course (which means all quizzes across all topics in the course)
6. Same above like quizzes, but for forum topics

I am not looking for someone to solve this for me. I just need a jumpstart. If I see one example then I can extend for all my requirements. I see there are 4 taxonomy related tables, and just not sure where to start.

For instance, would this make sense for taxonomy and terms?

term_taxonomy
term_tax_id   term_id   taxonomy              description        parent
1             1         course                Course
2             2         lesson                Lesson               1
3             3         topic                 Topic                2
4             4         topic_content_type    Topic Content        3               
5             5         topic_content_type    Topic Content        3                              

terms
term_id   name    
1         Course
2         Lesson
3         Topic
4         Quiz
5         Discussion Topic 

Note: For a forum topic, it will have taxonomy to tie it to a forum (BBPress dopes that automatically), as well as lesson topic.

Then if I had

posts
ID    title                  type
1     Course 1               course
2     Lesson 1.1             lesson
3     Lesson 1.2             lesson
4     Topic 1.1.1            topic
5     Quiz 1.1.1             quiz
6     Quiz 1.1.2             quiz
7     Some Forum Topic 1.1.1 forum-topic
8     Topic 1.1.2            topic

I would relate as

term_relationships
object_id   term_tax_id
1           1
2           2
3           2
4           3
5           4
6           4
7           5
8           3

I am really confused about how to set this up so everything is logical and consistent. I am also confused as to whether a course instance itself would be a taxonomy, or if the taxonomy and terms are just generalizations and then I attach course instances (and lessons instances, topics, etc) to these taxonomies.

As far as querying, I want to think in terms of pure queries before I use wordpress's tax_query.

So if I want to extract a topic's content (like topic above with id 4), would it be something like

SELECT posts.ID FROM wp_posts AS posts
INNER JOIN term_relationships 
   AS termrel ON termrel.object_id = posts.ID
INNER JOIN term_taxonomy
   AS tax     ON tax.term_tax_id   = termrel.term_tax_id
INNER JOIN terms
   AS terms   ON terms.term_id     = term_taxonomy.term_id
WHERE tax.taxonomy =  topic_content_type AND posts.ID = 4

I must be missing a JOIN or two, since I haven't used the parent id yet anywhere.

And extracting a topic's course id would be something like, ok, well, I am not even sure how to build that query, but I want to sanity check that this structure enables it. Maybe that is where post meta comes in where I save a course_id for a topic. But at same time, a topic's course id is relational data. And when I go down this line of thinking, I get right back to post meta which is how I am currently forming these relationships...

Could use some advice going forward... Brain is fried...

Thanks, Brian

Edit:

Ok, so I set up an sqlfiddle for this.

!9/4f9a5e/9

These queries really seem to start getting out of hand quickly. For instance, if I want all lessons for a certain course it seems to amount to

SELECT course_rels.object_id as course_id, lesson.post_title as lesson_title FROM posts AS lesson
INNER JOIN wp_term_relationships AS lesson_rels ON lesson.ID = lesson_rels.object_id
INNER JOIN wp_term_relationships AS course_rels ON course_rels.object_id = 1
INNER JOIN wp_term_taxonomy AS lesson_tax 
   ON 
       (lesson_tax.term_taxonomy_id = lesson_rels.term_taxonomy_id
       AND
       lesson_tax.parent = course_rels.term_taxonomy_id)
INNER JOIN wp_terms AS terms ON terms.term_id = lesson_tax.term_id
WHERE terms.name = 'Lesson';

I am sure the WordPress query methods make all this transparent, but I think using WP_Query would still involve various preparatory function calls to produce final result (or some pre query filter). So the question is: Is there a better way to go about organizing relationships for a customized app, where it isn't so important to rely on WordPress methods? I mean, in my case, it is the relations among my courses, lessons, and topics that is of utmost importance. So maybe I just create a single table that contains all my content relationships? Like

Relations
---------------------------------------------------------------
id   post_id   related_course   related_lesson    related_topic

Then in case of my sqlfiddle example, that would be

Relations
---------------------------------------------------------------
id   post_id   related_course related_lesson related_topic post_type
1    1         0                0                 0        course
2    2         1                0                 0        lesson
3    3         1                2                 0        lesson-topic
4    4         1                2                 3        quiz
5    5         1                2                 3        quiz
6    6         1                2                 3        quiz
7    7         1                2                 0        lesson-topic
8    8         1                0                 0        forum
9    9         1                0                 0        forum
10  10         1                2                 3        topic
11  11         1                2                 0        lesson-topic
12  12         1                2                 11       quiz
13  13         1                0                 0        lesson
14  14         1                13                0        lesson-topic
15  15         1                13                14       topic 

Granted, above doesn't handle the BBPress forum categorization, but I could factor that in I suppose. Maybe include a parent_id column, or related_forum.

Maybe I will update the fiddle to include this approach. But for now, really hoping to get a dialogue going here. I suspect there are a lot of people wondering the same thing, and wishing to break free from WordPress's built in provisions for relationships and create a custom approach. I think it just starts getting crazy relying on tables shared by a bunch of plugins. Why do that if we want to deal with out custom content first and foremost?

Thoughts?

本文标签： Setting up taxonomy to relate various custom post typesadvice on structuring