Q: Joins using Criteria ?

[goffredo <go...@twibble.org>]

Hi All,

I have been reading the documentation on using Criteria objects with Torque,
but can't find an example of a join of say three or four tables that returns
all the columns of the three or four tables that satisfy the conditions of the
query. The examples illustrate multiple joins, but retrieve information from
only one of the tables in the join, because the doSelect() method is attached
to Peer classes only. Or am I expected to add tables to my database to handle
this kind of situation?  

I am not sure from reading the documentation if the use of Criteria is designed
to replace SQL ... or if Criteria form a more limited subset of SQL. 

Regards
Goffredo

Re: Q: Joins using Criteria ?

[David Hainlin <dh...@attbi.com>]

Greetings,
One way to approach this is to create a view which joins the req'd 
tables and then define the torque object based on the view.
Works pretty well but there may be other ways too...
David

goffredo wrote:

>Hi All,
>
>I have been reading the documentation on using Criteria objects with Torque,
>but can't find an example of a join of say three or four tables that returns
>all the columns of the three or four tables that satisfy the conditions of the
>query. The examples illustrate multiple joins, but retrieve information from
>only one of the tables in the join, because the doSelect() method is attached
>to Peer classes only. Or am I expected to add tables to my database to handle
>this kind of situation?  
>
>I am not sure from reading the documentation if the use of Criteria is designed
>to replace SQL ... or if Criteria form a more limited subset of SQL. 
>
>Regards
>Goffredo
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>For additional commands, e-mail: torque-user-help@db.apache.org
>
>
>  
>

RE: Joins using Criteria ?

[Chris K Chew <ch...@fenetics.com>]

Criteria objects will replace SQL in all but the most extreme cases.  Take
another look at the Peers Howto
(http://db.apache.org/torque/peers-howto.html), specifically in the "Joins
And Linking Objects" section.  I will repeat the concept below because
sometimes it helps to hear it another way...

If you simply want to pull a field from a related object, use the
<foreign-key> tag in your schema to relate them.  When generating your peers
and classes, this will provide a get<RelatedObject>() or
get<RelatedObject>s() in the objects which you can use to access the foreign
object, and thus the foreign field.

For example, I have a Customer and a BuilderRegistration.  BR's are related
to C's by CUSTOMER_ID.  Say I want the name of the customer that registered
a particular builder:

Schema:
=================================================================
<table name="BUILDER_REGISTRATION">
	...
	<foreign-key foreignTable="CUSTOMER">
		<reference local="CUSTOMER_ID" foreign="CUSTOMER_ID"/>
	</foreign-key>
</table>

Criteria:
=================================================================
Criteria crit = new Criteria();
crit.add(BuilderRegistrationPeer.ID,builderRegId);

Usage:
=================================================================
List results = BuilderRegistrationPeer.doSelect(crit);
BuilderRegistration breg = (BuilderRegistration)results.get(0);
System.out.println("BREG " + builderRegId + " belongs to Customer " +
breg.getCustomer().getName());



I hope this helps,

Chris

> From: goffredo
>
> I have been reading the documentation on using Criteria objects
> with Torque,
> but can't find an example of a join of say three or four tables
> that returns
> all the columns of the three or four tables that satisfy the
> conditions of the
> query. The examples illustrate multiple joins, but retrieve
> information from
> only one of the tables in the join, because the doSelect() method
> is attached
> to Peer classes only. Or am I expected to add tables to my
> database to handle
> this kind of situation?
>
> I am not sure from reading the documentation if the use of
> Criteria is designed
> to replace SQL ... or if Criteria form a more limited subset of SQL.
>
> Regards
> Goffredo