Oh my... so that actually works! Wow... I tried it and it
seemed OJB generates the SQL that we did before 'by hand',
so that's perfect! Just wish I'd known about this sooner,
there is no mentioning of this in any tutorial.
One more thing; when I tried to add 2 criteria on an m:n
relation (on a m:n relation and its parent), the generated
SQL was not accepted by SQL server 2000 and I got an exception
saying something about
'The correlation name 'A3' is specified multiple times in a FROM clause.'
which was indeed the case. Is this a bug?
----- Original Message -----
From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Thursday, October 03, 2002 5:36 PM
Subject: Re: Query on collections
> hi rudi,
>
> this snippet is from test case QueryTest:
>
> Criteria crit = new Criteria();
> crit.addEqualTo("projects.title", "HSQLDB"); // direct m:n
> Query q = QueryFactory.newQuery(Person.class, crit);
> Collection results = broker.getCollectionByQuery(q);
>
> it's looking for persons working on project named "HSQLDB"
>
> hth
> jakob
>
> ----- Original Message -----
> From: "Rudi Alberda" <[EMAIL PROTECTED]>
> To: "OJB Users List" <[EMAIL PROTECTED]>
> Sent: Thursday, October 03, 2002 2:44 PM
> Subject: Re: Query on collections
>
>
> > Hey Chris,
> >
> > Thanks for your tip, but we (and indeed I forgot to mention this)
already
> do
> > the query on m:n
> > objects like this (except we use 'hard' table names and PK field names).
> > What I was wondering
> > was if there was an easier way to do it than like this :)
> >
> > For example something like this would be extremely cool to have in OJB:
> > Criteria c = new Criteria();
> > c.addIn("bees[]", B);
> > Query q = new QueryByCriteria(B.class, c, false);
> > broker.getCollectionByQuery(q);
> >
> > or even
> > Criteria c = new Criteria();
> > c.addEqualTo("bees[0].name", B.getName());
> > Query q = new QueryByCriteria(B.class, c, false);
> > broker.getCollectionByQuery(q);
> >
> > Nevertheless, thanks for your input! :)
> >
> >
> > ----- Original Message -----
> > From: "Chris Lewington" <[EMAIL PROTECTED]>
> > To: "OJB Users List" <[EMAIL PROTECTED]>
> > Sent: Thursday, October 03, 2002 2:28 PM
> > Subject: Re: Query on collections
> >
> >
> > > Hi Rudi,
> > >
> > > I had to address a similar problem as part of implementing a "query by
> > object"
> > > mechanism (tricky!) to search on "partially complete" objects rather
> than
> > using
> > > straight queries themselves.. The basic idea I had was as follows:
> > >
> > > - build an empty A and set your concrete B into its collection (use
this
> > as a
> > > basis for what follows);
> > >
> > > - use the CollectionDescriptor of class A to get the FK column names
of
> > the
> > > indirection table to both the item class (B) and the "this" class (A),
> and
> > also
> > > the name of the indirection table itself;
> > >
> > > - then, from your concrete B, use its ClassDescriptor to get the PK
> field
> > info
> > > and hence from that a PersistentField from which you can obtain the
> actual
> > PK
> > > value of your B object;
> > >
> > > - then build a Criteria object with the following:
> > > 1) an "addEqualToColumn" which matches the PK field for A to its
> > > corresponding column name in the indirection table, plus
> > > 2) an "addEqualTo" which matches the actual PK value you have for B
> with
> > its
> > > corresponding column name in the indirection table;
> > >
> > > - then finally create a "QueryByMtoNCriteria" with the class of A, the
> > > indirection table name and the newly built Criteria object.
> > >
> > > From there you can do a getCollectionByQuery with your newly built
Query
> > object
> > > above. I'm sure there are easier ways but that was the easiest I could
> > come up
> > > with for now and it works OK for me.
> > >
> > > Hope that helps,
> > >
> > > Cheers,
> > >
> > >
> > > Chris
> > >
> > > Rudi Alberda wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a question regarding queries on m:n relations. Suppose I have
> two
> > > > classes which are associated in OJB through a m:n relation, objects
of
> > type
> > > > 'A' and 'B'. 'A' has a property called 'bees' returning all objects
of
> > type
> > > > 'B', 'B' has a property called 'as' which returns all 'A' objects.
> > > > This all works nicely in OJB, storing, updating, deleting. But now I
> > want to
> > > > query. I have an object of type 'B' and I want to know which objects
> of
> > type
> > > > 'A' are associated to it. In other words, I'd like a query which
> results
> > in
> > > > a collection of 'A' objects for which 'B' is in the collection
> 'A.bees'.
> > > > How would this work?
> > > > I cannot simply go and create a query like:
> > > >
> > > > Criteria c = new Criteria();
> > > > c.addEqualTo("bees", B);
> > > > Query q = new QueryByCriteria(B.class, c, false);
> > > > broker.getCollectionByQuery(q);
> > > >
> > > > because bees is a collection. Will Criteria.addIn work? And if this
> > doesn't,
> > > > what will?
> > > > I have looked at QueryByMtoNCriteria but the documentation of this
was
> > > > rather unclear.
> > > > Also, I might be able to use Criteria.addExists, but I need to refer
> to
> > a
> > > > field from the main query in the subquery, otherwise my subQuery
isn't
> > of
> > > > much use.
> > > > Help!
> > > >
> > > > --
> > > > To unsubscribe, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > > > For additional commands, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > >
> > >
> > > --
> > > To unsubscribe, e-mail:
> <mailto:[EMAIL PROTECTED]>
> > > For additional commands, e-mail:
> <mailto:[EMAIL PROTECTED]>
> > >
> > >
> >
> >
> > --
> > To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> > For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
> >
>
>
> --
> To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
