> I have a table that associates name/value search items with documents:
>
> create table search_values (
>     id integer unsigned not null,
>     document varchar(40) not null,
>     name varchar(40) not null,
>     value varchar(40) not null
> );
>
> insert into search_values (id,document,name,value) values
> (1,"doc1","name-1","value-1");
> insert into search_values (id,document,name,value) values
> (2,"doc1","name-2","value-2");
> insert into search_values (id,document,name,value) values
> (3,"doc1","name-3","value-3");
> insert into search_values (id,document,name,value) values
> (4,"doc2","name-a","value-a");
> insert into search_values (id,document,name,value) values
> (5,"doc2","name-b","value-b");
> insert into search_values (id,document,name,value) values
> (6,"doc2","name-c","value-c");
> insert into search_values (id,document,name,value) values
> (7,"doc3","name-1","value-1");
> insert into search_values (id,document,name,value) values
> (8,"doc3","name-2","value-2");
> insert into search_values (id,document,name,value) values
> (9,"doc3","name-3","value-3");
>
> I need to be able to build queries that search for documents having
> name/value pairs set, compound by AND or OR operators, e.g.
> "(name1/value1and name2/value2 and name3,value3)" or "(name1/value1 or
> name2/value2 or name3,value3)", but that is not flexible enough.
>
> By executing something like:
>
> select document, count(*) as count from search_values as search where
> (search.name = "name-1" and search.value like "value-1")
> or
> (search.name = "name-2" and search.value like "value-2")
> or
> (search.name = "name-a" and search.value like "value-a")
> group by document;
>
> I can check how many name/value pairs actually matched for a document
> (e.g. by adding a having clause checking count(document) to the query
> above).
>
> This works fine as long as I want to compound all name/value pairs
> either by OR or AND.
>
> The problems begins when I want to build queries that search for
> arbitrary combinations of name/value pairs on documents, e.g.:
>
> search for docs having:
> name-1,value-1 && name-2,value-2
> should select doc1,doc3
>
> search for docs having:
> name-1,value-x || name-a,value-a
> should select just doc2 (value-x does not exist in any doc)
>
> search for docs having:
> name-2,value-2 || name-c,value-c
> should select doc1,doc2
>
> search for docs having:
> (name-1,value-1 && name-2,value-2) || (name-1,value-1 &&
> name-b,value-b)
> should select doc1,doc3 again
>
> Any suggestions about queries or modifications on my schema would be
> really welcome and merry x-max to everybody ;)
>
> Regards,
> Dominik