Using join-queries to build up (complex) object trees

[Nils Guenther <n....@ngol.de>]

Hello fellows,

I have comme pretty far with ojb and its docs, but now I´m stuck on a very specific 
problem. I just do not get the point.

At first my Object model. I´ve got four classes:
- ArticleGroupType this stands for "food", "beverages", "smoking stuff" etc.
- ArticleGroup this stands for sth. like "beers", "soft drinks", "pizza", "salads" 
etc.
- Article this stands for "Carlsberg 0,2l", "Pizza Calzone" etc.
- DistributionChannel  this stands for "dilivery service", "shipping by 
mail", "self service" etc.


Now to their relationships (as you might already have guessed):

-ArticleGroupType 1 : n ArticleGroup (ArticleGroupType contains a list of 
ArticleGroup-Elements and the DB-Table T_ArticleGroup has a column 
articleGroupTypeId)
-ArticleGroup 1 : n Article (ArticleGroup contains a list of Article-Elements and 
the DB-Table T_Article has a column ArticleGroupId)
-Article m : n DistributionChannel (Article contains a list of DistributionChannel-
Elements and the DB contains a table T_Article_DistributionChannel with columns 
ArticleId, DistributionChannelId)

I have built up the whole stuff using ojb´s xdoclet-module the keys are anonymous. 
Now I´d like to do sth. like this:"Give me an Object ArticleGroupType "food". This 
shall be filled with ArticleGroups and Articles so that only Articles are shown 
which contain the DistributionChannel "delivery service" and if this leads to empty 
ArticleGroups (the ArticleGroup is of type "food", but contains no articles 
with "delivery service") they shall be omitted." This is a case for Join-queries. I 
am using the Persistence-Broker API this is my code:

PersistenceBroker broker= null;
Criteria crit = new Criteria();
crit.addEqualTo("articleGroupTypeId", new Integer(2)); /*2=Id of food*/
crit.addEqualTo(
    "artikelGruppeListe.artikelListe.Artikel" +
    ".lieferArtListe.lieferArtId", 
    new Integer(1) ); /*1=Id of DistributionChannel "delivery service"*/
Query query = 
    new QueryByCriteria(IArticleGruopType.class, crit, true); /*Interface for 
ArticleGroupType*/
try {
    broker = PersistenceBrokerFactory.DefaultPersistenceBroker();
    IArticleGroupType agt =
        (IArticleGroupType) broker.getObjectByQuery(query);
} catch (PersistenceBrokerException) {}...


This leads to a very impressive SQL-Statement with several JOINS. I have tested it 
manually with a DB-Client and the result is 13 lines containing values (2,'food'). 
So the ArticleGroupType´s name is right and the number of lines is the exact amount 
of non-empty ArticleGroups. But iterating over the ArticleGroupList in the 
ArticleGroupType results in a new SQL-Statement which fetches ALL ArticleGroups 
with articleGroupTypeId=2 (not just the non-empty ones). How can I prevent this 
behaviour? What am I doing wrong? I have allready tried disabling proxy-mechanism 
but without success.

A long story for a specific problem :)) Every help is appreciated.

Greetings Nils

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org