Re: Possible Broker query bug?

[Jakob Braeuchi]

hi bill,

can i assume that every OR results in an OUTER JOIN ?
if true i think fixing it is quite easy.
jakob

V.B. Skrypnyk wrote:

Hi Scott,

It seems you ran into exactly the same problem as I did (sorry I didn't find
your post before posting mine). The workaround to the INNER JOIN problem by
using report queries doesn't help in my case, because I modify an existing
query by adding the restrictions (this is permissions oriented layer in my
app). However, I think that this can be easily? fixed in the
SqlQueryStatement.java file. In fact I hacked a few lines of code to fix the
bug, but not being very proficient with the OJB internals I don't know if
it's going to affect anything else.
The relevant code follows:

   private void buildJoinTree(Criteria crit)
   {
       Enumeration e = crit.getElements();
       while (e.hasMoreElements())
       {
           Object o = e.nextElement();
           if (o instanceof Criteria)
           {
               buildJoinTree((Criteria) o);
           }
           else
           {
               SelectionCriteria c = (SelectionCriteria) o;
               if (c.getAttribute() != null)
               {
                   buildJoinTreeForColumn(c.getAttribute(),
crit.getAlias(), ( crit.getType() == cr
it.OR ));
               }
               if (c instanceof FieldCriteria)
               {
                   FieldCriteria cc = (FieldCriteria) c;
                  buildJoinTreeForColumn((String) cc.getValue(),
crit.getAlias(), (crit.getType()
== crit.OR ));
               }
               else if (c instanceof ColumnCriteria)
               {
                   ColumnCriteria cc = (ColumnCriteria) c;
                   buildJoinTreeForColumn((String) cc.getValue(),
crit.getAlias(), (crit.getType()
== crit.OR ));
               }
           }
       }
   }
and you also have to add the following method overloading the existing one:

   private void buildJoinTreeForColumn(String aColName, String aUserAlias,
     boolean outerjoin )
   {
       String pathName = SqlHelper.cleanPath(aColName);
       int sepPos = pathName.lastIndexOf(".");
       if (sepPos >= 0)
       {
           getTableAlias(pathName.substring(0, sepPos), outerjoin,
aUserAlias);
       }
   }
Basically, the gist is that if you get an OR criteria, any resulting join
based on it should be an outer one. Is this a correct assumption?
This seems to have fixed the problem for me in the tests that I have.

Cheers,
--Bill.
----- Original Message -----
From: "Scott Howlett" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Saturday, March 08, 2003 2:47 PM
Subject: RE: Possible Broker query bug?
I would also be interested to know whether this is considered to be a
bug. I mentioned it a while back:
http://archives.apache.org/eyebrowse/[EMAIL PROTECTED]
pache.org&msgId=613222
but never heard anything more.

Regards,
Scott Howlett
-----Original Message-----
From: V.B. Skrypnyk [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 2:21 AM
To: OJB Users List
Subject: Possible Broker query bug?
I come across the following situation with PersistenceBroker queries:

Here is my object
public class Customer {
   ...
   private List acls = new Vector();
   private String realm = "...";
}
acls is a collection with Acl as collection member.

This is the query

Criteria crit = new Criteria();
crit.addEqualTo( "oid", new Long(201));
crit.addLike( "realm", "/201*" );
Criteria or = new Criteria();
or.addEqualTo( "acls.user_FK", new Long( 1 ) ); // notice the reference
field crit.addOrCriteria( or );
Resulting sql statement is:
SELECT ... FROM customers A0 INNER JOIN acls A1 ON A0.cus_oid=A1.acl_eid
WHERE (A0.cus_oid =  '201' ) AND A0.cus_realm LIKE  '/201%'  OR
(A1.acl_uid IN ( '1' ))
The sql statement comes out quite well constructed, except for one
thing. It uses inner join which means that my OR condition in which I
refer to a reference field effectually becomes an AND condition.
Shouldn't the statement contain rather left join in this case?
Cheers,
--Bill.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]