Re: Possible Broker query bug?

[Jakob Braeuchi]

hi bill,

just commited it.
thanks for the patch.
jakob

V.B. Skrypnyk wrote:

Hi Jakob,

 

hi bill,

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

That would be my assumption.
--Bill.
 

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]
   



---------------------------------------------------------------------
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]