[odb-users] Use a view to query many-to-many relationship

[Lukas Barth]

Hi,

I figured out how to do what I want (see previous mail) in raw SQL, for
reference. The example from my previous mail could be achieved like this:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'foo' AND value = 'bar'
) as SQ1 on SQ1.cfg = MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'fuz' AND value = 'baz'
) as SQ2 on SQ2.cfg = MyObject.cfg
WHERE MyObject.value = 42
  AND NOT EXISTS
      ( SELECT 1
        FROM ConfigKV
        WHERE ConfigKV.cfg = MyObject.cfg
          AND NOT ( key = 'foo' AND value = 'bar'
                 OR key = 'fuz' AND value = 'baz'
                  )
      ) ;

The query looks a bit intimidating, but is pretty easy:

- One INNER JOIN per Key-Value pair to eliminate all MyObjects that
don't have a required key-value mapping
- The final "AND NOT EXISTS" to filter out everything that has extra
Key-Value mappings.

I know I can execute raw SQL using db::execute, but that doesn't allow
me to retrieve the results. I know that I can have arbitrary SQL in
views using pragma annotations, but that SQL needs to be known at
compile time, right? I need to build the SQL dynamically, because the
number of INNER JOINs required depends on the number of Key-Value pairs…

Thanks again,

Lukas