How can I clean up this query?

I am generating queries which carry out tag searches on the following tables:

entry     : id, name, desc
tag       : id, name
entry_tag : entry, tag, val

The query is generated from a user-entered predicate such as:

(year >= 1990 and year < 2000) and (status = 'complete' or status = 'pending')

It resembles the following:

select * from entry where id in
  (select entry
  from
    (select t0.entry, t0.val as c0, t1.val as c1, …[1]
    from
      (select entry, val from entry_tag where tag in
        (select id from tag where name = 'year')) as t0,
      (select entry, val from entry_tag where tag in
        (select id from tag where name = 'status')) as t1,
      …[2]
    where t0.entry = t1.entry and …[3]) as t
  where
    …[4]);

(The deep nesting is my naïve attempt to minimise the number of roundtrips, assuming of course that 开发者_JAVA技巧that’s a Good Thing™ to do.)

1. Find tags by name and alias each value to c0…cn in order of input appearance.

2. Alias each tag_entry query to the corresponding t0…tn.

3. Group tag mappings by entry.

4. Insert the transformed Boolean expression directly into the where clause, e.g.:

   (cast(c0 as signed) >= 1990 and cast(c0 as signed) < 2000)
     and (c1 = 'complete' or c1 = 'pending')
   

Is there a better way to go about this, or am I on the right track?

Having to produce this seems wrong:

t0.entry = t1.entry and … and t0.entry = tn.entry

I’m just not sure how else to go about this in a way that preserves the structure of the predicate entered by the user. I don’t want to generate a mangled series of joins when I can do a straightforward mechanical transformation.