Here is a similar query from one of my applications:
SelectQuery q = new SelectQuery(ZWells.class);
q.setQualifier( Expression.fromString( "toZPlates.plateTypCd =
'GPR'") );
q.andQualifier( Expression.fromString(
"toZPlates.toZPlateSlidePrefixes.prefix = 'UBA'") );
q.addOrdering("toZPlates.plateDesignator", true);
q.addOrdering("xCoord", true);
q.addOrdering("yCoord", true);
q.addPrefetch("toZPlates"); //optional
q.addPrefetch("toZPrimers"); //optional
q.addPrefetch("toZPrimers.toZGenes"); //optional
q.setLoggingLevel(Level.ERROR); //optional
List wells = context.performQuery(q);
if(wells.size() > 0)
{
for(int w = 0; w < wells.size(); w++)
{
ZWells well = (ZWells)wells.get(w);
ZGenes gene = well.getToZPrimers().getToZGenes();
System.out.print(well.getToZPlates().getPlateDesignator() +
" " + well.getXCoord() + " " + well.getYCoord() + " " );
System.out.println( gene.getLocus());
}
}
In your case you would probably start with VILLAGE as your base class and
build the query from there. This assumes that you have foreign key relations
to the other tables, in general. My rule of thumb is to reverse the way you
think about querying -- rather than think top-down, think bottom-up.
Otherwise, you must do many queries that generate lists that you then
iterate and use as the root of the next query... If you start with a table
near or at the bottom of the join chain, you can usually do what I did
above.
The .addPrefetch calls are not needed for many applications. I was trying to
get performance similar to that for using SQL directly. In most
applications, you will get good performance initially and optimal
performance over time by simply taking advantage of the caching in Cayenne
and doing the lazy loading that is the default.
Another comment: If you are making many accesses to the database for
villages, you may want to rethink your query and actually just do multiple
queries since, again, the caching will be able to speed your application
over time.
Best,
Joe
On 12/28/04 6:02 AM, "Emmanuel Okyere" <chief-list..kyere.org> wrote:
> Hello All:
>
> I am struggling with creating joins with cayenne; I've found a lot of
> posts on joins in the archives, but nothing (I have found) seems to
> provide a simple step-by-step method of creating simple joins (selects
> across tables).
>
> Assuming I have 4 tables (District, Ward, Village, SubVillage) and would
> want to perform the following query (for reporting purposes):
>
> select d.DISTRICT_NAME as district, w.WARD_NAME as ward, v.DESCRIPTION
> as village, count(1) as subvillages, sum(s.male_population) as male,
> sum(s.female_population) as female, v.cattle, v.livestock
> from DISTRICT d, WARD w, VILLAGE v, SUBVILLAGE s
> where d.DISTRICT_ID = w.DISTRICT_ID and w.WARD_ID = v.WARD_ID and
> v.VILLAGE_ID = s.VILLAGE_ID
> group by d.DISTRICT_NAME, w.WARD_NAME, v.DESCRIPTION, v.CATTLE, v.LIVESTOCK;
>
> what's the best route to go? So far (for me) everything points to the
> SqlSelectQuery class, but that is deprecated, so I was hoping somebody
> could show me the better way here.
>
> Many thanks,
> Emmanuel
This archive was generated by hypermail 2.0.0 : Tue Dec 28 2004 - 09:30:13 EST