Re: Order by and joins

Jakob Braeuchi Wed, 17 Mar 2004 11:58:26 -0800

hi alan,

thanks for pointing to the version where i changed this behaviour. it was changed because since 1.37 you can define the join-type for a path:

QueryByCriteria#setPathOuterJoin

    /**
     * Force outer join for path
     *
     * @param aPath
     */
    public void setPathOuterJoin(String aPath)
    {
        getPathOuterJoin().put(aPath, Boolean.TRUE);
    }

before ojb always assumed outer joins for additional columns.

hth
jakob

Olmanson, Alan wrote:

Jakob,

Sure, the generated SQL:

SELECT
A0.CREATED_DATE,A0.REVENUE,A0.SOL_NAME2,A0.SOL_DESC,A0.CLOSING_DATE,A0.TERM_
REASON_ID,A0.CURR_ISO_CODE,A0.NOTE,A0.SOL_MARK_TYPE_ID,A0.CREATED,A0.SOUR_TY
PE_ID,A0.SOL_NAME,A0.CONT_ID,A0.CURR_PROPOSAL_ID,A0.LAST_UPDATE_DATE,A0.CHAN
GED,A0.SOL_STATUS_ID,A0.PROBABILITY,A0.CREATOR,A0.CURR_PRESENTATION_ID,A0.CH
ANGER,A0.EXPECTED_VALUE,A0.CURR_FINANCE_ID,A0.SOL_ID,A0.PRIMARY_QUOTE_ID,A0.
OWNER_ID,A0.SAL_CHAN_ID,A0.CUST_ID,A0.CURR_QUOTATION_ID,A2.CITY as
ojb_col_29 FROM R_SOLUTION A0 INNER JOIN M_CUSTOMER A1 ON
A0.CUST_ID=A1.CUST_ID INNER JOIN M_ADDRESS A2 ON A1.ADDRESS_ID=A2.ADDRESS_ID
ORDER BY 30

I did noticed that Criteria#addOrderByAscending had been deprecated,
unfortunately, switching to QueryByCriteria is going to take a lot of
reworking of my application to use.

I did take a look into the OJB code, and the problem appears to be in
SqlQueryStatement.  The ensureColumns method was changed (between versions
1.36 and 1.37) to call appendColName with a false for useOuterJoins.
Changing that to true, resulted in the following query being generated which
worked. (I however have no idea what side effects this would have, I assume
the true-->false change was made for a reason).

SELECT
A0.CREATED_DATE,A0.REVENUE,A0.SOL_NAME2,A0.SOL_DESC,A0.CLOSING_DATE,A0.TERM_
REASON_ID,A0.CURR_ISO_CODE,A0.NOTE,A0.SOL_MARK_TYPE_ID,A0.CREATED,A0.SOUR_TY
PE_ID,A0.SOL_NAME,A0.CONT_ID,A0.CURR_PROPOSAL_ID,A0.LAST_UPDATE_DATE,A0.CHAN
GED,A0.SOL_STATUS_ID,A0.PROBABILITY,A0.CREATOR,A0.CURR_PRESENTATION_ID,A0.CH
ANGER,A0.EXPECTED_VALUE,A0.CURR_FINANCE_ID,A0.SOL_ID,A0.PRIMARY_QUOTE_ID,A0.
OWNER_ID,A0.SAL_CHAN_ID,A0.CUST_ID,A0.CURR_QUOTATION_ID,A2.CITY as
ojb_col_29 FROM R_SOLUTION A0 LEFT OUTER JOIN M_CUSTOMER A1 ON
A0.CUST_ID=A1.CUST_ID LEFT OUTER JOIN M_ADDRESS A2 ON
A1.ADDRESS_ID=A2.ADDRESS_ID ORDER BY 30

Alan Olmanson.

-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Monday, March 15, 2004 4:01 PM
To: OJB Users List
Subject: Re: Order by and joins

hi alan,

please post the generated sql. is there an ORDER BY clause ?
btw Criteria#addOrderByAscending is deprecated has been moved to
QueryByCriteria.

jakob

Olmanson, Alan wrote:

Hello,

I'm having a problem with query sorting not working the same in the CVS I pulled on 3/4/04 as it did in the 0.9.8.

I do the following query:
        Criteria crit = new Criteria();
           crit.addOrderByAscending("customer.mainAddress.city");
           query = QueryFactory.newQuery("com.firepond.domain.Solution",
crit);
In 0.9.8 this would work, I'd get all the solutions, in the current

version

this returns an empty list.

The problem seem to be that OJB is now using INNER JOIN to join solution,
customer, and address, rather than left outer joins.


Repository (fragment):
<class-descriptor class="com.firepond.domain.Solution" table="R_SOLUTION">

  <field-descriptor id="1" name="id" column="SOL_ID" jdbc-type="NUMERIC"
primarykey="true" autoincrement="true">
   </field-descriptor>

   <field-descriptor id="14" name="customerId" column="CUST_ID"
jdbc-type="NUMERIC">
   </field-descriptor>

   <reference-descriptor name="customer"
class-ref="com.firepond.domain.Customer" proxy="true" auto-update="true">
       <foreignkey field-id-ref="14"/> <!-- customerId -->
   </reference-descriptor>

</class-descriptor>

<class-descriptor class="com.firepond.domain.Customer" table="M_CUSTOMER">

<field-descriptor id="1" name="id" column="CUST_ID"

jdbc-type="NUMERIC"

primarykey="true" autoincrement="true"> </field-descriptor> <field-descriptor id="26" name="addressId" column="ADDRESS_ID" jdbc-type="NUMERIC"> </field-descriptor>

   <reference-descriptor name="mainAddress"
class-ref="com.firepond.domain.Address" proxy="true" auto-update="true">
       <foreignkey field-id-ref="26"/> <!-- addressId -->
   </reference-descriptor>
</class-descriptor>


<class-descriptor class="com.firepond.domain.Address" table="M_ADDRESS"
sequence-manager="com.firepond.domain.ojb.SequenceManagerHighLowImpl">

   <field-descriptor id="1" name="id" column="ADDRESS_ID"
jdbc-type="NUMERIC" primarykey="true" autoincrement="true">
   </field-descriptor>

<field-descriptor id="16" name="city" column="CITY"

jdbc-type="VARCHAR">

   </field-descriptor>
</class-descriptor>

Alan Olmanson

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

Re: Order by and joins

Reply via email to