Geek asks and answers

How to limit tags between 3 tables

I am working on a tag system, what it should do is you can get the request and the selected tags, for example. jqueryand tags javascript, library. It should show only related scripts with the request AND only those that have tags. This is the database layout:

Script Table:

+-----------+---------------+
| script_id | name          |
+-----------+---------------+
|         1 | jQuery        |
|         2 | Sencha Touch  |
|         3 | Codeigniter   |
|         4 | Google Chrome |
|         5 | memcached     |
|         6 | PHP           |
|         7 | MooTools      |
|         8 | node.js       |
|         9 | jQuery Mobile |
+-----------+---------------+

Tag Table:

+--------+-------------+-------------+
| tag_id | name        | url_name    |
+--------+-------------+-------------+
|      1 | JavaScript  | javascript  |
|      2 | Library     | library     |
|      3 | PHP         | php         |
|      4 | MySQL       | mysql       |
|      5 | Cache       | cache       |
|      6 | HTML        | html        |
|      7 | Open source | open-source |
+--------+-------------+-------------+

Table with tags:

+-----------+--------+-----------+
| tagged_id | tag_id | script_id |
+-----------+--------+-----------+
|         1 |      1 |         1 |  # javascript -- jQuery
|         2 |      2 |         1 |  # library    -- jQuery
|         3 |      1 |         9 |  # javascript -- jQuery mobile
+-----------+--------+-----------+

When I run my SQL, it still selects jQuery Mobile, but should not, because it does not contain a tag library, where jquery, I need it to limit the results that should match the selected tags.

This is my SQL:

SELECT scripts.script_id,
       scripts.name
FROM
(
    scripts scripts

    LEFT OUTER JOIN tagged tagged ON tagged.script_id = scripts.script_id

    LEFT OUTER JOIN tags tags ON tags.tag_id = tagged.tag_id
)
WHERE MATCH(scripts.name) AGAINST ('jquery*' IN BOOLEAN MODE) AND ( tags.url_name = 'javascript' OR tags.url_name = 'library' )
GROUP BY script_id
ORDER BY scripts.name
LIMIT 0, 25

It returns:

+-----------+---------------+
| script_id | name          |
+-----------+---------------+
|         1 | jQuery        |
|         9 | jQuery Mobile |
+-----------+---------------+

If I change ORto AND, it will not return anything or remove the brackets from the tags (and )it will not return anything either.

?

+3
4

:

select s.script_id, s.name from scripts s
join tagged tj on s.script_id = tj.script_id
join tags t on t.tag_id = tj.tag_id
where s.name like 'jQuery%' and t.url_name in ('javascript', 'library')
group by s.script_id, s.name
having count(*) = 2
order by s.name
limit 25

, 2 in.

fiddle.

+2

, , . , . .

, :

+-----------+-----------+---------------+
|     tag_id| script_id | name          |
+-----------+-----------+---------------+
|         1 |         1 | jQuery        |
|         2 |         1 | jQuery        |
|         1 |         9 | jQuery Mobile |
+-----------+-----------+---------------+

, ,

HAVING count(tag_id) = 2

sql, , , , 2 .

, :

SELECT scripts.script_id,
       scripts.name
FROM
(
    scripts scripts

    LEFT OUTER JOIN tagged tagged ON tagged.script_id = scripts.script_id

    LEFT OUTER JOIN tags tags ON tags.tag_id = tagged.tag_id
)
WHERE MATCH(scripts.name) AGAINST ('jquery*' IN BOOLEAN MODE) AND ( tags.url_name = 'javascript' OR tags.url_name = 'library' )
GROUP BY script_id
HAVING count(tag_id) = 2
ORDER BY scripts.name
LIMIT 0, 25
+1
SELECT s.script_id,
       s.name
FROM
        scripts AS s
    INNER JOIN 
        tagged AS st1
      ON  st1.script_id = s.script_id
    INNER JOIN tags AS tag1
      ON  tag1.tag_id = st1.tag_id
      AND tag1.url_name = 'javascript'
    INNER JOIN 
        tagged AS st2
      ON  st2.script_id = s.script_id
    INNER JOIN tags AS tag2
      ON  tag2.tag_id = st2.tag_id
      AND tag2.url_name = 'library'
ORDER BY s.name
LIMIT 0, 25
0
source

My attempt:

SELECT s.script_id, s.name, so_tags.url_name 
  FROM so_scripts s 
       LEFT JOIN so_tagged USING (script_id) 
       LEFT JOIN so_tags USING (tag_id)
  WHERE s.script_id IN 
  (
    SELECT td.script_id FROM so_tagged td 
      LEFT JOIN so_tags t USING (tag_id) 
    WHERE t.url_name = "javascript" OR t.url_name = "library"
  ) 
GROUP BY url_name HAVING COUNT(s.name) = 2

This is a bit hacked, it first searches for all records matching any of the tags, and then groups by name and returns only those with the number of matches equal to the number of tags delivered :)

Not sure how fast it will be for 3, 4 or more tags, but at least it works :)

0
source

More articles:


All Articles