You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "Olmanson, Alan" <al...@firepond.com> on 2004/03/15 22:53:00 UTC

Order by and joins

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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Order by and joins

Posted by Jakob Braeuchi <jb...@gmx.ch>.
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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org