CODEWITHSUNDEEP
mysql
Kakigari
Kakigari

Reputation: 7

Select all posts (and associated tags) that are tagged with a search

I want to select posts (and their tags) that are tagged with one or more search term.

Here are my tables:

Post

| ID | community | created             | updated             | title | content |
|----|-----------|---------------------|---------------------|-------|---------|
| 99 | 17        | 2019-08-14 14:20:38 | 2019-08-14 14:20:38 | Lorem | Ipsum   |
| 80 | 16        | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | Lorem | Ipsum   |
| 79 | 16        | 2019-07-27 23:09:47 | 2019-07-27 23:09:47 | Lorem | Ipsum   |

Community

| ID | title          |
|----|----------------|
| 16 | The Scary Door |
| 17 | Other          |

Community Tag Categories

| Community | Category |
|-----------|----------|
| 16        | 5        |
| 16        | 18       |
| 16        | 19       |
| 16        | 20       |
| 17        | 6        |

Tag Category

| category    | ID |
|-------------|----|
| character   | 5  |
| focus       | 18 |
| warning     | 19 |
| time-period | 20 |
| NULL        | 6  |

Tag Categorised

| category | tag |
|----------|-----|
| 5        | 146 |
| 6        | 131 |
| 6        | 147 |
| 19       | 147 |
| 20       | 148 |

Tag

| name          | id  |
|---------------|-----|
| spider        | 146 |
| arachnophobia | 147 |
| Victorian era | 148 |
| NULL          | 131 |

Post Tags

| post | tag |
|------|-----|
| 99   | 147 |
| 80   | 146 |
| 80   | 147 |
| 80   | 148 |
| 79   | 131 |

I'm already using this query to get information on all posts and their associated tags and categories:

SELECT p.id, c.id as 'commid', c.title AS 'community', p.title, p.content, p.author, p.created, p.updated, ct.category, t.name AS 'tag'
FROM Post p, Community c, CommunityTagCategories cc, TagCategory ct, TagCategorised tc, Tag t, PostTags pt
WHERE p.community=c.id AND c.id=cc.community AND cc.category=ct.id AND ct.id=tc.category AND tc.tag=t.id AND t.id=pt.tag AND pt.post=p.id
ORDER BY p.created DESC;

If I search for a tag like 'arachnophobia' I would like the results to be narrowed down like this: (and if I wanted to search for 'arachnophobia' AND 'spider' it should only display the information for post 80)

| ID | commid | community      | title | content | author | created             | updated             | category    | tag           |
|----|--------|----------------|-------|---------|--------|---------------------|---------------------|-------------|---------------|
| 99 | 17     | Other          | Lorem | Lorem   | 7      | 2019-08-14 14:20:38 | 2019-08-14 14:20:38 | NULL        | arachnophobia |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | character   | spider        |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | warning     | arachnophobia |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | time-period | Victorian era |

From searching similar questions it seems like I might need to use a nested SELECT and INNER JOINS. I tried doing that like this:

SELECT p2.id, c.id as 'commid', c.title AS 'community', p2.author, p2.created, p2.updated, p2.title, p2.content, tc.category, t.name AS 'tag' 
FROM 
    (SELECT p.id
    FROM Post p, Tag t, PostTags pt
    WHERE p.id=pt.post AND pt.tag=t.id AND t.name ="arachnophobia"
    ) search
INNER JOIN Post p2
ON (search.id = p2.id)
INNER JOIN Community c
ON p2.community=c.id
INNER JOIN CommunityTagCategories cc
ON c.id=cc.community
INNER JOIN TagCategory tc
ON cc.category=tc.id
INNER JOIN TagCategorised ct
ON tc.id=ct.category
INNER JOIN Tag t
ON ct.tag=t.id
INNER JOIN PostTags pt
ON t.id=pt.tag
INNER JOIN Post p3
ON pt.post=p3.id
ORDER BY p2.created DESC;

It does return only results for posts 99 and 80 but it gives me results for every single tag and category that exists in their associated communities, even when those posts are not associated with those tags and categories. So here it returns an extra row for post 80 with the tag from post 79 which are both in the same community.

| ID | commid | community      | title | content | author | created             | updated             | category    | tag           |
|----|--------|----------------|-------|---------|--------|---------------------|---------------------|-------------|---------------|
| 99 | 17     | Other          | Lorem | Lorem   | 7      | 2019-08-14 14:20:38 | 2019-08-14 14:20:38 | NULL        | arachnophobia |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | character   | spider        |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | warning     | arachnophobia |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | time-period | Victorian era |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | character   | NULL          |

I asked a similar question about displaying all post data a few days ago here and user nbk helped out with a different query that also works, but it's a little too complicated for me to know how to alter myself.

Upvotes: 0

Views: 41

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

For avoid duplicated rows you could use DISTINCT:

SELECT DISTINCT p2.id
  , c.id as commid
  , c.title AS community
  , p2.author
  , p2.created
  , p2.updated
  , p2.title
  , p2.content
  , tc.category
  , t.name AS tag 
FROM  (
   SELECT p.id
   FROM Post p
   INNER JOIN PostTags pt ON p.id=pt.post
   INNER JOIN Tag t ON  pt.tag=t.id AND t.name ="arachnophobia"
) search
INNER JOIN Post p2 ON search.id = p2.id
INNER JOIN Community c ON p2.community=c.id
INNER JOIN CommunityTagCategories cc ON c.id=cc.community
INNER JOIN TagCategory tc ON cc.category=tc.id
INNER JOIN TagCategorised ct ON tc.id=ct.category
INNER JOIN Tag t ON ct.tag=t.id
INNER JOIN PostTags pt ON t.id=pt.tag
INNER JOIN Post p3 ON pt.post=p3.id
ORDER BY p2.created DESC;

Just some suggestions.

You should not use single quote around column name (this if for literal text) use backticks when needed.

Should not use old implicit join syntax based table name comma separated and where condition (you have in your subquery).

Avoid unnecessary () around join condition.

And looking to your relation: you should avoid the last part of join for tag you have already obtained by search.

SELECT DISTINCT p2.id
  , c.id as commid
  , c.title AS community
  , p2.author
  , p2.created
  , p2.updated
  , p2.title
  , p2.content
  , tc.category
  , t.name AS tag 
FROM  (
    SELECT p.id
    FROM Post p
    INNER JOIN Tag t ON p.id=pt.post AND t.name ="arachnophobia"
    INNER JOIN PostTags pt ON pt.tag=t.id 
) search
INNER JOIN Post p2 ON search.id = p2.id
INNER JOIN Community c ON p2.community=c.id
INNER JOIN CommunityTagCategories cc ON c.id=cc.community
INNER JOIN TagCategory tc ON cc.category=tc.id
INNER JOIN TagCategorised ct ON tc.id=ct.category
ORDER BY p2.created DESC;

Upvotes: 1

Related Questions