Drupal: MySQL SELECT all posts belonging to a specific forum

Question

I need to select all the messages (first posts and replies) posted in an array of specific categories (forums) in Drupal.

First posts are stored in field_data_body, replies are stored in field_data_comment_body.

The structure of field_data_body and field_data_comment_body is the same, in the column body_value there's the content of the posts and in the column entity_id their unique ID.

The table field_data_taxonomy_forums contains the entity_id column and the taxonomy_forums_tid column (which are the IDs of the forum categories). The table taxonomy_term_data contains the columns tid (which is the same of taxonomy_forums_tid and the description column (which is the title of the forum category).

So, I'm looking for a query that allows me to select the body of the posts (both first posts and replies) and the description of the forum specifying an array of tids (i.e. the IDs of the forum categories), that I'll manually find in the taxonomy_term_data table.

So, for example I'm looking for the query that allows me to SELECT the posts "belonging" to tids 1456,7622,862 and the relative tid description.

Here's a screenshot of the field_data_body table :

Answer 1

I think, it would be better for us to split the task into 2 subtasks:

1. Find the bodies of the posts, belonging to a particular tid.
2. Find all the comments of the body, belonging to a particular tid.

We'll need to use these tables:

1. field_data_taxonomy_forums
2. field_data_body
3. comment
4. field_data_comment_body

Database structure:

Finding the bodies

SELECT
    taxonomy_forums.taxonomy_forums_tid AS tid,
    body.entity_id,
    body.body_value AS body
FROM
    field_data_taxonomy_forums AS taxonomy_forums
INNER JOIN
    field_data_body AS body
ON
    body.entity_id=taxonomy_forums.entity_id
WHERE
    taxonomy_forums.taxonomy_forums_tid IN (9);

Finding the comments for the bodies

Here we'll need comments table, that unites field_data_body and field_data_comment_body.

SELECT
    taxonomy_forums.taxonomy_forums_tid AS tid,
    comment_body.entity_id,
    comment_body.comment_body_value AS body
FROM
    field_data_taxonomy_forums AS taxonomy_forums
INNER JOIN
    field_data_body AS body
ON
    body.entity_id=taxonomy_forums.entity_id
INNER JOIN
    comment
ON
    comment.nid=body.entity_id
INNER JOIN
    field_data_comment_body AS comment_body
ON
    comment_body.entity_id=comment.cid
WHERE
    taxonomy_forums.taxonomy_forums_tid IN (9);

If you UNION these 2 queries, you'll get the list of posts and comments.

sqlfiddle

Answer 2

To find reference to forum id you must add use of "forum" table (table establishing relationship of nodes to forum terms.) Also it should be noted that forum id does not exist, it is a taxonomy id. Here is an example to get only first message topic by taxonomy id(tid)

SELECT * FROM field_data_body fdb
LEFT JOIN forum f ON f.nid = fdb.entity_id
WHERE fdb.bundle="forum" AND f.tid=15

Note that the forum module works with comment core module wich means that only first topic message is stored in "field_data_body" and all replies are stored in "field_data_comment_body" table.

Hope it helps