Multiple tag search

gtk · #1 (**permalink**) 06-24-09, 09:07

Let's say we have a ticket system, each ticket can be tagged.
So we have a table that will store the tags for each ticket.
Example of content:

Code:

+----+-----------+--------+
| id | ticket_id | tag    |
+----+-----------+--------+
|  1 |         1 | truc   |
|  2 |         1 | machin |
|  3 |         1 | chose  |
|  4 |         2 | truc   |
|  5 |         2 | machin |
|  6 |         4 | chose  |
|  7 |         5 | truc   |
|  8 |         6 | machin |
|  9 |         6 | chose  |
+----+-----------+--------+

I'd like to find the tickets which have both the tags "machin" and "chose".
Here the result would be 1 and 6.
I was thinking of using BIT_AND() but not sure it will be the good way.

r937 · #2 (**permalink**) 06-24-09, 11:10

Code:

SELECT ticket_id
  FROM ticket_tags
 WHERE tag IN ( 'machin' , 'close' )
GROUP
    BY ticket_id
HAVING COUNT(*) = 2

by the way, your id column in that table is useless, just remove it and make ( ticket_id , tag ) the primary key

gtk · #3 (**permalink**) 06-24-09, 11:27

Thanks, it seems to be the best way indeed

Pat Phelan · #4 (**permalink**) 06-24-09, 14:52

You also need to create a unique index on ticket_id and tag, or the user might enter six "close" rows for a given ticket_id. On most of the MySQL servers that I use, you can't rely on a primary key definition to prevent this kind of problem.

-PatP

r937 · #5 (**permalink**) 06-24-09, 14:59

Quote:

Originally Posted by Pat Phelan

You also need to create a unique index on ticket_id and tag, or the user might enter six "close" rows for a given ticket_id. On most of the MySQL servers that I use, you can't rely on a primary key definition to prevent this kind of problem.

you're saying that my suggestion in post #2 won't work??? please elaborate

Pat Phelan · #6 (**permalink**) 06-24-09, 16:11

As long as there aren't cases with multiple rows that have duplicate values for any ticket_id and tag combination, your suggestion will work. By creating a unique index, you can ensure that there aren't multiple rows with the same ticket_id and tag values.

In a few cases, declaring a primary key will create the unique index automagically. For every commercial MySQL implementation that I work with a primary key declaration is a comment, the declaration doesn't actually DO anything so a unique index is needed to ensure uniqueness.

Your suggestion will work fine if the uniqueness is enforced.

-PatP

r937 · #7 (**permalink**) 06-24-09, 16:25

Quote:

Originally Posted by Pat Phelan

For every commercial MySQL implementation that I work with a primary key declaration is a comment, the declaration doesn't actually DO anything

please elaborate

you're saying that in most cases, mysql PKs don't enforce uniqueness

if you could be explicit about the table types or other details, i want to test this for myself

frankly, i think you're flat out wrong, but a simple test should set me straight

Pat Phelan · #8 (**permalink**) 06-25-09, 02:38

As an example, use the stock Linux distro, which is MySQL 4.0 on most distributions with only MyISAM installed. Create a table with a declared PK, then try to insert the same values twice. Logically it ought to fail, but in fact it works without error on seven of nine systems that I just tried.

Code:

CREATE TABLE foo (
   bar		INT,
   PRIMARY KEY (bar));

--  This would prevent the problem
--  CREATE UNIQUE INDEX XPKfoo ON foo (bar);

INSERT INTO foo (bar) VALUES (1);	-- This should work
INSERT INTO foo (bar) VALUES (1);	-- This should fail

Two out of nine caught the error, but that's not even a fourth of the systems that I tried. At least in my opinion, the unique index is cheap insurance to make sure that the code you posted (and other code that might rely on uniqueness) runs correctly.

-PatP

r937 · #9 (**permalink**) 06-25-09, 09:35

well, ya got me

i have to admit i cannot test that, so ya got me

but that's a bit preposterous, you know?

you realize, of course, that 4.0 is the great-great-great-grandfather of the current version

that'd be like saying "SQL Server is deficient in such-and-such a manner" without mentioning that i'm talking about SQL Server 6

you really seem to enjoy disparaging mysql

it's getting really tiresome, and i've had enough

Pat Phelan · #10 (**permalink**) 06-25-09, 10:47

You obviously see what you want to see... You seem to have missed the point that I have nine different MySQL configurations that I administer to test. I don't have nine installations of products that I don't like.

If any form of criticism or warning about common problems, then that's a fair analysis. I don't see things that way.

I use MySQL. I like MySQL. I do NOT worship MySQL or any other product. Products are man-made, and as such they usually have faults. They are installed by human beings, often overworked and underpaid human beings at that... This leads to more problems which are caused by the choices made by those same human beings.

The world isn't perfect, and neither is MySQL. That doesn't mean that they aren't good, you just need to have the appropriate protective gear!

MySQL 4.x is not the current release from MySQL. It is what appears on current *ix distros, and since it is installed by default I would imagine it is what is used by most users. My own installation of MySQL and one of my hosts is 5.x (no, neither the same build or the same platform but at least the same major version).

In CandyLand, everyone would have version 7 (which isn't available yet), but most of us have to live in the actual real world and make do with what we've got. I use MySQL, and it does a good job for many things, but pretending that common problems don't exist isn't on my agenda.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech