How to use M:N collections with outer joins and exists ? (PersistenceBroker)

[Theo Niemeijer <po...@juras.org>]

Hi all.
Does anyone like to share their solutions for the following query problems ?
Either I don't get it or their is no clean and simpel way to do it.

 Example: products and dealers, having a non-decomposed n:m relationship. 

 1 - With an n:m collection (with hidden indirection table) 
     search for items that do NOT have a relation. 

     The simple 'crit.addIsNull("dealers")' for products that 
     do not have a dealer does not work (is not recognised by the parser,
     so the name ends up as a column name in the SQL query)

     The workaround 'crit.addIsNull("dealers.id")' obviously does not work, 
     because it creates an INNER JOIN, and the id will never be null. 

     So there does not seem to be an idiom to express this query without
     using special knowledge about the mapping, using complex crit.addSql().

     The query would be something like "NOT EXISTS" on the indirection 
     table I suppose, but I am not strong in SQL, and rather like OJB 
     to generate this for me. 

 2 - Using an OUTER JOIN to search for products with a certain dealer OR
     with a special feature. For example:
     crit.addEqualTo("dealers.name", "jakarta")	
     Criteria crit2 = new Criteria();
     crit2.addEqualTo("type", "commercial");
     crit.addOrCriteria(crit2);

     This should give all software that is of type "commercial" or where the 
     dealer is "jakarta". With an INNER JOIN this will of course not give results,
     but with an OUTER JOIN the chances would be better :->.
     How would i get such a result ? The SQL is quite simple, but the 
     Outer Joins syntax is very database dependant, so I would love to 
     have this done by OJB instead of having this exposed in my code. 

 
To me this looks like questions that anyone using non-decomposed m:n in 
OJB will run into. At least when using the Persistence Proker API, and 
I guess it would not be better in the ODMG or JDO API's ? 

Regards,
	Theo