0

I have a course website where I am trying to get forum titles and topic ids where the forum is related to a course content type.

The course forum is organized as

Parent Forum
   Announcements
   Required Discussions
   Student Discussions

The actual topics are all in the subforums - so I know to never search in the parent forum.

In the database I have, for instance, a posts table

ID post_title           post_type   post_parent
1  Some Course          course      0
2  ParentForum          forum       0
3  Assignments          forum       2
4  Required Discussions forum       2
5  Student  Discussions forum       2
6  Some Topic1          topic       3
7  Some Topic2          topic       4
8  Some Topic3          topic       5

Then there is meta data as postmeta table that stores relation between a course and its forums.

meta_id post_id meta_key          meta_value
1       1       related_forum_2   2

For a given course ID, I want all topics (titles and url) and their forum titles with id's, so a result of the form

topic_id topic_title  forum_title          forum_id
3        SomeTopic1   Assignments             6
4        SomeTopic2   Required Discussions    7
5        SomeTopic3   Student  Discussions    8

Been reading about INNER JOIN for this sort of case, but the examples I see seem to assume that content types have their own tables, and I can't find any examples that are similar to what I am trying to do. In this case I am dealing with a post table where course, forum, and topic ids are stored (and titles), as well as a meta table where data is stored for all content types, as well as some relational data.

Does this even lend itself to a single query? How would you form such a query?

thanks

Brian
  • 561
  • 5
  • 16
  • Actually, key-value tables like that are very common, not considered a crazy way at all. It allows storing arbitrary data in a general-purpose table. – Barmar Nov 20 '19 at 22:06
  • After a lot of trial and error, I figured out a query that works (this assumes a given course id): "SELECT t.ID, t.post_title, f.post_title, pm.meta_value FROM wp_postmeta AS pm INNER JOIN wp_posts AS f ON f.ID = pm.meta_value INNER JOIN wp_posts AS t ON t.post_parent = f.ID WHERE pm.meta_key LIKE 'related_forum%' AND pm.post_id = 1 AND f.post_parent != 0 AND t.post_type='topic'" – Brian Nov 21 '19 at 11:13

0 Answers0