how do i display all the tags related to all the feedbacks in one query

Question

Hi,

I am trying to write a sql query which fetches all the tags related to every topic being displayed on the page.

like this

TITLE: feedback1
POSTED BY: User1
CATEGORY: category1
TAGS: tag1, tag2, tag3

TITLE: feedback2
POSTED BY: User2
CATEGORY: category2
TAGS: tag2, tag5, tag7,tag8

TITLE: feedback3
POSTED BY: User3
CATEGORY: category3
TAGS: tag1, tag5, tag6, tag3

The relationship of tags to topics is many to many.

Right now I am first fetching all the topics from the "topics" table and to fetch the related tags of every topic I loop over the returned topics array for fetching tags. But this method is very expensive in terms of speed and not efficient too.

Please help me write this sql query.

Query for fetching all the topics and its information is as follows:

SELECT
    tbl_feedbacks.pk_feedbackid as feedbackId,
    tbl_feedbacks.type as feedbackType,
    DATE_FORMAT(tbl_feedbacks.createdon,'%M %D, %Y') as postedOn,
    tbl_feedbacks.description as description,
    tbl_feedbacks.upvotecount as upvotecount,
    tbl_feedbacks.downvotecount as downvotecount,
    (tbl_feedbacks.upvotecount)-(tbl_feedbacks.downvotecount) as totalvotecount,
    tbl_feedbacks.viewcount as viewcount,
    tbl_feedbacks.title as feedbackTitle,
    tbl_users.email as userEmail,
    tbl_users.name as postedBy,
    tbl_categories.pk_categoryid as categoryId,
    tbl_clients.pk_clientid as clientId
FROM
    tbl_feedbacks
LEFT JOIN tbl_users
    ON ( tbl_users.pk_userid = tbl_feedbacks.fk_tbl_users_userid )
LEFT JOIN tbl_categories
    ON ( tbl_categories.pk_categoryid = tbl_feedbacks.fk_tbl_categories_categoryid )
LEFT JOIN tbl_clients
    ON ( tbl_clients.pk_clientid = tbl_feedbacks.fk_tbl_clients_clientid )
WHERE
    tbl_clients.pk_clientid = '1'

What is the best practice that should be followed in such cases when you need to display all the tags related to every topic being displayed on a single page.

How do I alter the above sql query, so that all the tags plus related information of topics is fetched using a single query.

For a demo of what I am trying to achieve is similar to the'questions' page of stackoverflow. All the information (tags + information of every topic being displayed) is properly displayed.

Thanks

Answer 1

To do this, I would have three tables:

Topics
  topic_id
  [whatever else you need to know for a topic]
Tags
  tag_id
  [etc]
Map
  topic_id
  tag_id

select t.[whatever], tag.[whatever]
  from topics t
  join map m on t.topic_id = m.topic_id
  join tags tag on tag.tag_id = m.tag_id
  where [conditionals]

Set up partitions and/or indexes on the map table to maximize the speed of your query. For example, if you have many more topics than tags, partition the table on topics. Then, each time you grab all the tags for a topic, it will be 1 read from 1 area, no seeking needed. Make sure to have both topics and tags indexed on their _id.

Use your 'explain plan' tool. (I am not familiar with mysql, but I assume there is some tool that can tell you how a query will be run, so you can optimize it)

EDIT:

So you have the following tables:

tbl_feedbacks
tbl_users
tbl_categories
tbl_clients
tbl_tags
tbl_topics
tbl_topics_tags

The query you provide as a starting point shows how feedback, users, categories and clients relate to each other.

I assume that tbl_topics_tags contains FKs to tags and topics, showing which topic has which tag. Is this correct?

What of (feedbacks, users, categories, and clients) has a FK to topics or tags? Or, do either topics or tags have a FK to any of the initial 4?

Once I know this, I'll be able to show how to modify the query.

EDIT #2

There are two different ways to go about this:

The easy way is the just join on your FK. This will give you one row for each tag. It is much easier and more flexible to put together the SQL to do it this way. If you are using some other language to take the results of the query and translate them to present them to the user, this method is better. If nothing else, it will be far more obvious what is going on, and will be easier to debug and maintain.

However, you may want each row of the query results to contain one feedback (and the tags that go with it).

http://stackoverflow.com/questions/1651608/sql-joining-question <- this is a question I posted on how to do this. The answer I accepted is an oracle-only answer AFAIK, but there are other non-oracle answers.

Adapting Kevin's answer (which is supposed to work in SQL92 compliant systems):

select 
[other stuff: same as in your post],
 (select tag
  from tbl_tag tt
  join tbl_feedbacks_tags tft on tft.tag_id = tt.tag_id
  where tft.fk_feedbackid = tbl_feedbacks.pk_feedbackid
  order by tag_id
  limit 1
  offset 0 ) as tag1,
 (select tag
  from tbl_tag tt
  join tbl_feedbacks_tags tft on tft.tag_id = tt.tag_id
  where tft.fk_feedbackid = tbl_feedbacks.pk_feedbackid
  order by tag_id
  limit 1
  offset 1 ) as tag2,
 (select tag
  from tbl_tag tt
  join tbl_feedbacks_tags tft on tft.tag_id = tt.tag_id
  where tft.fk_feedbackid = tbl_feedbacks.pk_feedbackid
  order by tag_id
  limit 1
  offset 2 ) as tag3
from [same as in the OP]

This should do the trick.

Notes:

This will pull the first three tags. AFAIK, there isn't a way to have an arbitrary number of tags. You can expand the number of tags shown by copying and pasting more of those parts of the query. Make sure to increase the offset setting.

If this does not work, you'll probably have to write up another question, focusing on how to do the pivot in mysql. I've never used mysql, so I'm only guessing that this will work based on what others have told me.

One tip: you'll usually get more attention to your question if you strip away all the extra details. In the question I linked to above, I was really joining between 4 or 5 different tables, with many different fields. But I stripped it down to just the part I didn't know (how to get oracle to aggregate my results into one row). I know some stuff, but you can usually do far better than just one person if you trim your question down to the essentials.