MySQL, need help defining multiple join query to return complete info

Question

So, I have modified a query I learned from this thread, however when I filter between tags and cats, the result is undesirable. Filtering for category 5 will return just the category list info and tags will be empty, while the opposite applies for tags.

    SELECT posts.id,time,title,
        GROUP_CONCAT(IFNULL(cats.id, '') ORDER BY cats.id DESC SEPARATOR '~') as catIdList,
        GROUP_CONCAT(IFNULL(cats.name, '') ORDER BY cats.id DESC SEPARATOR '~') as catNameList,
        GROUP_CONCAT(IFNULL(cats.slug, '') ORDER BY cats.id DESC SEPARATOR '~') as catSlugList,
        GROUP_CONCAT(IFNULL(cats.value, '') ORDER BY cats.id DESC SEPARATOR '~') as catValueList,
        GROUP_CONCAT(IFNULL(tags.id, '') ORDER BY tags.id DESC SEPARATOR '~') as tagIdList,
        GROUP_CONCAT(IFNULL(tags.name, '') ORDER BY tags.id DESC SEPARATOR '~') as tagNameList,
        GROUP_CONCAT(IFNULL(tags.slug, '') ORDER BY tags.id DESC SEPARATOR '~') as tagSlugList,
        GROUP_CONCAT(IFNULL(tags.value, '') ORDER BY tags.id DESC SEPARATOR '~') as tagValueList
    FROM posts
    LEFT JOIN termRelations ON ( posts.id = termRelations.postId )
    LEFT JOIN cats ON ( termRelations.termId = cats.id AND termRelations.termTypeId = 1 )
    LEFT JOIN tags ON ( termRelations.termId = tags.id AND termRelations.termTypeId = 0 )
    WHERE ( ( IFNULL(tags.id, '') = '4' ) )
    GROUP BY posts.id ORDER BY time DESC

The IFNULL() is there to work around non-existant entries. This query above will return:

    (
        [id] => 15
        [time] => 0
        [title] => post 15
        [catIdList] => 
        [catNameList] => 
        [catSlugList] => 
        [catValueList] => 
        [tagIdList] => 4
        [tagNameList] => tagname
        [tagSlugList] => tagname
        [tagValueList] => 
    )

    (
        [id] => 16
        [time] => 0
        [title] => post 16
        [catIdList] => 
        [catNameList] => 
        [catSlugList] => 
        [catValueList] => 
        [tagIdList] => 4
        [tagNameList] => tagname
        [tagSlugList] => tagname
        [tagValueList] => 
    )

While without WHERE ( ( IFNULL(tags.id, '') = '4' ) ) the result would be (Along with all other posts due to it not being filtered to this tag, of course):

    (
        [id] => 15
        [time] => 0
        [title] => post 15
        [catIdList] => 
        [catNameList] => 
        [catSlugList] => 
        [catValueList] => 
        [tagIdList] => 4
        [tagNameList] => tagname
        [tagSlugList] => tagname
        [tagValueList] => 
    )

    (
        [id] => 16
        [time] => 0
        [title] => post 16
        [catIdList] => 5~~
        [catNameList] => Movies~~
        [catSlugList] => movies~~
        [catValueList] => ~~
        [tagIdList] => 4~1~
        [tagNameList] => tagname~sand~
        [tagSlugList] => tagname~sand~
        [tagValueList] => ~~
    )

Which is of course what I want - all relevant info!

The tables are:

1. termRelations contains the post ID and term ID, with termTypeId distinguishing between the cats and tags tables.
2. cats contains the term ID and category info (Name, slug, parentId, etc.)
3. tags contains the term ID and tag info (Name, slug, etc.)
4. posts contains post info (title, time, body etc.)

The purpose of termRelations is to bind tags and categories to the posts. The purpose of this query is to return filtered results (I want users to be able to view posts with a specific tag, and also a specific category.) while still retaining complete information.

Is it possible that this would be solved by combining the cats and tags tables into terms?

I wish I knew how, but at this point I'm pretty much hitting a mental wall on this. So, little help :) ? Thanks!!

Answer 1

Change the WHERE to an EXIST with a subquery:

SELECT posts.id,time,title,
    GROUP_CONCAT(IFNULL(cats.id, '') ORDER BY cats.id DESC SEPARATOR '~')
      AS catIdList,
    GROUP_CONCAT(IFNULL(cats.name, '') ORDER BY cats.id DESC SEPARATOR '~') 
      AS catNameList,
    GROUP_CONCAT(IFNULL(cats.slug, '') ORDER BY cats.id DESC SEPARATOR '~') 
      AS catSlugList,
    GROUP_CONCAT(IFNULL(cats.value, '') ORDER BY cats.id DESC SEPARATOR '~') 
      AS catValueList,
    GROUP_CONCAT(IFNULL(tags.id, '') ORDER BY tags.id DESC SEPARATOR '~') 
      AS tagIdList,
    GROUP_CONCAT(IFNULL(tags.name, '') ORDER BY tags.id DESC SEPARATOR '~') 
      AS tagNameList,
    GROUP_CONCAT(IFNULL(tags.slug, '') ORDER BY tags.id DESC SEPARATOR '~') 
      AS tagSlugList,
    GROUP_CONCAT(IFNULL(tags.value, '') ORDER BY tags.id DESC SEPARATOR '~') 
      AS tagValueList
FROM posts
LEFT JOIN termRelations ON ( posts.id = termRelations.postId )
LEFT JOIN cats 
  ON ( termRelations.termId = cats.id AND termRelations.termTypeId = 1 )
LEFT JOIN tags 
  ON ( termRelations.termId = tags.id AND termRelations.termTypeId = 0 )
WHERE EXISTS
      ( SELECT *
        FROM termRelations 
        WHERE termRelations.termId = '4'
          AND termRelations.termTypeId = 0
          AND posts.id = termRelations.postId
      )
GROUP BY posts.id 
ORDER BY time DESC