Professional Web Applications Themes

selecting based on matches on multiple rows? - MySQL

[Aplogies if this is a duplicate. I tried to post something similar a few moments ago, and my news client crashed, without saving a local copy of the post. This is a second try, with a different news client] I thought this would be simple, but it has me stumped. I've got items that have attributes. Attributes are of the form "name=value", where "name" and "value" are short strings. The possible attributes names are NOT known in advance. When given a new item, the giver might supply an attribute name that has never been seen before. My task is to ...

  1. #1

    Default selecting based on matches on multiple rows?

    [Aplogies if this is a duplicate. I tried to post something similar a few
    moments ago, and my news client crashed, without saving a local copy of the
    post. This is a second try, with a different news client]

    I thought this would be simple, but it has me stumped. I've got items that
    have attributes. Attributes are of the form "name=value", where "name" and
    "value" are short strings. The possible attributes names are NOT known in
    advance. When given a new item, the giver might supply an attribute name
    that has never been seen before.

    My task is to the store items for retrieval. The retriever will ask for
    them by specifying attributes for the items they are looking for.

    For example, a retriever might want all items where "color = red or size =
    12".

    My first thought is to have an attribute table something like this (not
    normalized, to keep this simpler):

    item_id INT,
    name VARCHAR(16),
    value VARCHAR(16)

    That example above would then translate into a simple SELECT:

    SELECT item_id FROM attribute WHERE (name = "color" AND value = "red")
    OR (name = "size" AND value = "12")

    No problem there, right? But what if the retriever wants this? "color = red
    AND size = 12"?

    This doesn't do it:

    SELECT item_id FROM attribute WHERE (name = "color" AND value = "red")
    AND (name = "size" AND value = "12")

    This looks like it would:

    SELECT ... WHERE name = "color" AND value = "red"
    INTERSECT
    SELECT ... WHERE name = "size" AND value = "12"

    However, MySQL doesn't implement INTERSECT. Oops!

    This particular one can be done with a self join:

    SELECT ... FROM attribute AS a, attribute AS b WHERE
    a.item_id = b.item_id AND a.name = "color"
    AND a.value = "red" AND b.name = "size"
    AND b.value = "12";

    OK, that's not too bad. But what if the retriever specified a dozen terms?
    Then the table would possibly need to be joined to itself 11 times. That
    makes me nervous.

    Here are the ways we've come up with hashing this out at work, and none of
    them make me happy.

    1. Self join, as described above. P the retriever's expression, and
    generate from that a monstrous SQL statement.

    2. Do it in code. P the retriever's expression, do an SQL query for
    each term, and then combine the result sets in code to evaluate the
    expression. (Can do some optimization to reduce the number of SQL queries,
    such as combining ORs).

    3. Similar to #2, but use temporary tables to store intermediate results.

    4. Store with each item a textual representation of all its attribute names
    and values. Use MySQL's full text search to search this, instead of using
    the attribute table. Interesting--I haven't used full text search to know
    if this would work or not, but as a matter of general principle, it is an
    ugly hack even if it works! :-)

    5. Change the spec so that the retriever is only allowed to use OR if they
    want more than one term. :-)

    Is there some clever, efficient, way to do this that I've overlooked? Or
    some better way to the store this data that makes this easy?

    Thanks!

    --
    --Tim Smith
    Tim Smith Guest

  2. #2

    Default Re: selecting based on matches on multiple rows?

    It might not be cleverer or more efficient but you could also construct
    the query this way:

    SELECT item_id, GROUP_CONCAT( name,': ',value ) AS namevalue
    FROM attribute
    GROUP BY item_id
    HAVING namevalue LIKE '%size: 12%'
    AND namevalue LIKE '%color: red%';

    strawberry Guest

Similar Threads

  1. Selecting and Highlighting Multiple Rows in a DataGrid
    By .NETn00b in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: November 19th, 03:34 AM
  2. Selecting Multiple Rows from DB
    By Ultrashock in forum Coldfusion Database Access
    Replies: 7
    Last Post: July 18th, 03:36 AM
  3. Datagrid: Selecting multiple rows/cols
    By PontiMax in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 21st, 03:06 AM
  4. DataGrid: Selecting multiple rows/columns
    By PontiMax in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 12th, 12:38 PM
  5. Selecting records based on multiple values
    By Jacob in forum ASP Database
    Replies: 1
    Last Post: July 7th, 03:56 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139