Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Multiple tag search

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Thanks, it seems to be the best way indeed

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •