db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Elie Medeiros" <nc...@networkphotographers.com>
Subject Self-joins and selecting indinvidual columns using Criteria
Date Tue, 29 Jul 2003 12:11:34 GMT
How to select individual columns using self-joins from a single or group
of tables in Torque with Criteria.

I had a few problems doing this, and there is hardly any helpful info 
in the docs at all as to how to do this, so I thought i'd write a quick
tutorial to save others the time I've spent finding this out.

The scenario is the following: we have a single table, table1 (there
could be multiple tables though, the methodology is identical). Table
definition as follows:

Table1:
pk
result_id
relevance
col3

where pk is the id column, result_id is a non-unique FK referencing rows
in another table, relevance is an integer describing the relevance of a
result, and col3 is the column whose value you want to search - it could
be a timestamp, a search identifier, a descriptive string etc...

We want to select table1.result_id and a sum of the relevance columns,
based on different values of col3. For this, we will have to do a
self-join on the table, selecting the distinct result_ids matching each
different value of col3. As we will be wanting to calculate the
relevance of the result, and SQL math functions are not available in
Torque, we will also have to select the different relevance values for
each matching col3/result_id combination and add it up in Java. For
this, we will need to use alias columns, so that the SQL query we want
to obtain looks something like this:

SELECT table1.result_id, table1.relevance, alias1.relevance,
alias2.relevance
FROM table1
LEFT JOIN table1 AS alias1 ON table1.pk=alias1.pk
LEFT JOIN table1 AS alias2 ON alias1.pk=alias2.pk
WHERE table1.col3='555'
AND alias1.colum3='666'
AND alias2.colum3='777'

The following query can be reproduced using Criteria:

Criteria crit=new Criteria();
    //add aliases
crit.addAlias("alias1", TablePeer.TABLE_NAME);
crit.addAlias("alias2", TablePeer.TABLE_NAME);
    //add joins
crit.addJoin("alias1.pk", TablePeer.pk);
crit.addJoin("alias2.pk", "alias1.pk");
    //add search criteria
crit.add(TablePeer.COLUMN_3, 'xyz');
crit.add("alias1.COLUMN_3", 'aaa');
crit.add("alias2.COLUMN_3", 'aab');
    //add select by column
crit.addSelectColumn(TablePeer.RESULT_ID);
crit.addSelectColumn(TablePeer.RELEVANCE);
crit.addSelectColumn("alias1.relevance");
crit.addSelectColumn("alias2.relevance");

If you don't know how many different values of col3 you will be looking
for, you will have to add the aliases, search criteria and selectColumns
dynamically.

You can then get the List of results back using the BasePeer Object:

List rows=BasePeer.doSelect(crit);

As Torque is based on the Village database abstraction layer, the
BasePeer.doSelect(Criteria crit) method returns a List of
com.workingdogs.village.Record objects, which you can then loop through
using an Iterator or a for{} loop:

for (int i = 0; i < rows.size(); i++) {
	com.workingdogs.village.Record row =
(com.workingdogs.village.Record)rows.get(i);
	//get primary_key_value
	int pk = row.getValue(1).asInt();
	//calculate relevance;
	int relevance = 0;
	for (int j = 2; j <= 4; j++) {
		relevance += row.getValue(j).asInt();
	}
}

Here, we use the Record.getValue(int i) method to return the different
columns' values. This method is 1-(not0-) based. You can then use
getType() -where Type is the type of the data you want to retrieve- to
get the value for that row's column.

I hope this helps someone.

Elie

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


Mime
View raw message