Hmm I'll see if this has some effect. If not I might try to update our OJB
version and see if that works. In any case, thanks for the help :)
----- Original Message -----
From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Friday, October 04, 2002 5:06 PM
Subject: Re: Query on collections
> hi rudi,
>
> the problem is the same alias A3 is used for different tables.
> i've tried to build a similar tests case using current ojb. i do not have
> the same problem but at least i could prevent creation of superfluous
> TableAlias.
>
> if you want to dig deeper set a breakpoint in
> SqlQueryStatement#getTableAlias
>
> ...
> indirect = (TableAlias) m_pathToAlias.get(attrPath + "*");
> if (indirect == null)
> {
> indirect = createTableAlias(cod.getIndirectionTable(), attrPath +
"*");
> }
> ...
>
> the if == null stuff is new, so please try it with your ojb version (it
> solved my problems)
>
> hth
> jakob
>
>
> ----- Original Message -----
> From: "Rudi Alberda" <[EMAIL PROTECTED]>
> To: "OJB Users List" <[EMAIL PROTECTED]>
> Sent: Friday, October 04, 2002 3:05 PM
> Subject: Re: Query on collections
>
>
> > Ok, here goes...
> >
> > We use OJB 0.9.5, MS SQL Server 2000 (and JBoss but that doesn't really
> > matter)
> >
> > The exception is created by the following code:
> >
> > ...
> > crit.addEqualTo("categories.id", categoryId);
> > Criteria crit2 = new Criteria();
> > crit2.addEqualTo("categories.mainCategory.id", categoryId);
> > crit.addOrCriteria(crit2);
> > Query q = new QueryByCriteria(ContentItem.class, crit, true);
> > broker.getIteratorByQuery(q);
> > ...
> >
> > Repository.xml snippet:
> >
> > ...
> > <class-descriptor
> > class="nl.casthere.uib.ojb.Category"
> > table="@[email protected]_category"
> > >
> > <extent-class class-ref="nl.casthere.uib.ojb.CourseCategory" />
> > <extent-class class-ref="nl.casthere.uib.ojb.ShowCategory" />
> > <extent-class class-ref="nl.casthere.uib.ojb.AccomodationCategory"
/>
> > <extent-class class-ref="nl.casthere.uib.ojb.LinkCategory" />
> > &category;
> >
> > </class-descriptor>
> >
> > <!-- Definitions for CourseCategory -->
> >
> > <class-descriptor
> > class="nl.casthere.uib.ojb.CourseCategory"
> > table="@[email protected]_category"
> > >
> > <!-- Category properties and relations -->
> > &category;
> > <collection-descriptor
> > name="contentItems"
> > element-class-ref="nl.casthere.uib.ojb.Course"
> > auto-update="false"
> > auto-retrieve="true"
> > auto-delete="false"
> > proxy="true"
> > indirection-table="@[email protected]_content_category"
> > >
> > <fk-pointing-to-this-class column="category_id"/>
> > <fk-pointing-to-element-class column="content_item_id"/>
> > </collection-descriptor>
> > </class-descriptor>
> > ...
> >
> > the reference to &category is another xml file, containing (amongst
other
> > things):
> >
> > ...
> > <reference-descriptor
> > name="mainCategory"
> > class-ref="nl.casthere.uib.ojb.Category"
> > auto-update="false"
> > auto-retrieve="true"
> > auto-delete="false"
> > proxy="true"
> > >
> > <foreignkey field-id-ref="5" />
> > </reference-descriptor>
> > ...
> >
> > Jboss server.log fragment:
> >
> > ...
> > 2002-10-04 14:48:02,780 DEBUG
> > [org.apache.ojb.broker.singlevm.PersistenceBrokerImpl]
> getIteratorFromQuery
> > class nl.casthere.uib.ojb.ContentItem, Query from class
> > nl.casthere.uib.ojb.ContentItem where categories.id = ? OR
> > categories.mainCategory.id = ?
> > 2002-10-04 14:48:02,780 DEBUG
> [org.apache.ojb.broker.accesslayer.RsIterator]
> > RsIterator(Query from class nl.casthere.uib.ojb.ContentItem where
> > categories.id = ? OR categories.mainCategory.id = ? , table:
> > uib.uib_location
> > FieldDescriptions:
> [Lorg.apache.ojb.broker.metadata.FieldDescriptor;@84ff20)
> > 2002-10-04 14:48:02,780 DEBUG
> [org.apache.ojb.broker.accesslayer.JdbcAccess]
> > executeQuery : Query from class nl.casthere.uib.ojb.ContentItem where
> > categories.id = ? OR categories.mainCategory.id = ?
> > 2002-10-04 14:48:02,780 DEBUG
> > [org.apache.ojb.broker.accesslayer.SqlGenerator] SQL: SELECT DISTINCT
> >
>
A0.image_id,A0.originator_id,A0.description,A0.street_number_addition,A0.url
> >
>
,A0.city,A0.keywords,A0.dummy,A0.opening_hours,A0.last_modified_by,A0.id,A0.
> >
>
zipcode_number,A0.ticket_sale,A0.ojb_concrete_class,A0.feed_remarks,A0.last_
> >
>
modified,A0.state,A0.zipcode,A0.created_by,A0.creation_date,A0.name,A0.route
> > ,A0.facility_text,A0.street_name,A0.street_number FROM (uib.uib_location
> A0
> > INNER JOIN uib.uib_content_category A3 ON A0.id=A3.content_item_id)
INNER
> > JOIN (uib.uib_content_category A1 INNER JOIN (uib.uib_category A2 INNER
> JOIN
> > uib.uib_category A3 ON A2.main_category_id=A3.id) ON
A1.category_id=A2.id)
> > ON A0.id=A1.content_item_id WHERE A2.id = ? OR A3.id = ?
> > 2002-10-04 14:48:02,780 DEBUG
> > [org.apache.ojb.broker.accesslayer.ConnectionManager] Request new
> connection
> > from ConnectionFactory:
> > com.microsoft.jdbc.sqlserver.SQLServerConnection@2ba88c
> > 2002-10-04 14:48:02,800 ERROR
> [org.apache.ojb.broker.accesslayer.JdbcAccess]
> > SQLException during the execution of the query (for a
> > nl.casthere.uib.ojb.Location): [Microsoft][SQLServer 2000 Driver for
> > JDBC][SQLServer]The correlation name 'A3' is specified multiple times in
a
> > FROM clause.
> > java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> > JDBC][SQLServer]The correlation name 'A3' is specified multiple times in
a
> > FROM clause.
> > at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
Source)
> > at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> > at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
> > Source)
> > at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
> > Source)
> > at
> com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown
> > Source)
> > at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
> Source)
> > at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.openCursor(Unknown
> > Source)
> > at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.execute(Unknown
> > Source)
> > at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
> > at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown
> > Source)
> > at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown
> > Source)
> > at org.apache.ojb.broker.accesslayer.JdbcAccess.executeQuery(Unknown
> > Source)
> > at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown Source)
> > at
> >
>
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getIteratorFromQuery(Un
> > known Source)
> > at
> >
>
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
> > known Source)
> > at
> >
>
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
> > known Source)
> > at
> >
>
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
> > known Source)
> > at
> >
>
nl.casthere.uib.ejb.IteratorSessionBean.getIterator(IteratorSessionBean.java
> > :93)
> > ...
> >
> >
> > ----- Original Message -----
> > From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
> > To: "OJB Users List" <[EMAIL PROTECTED]>
> > Sent: Friday, October 04, 2002 1:47 PM
> > Subject: Re: Query on collections
> >
> >
> > > include only some of the top stack entries and only relevant
descriptors
> > > from your repository.
> > >
> > > jakob
> > > ----- Original Message -----
> > > From: "Rudi Alberda" <[EMAIL PROTECTED]>
> > > To: "OJB Users List" <[EMAIL PROTECTED]>
> > > Sent: Friday, October 04, 2002 12:02 PM
> > > Subject: Re: Query on collections
> > >
> > >
> > > > I'll post the sample later on when I have some more time.
> > > > Do you want the entire stack trace along with the repository and the
> > code
> > > > that generates it? Because it's an awful lot (we've got a big
> repository
> > > > with loads of objects)
> > > >
> > > > ----- Original Message -----
> > > > From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
> > > > To: "OJB Users List" <[EMAIL PROTECTED]>
> > > > Sent: Friday, October 04, 2002 10:51 AM
> > > > Subject: Re: Query on collections
> > > >
> > > >
> > > > > hi rudi,
> > > > >
> > > > > > 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,
> > > > >
> > > > > well, we invested a lot of time to keep users happy...
> > > > >
> > > > > > 'The correlation name 'A3' is specified multiple times in a FROM
> > > > clause.'
> > > > > > which was indeed the case. Is this a bug?
> > > > >
> > > > > can you please post the sample and the generated sql ?
> > > > >
> > > > > jakob
> > > > >
> > > > > ----- Original Message -----
> > > > > From: "Rudi Alberda" <[EMAIL PROTECTED]>
> > > > > To: "OJB Users List" <[EMAIL PROTECTED]>
> > > > > Sent: Friday, October 04, 2002 9:30 AM
> > > > > Subject: Re: Query on collections
> > > > >
> > > > >
> > > > > > 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]>
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > 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]>
>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
