You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by Daniel O'Neil <da...@westpole.com> on 2003/11/20 15:08:34 UTC
Multiple joins to same table in torque
Greetings,
I have a table in my database that has multiple references to a table containing person data. The
references are foreign keys to the person table:
loan.loan_borrower_id
loan.loan_mortgagor_id
Both reference person.person_id.
In SQL if I wanted to return a single row that contained information about both the borrower and
the mortgagor, I would use the following sql statement:
<1> select p1.pers_id,
<2> p2.pers_id,
<3> loan_id,
<4> p1.pers_first_name as borrower_name,
<5> p2.pers_first_name as mortgagor_name
<6> from loan, person p1, person p2
<7> where loan_borrower_id = 1 and loan_mortgagor_id = 2
<8> and p1.pers_id = loan_borrower_id
<9> and p2.pers_id = loan_mortgagor_id;
I don't see an easy way to do this using criteria. The Criterion inner class takes a stab at it,
but it doesn't seem to be able to do joins across different tables, so it would be inadequate to
address the restriction clauses of lines <8> and <9> above.
Is there a way to easily do these kinds of joins, and, if not, is there a way to send a sql
statement directly to the data layer in the torque framework?
Thanks for your help,
Daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: Multiple joins to same table in torque
Posted by Daniel O'Neil <da...@westpole.com>.
Thanks, David.
Your solution gives me a headache, although in terms of the original question it is closest to
giving the database what I had in mind. I made the decision this morning to adhere to the "80-20"
nature of this technology, so what I did instead of making a multi-call query to the same table I
built the person table value objects sequentially using the various IDs stored in the loan table.
The overhead on this kind of thing is absurd relative to a single query (mostly because of the
setup costs of twelve separate Jdbc calls as opposed to one), but my time--and the expected
maintenance cost--is worth far more than the cost of upgrading the box. In this particular
application performance is not critical so it's not a show-stopper.
It occurred to me that this would be an issue in larger applications or if this was to scale to a
user list of thousands. The simplest way to keep the "80-20" philosophy in this situation and be
true to the object model here would be to
a) create a view in the database and custom-build the object model classes to access it. This is
a good solution in a databsae like Oracle.
b) Call a stored procedure to do the work for you. I like this one a lot, too, but I'm not sure
how to do it using torque.
In any case, I think that the key issue here is that the criteria are designed for fairly rapid
code development that cover most situations. Clearly a compromise between normalized database
models and the more abstracted object models in most business is necessary. One thing that I
don't want to do is design crappy databases in order to accomodate my object model, and I'm
thinking, with a little thought and planning, that I can have my normalization and Criteria too ;)
Cheers,
Daniel
--- David Ramsey <dr...@neko.com> wrote:
> Ah, you've touched on one of my hot buttons.
>
> Criteria are good for trivial queries but become ridiculously elaborate
> on anything too complex, and in many cases (as far as I can see) can't
> be used at all.
>
> And since the internals of criteria are hash tables, there's no
> guarantee that the final query that's actually sent to the database
> will have its terms constructed in the order you wish. This can have
> repercussions: for example, Oracle performance nose-dives in many cases
> if joins aren't performed in a particular order-- we're talking about
> the difference between a query returning in 2-3 seconds and a query
> returning in 4 minutes.
>
> Ahem. Anyway. Yes, you can bypass queries entirely and simply write raw
> JDBC code. It's easy and fun! Here's a code example:
>
> // Count all members with field data < jumpCharacter
> //
> // SELECT Count(*) FROM EpgClient where UPPER(<field>) < jumpCharacter
> //
> StringBuffer buf = new StringBuffer(100);
> buf.append( "SELECT COUNT(*) FROM " )
> .append( TABLE_NAME )
> .append( " WHERE UPPER(" )
> .append( field )
> .append( ") < '" )
> .append( jumpCharacter )
> .append( "'" );
> String query = buf.toString();
> Vector results = executeQuery( query );
> Record rec = (Record)results.get(0);
> return rec.getValue(1).asInt();
>
> As you can see, you get a vector of results from the executeQuery
> statement. I haven't moved to Torque yet but I think it works in a
> similar fashion-- you have objects and peers and such, right? This
> example just returns a "count(*)" number; for more complex queries you
> need to parse the data out of the results vector yourself. Here's an
> example:
>
> //
> // We've built the query string, now execute it
> //
> Vector qResults = BasePeer.executeQuery( query );
> int numResults = qResults.size();
> ArrayList results = new ArrayList( numResults );
>
> if ( numResults > 0 )
> {
> EpgBaseObject obj;
> Iterator iter = qResults.iterator();
> while( iter.hasNext() )
> {
> Record row = (Record)iter.next();
> obj = populateObject( row );
> results.add( obj );
> }
> }
> return results;
>
> The key here is the "populateObject" call. In the pre-Torque world,
> your peers normally have a "populateObject" method that's called
> automatically as part of the "making database calls with criteria"
> process. But since we're making the "executeQuery" call ourselves, we
> have to call it to get what we want: a list of populated objects.
>
> -- David Ramsey
>
> On Nov 20, 2003, at 6:08 AM, Daniel O'Neil wrote:
>
> > Greetings,
> >
> > I have a table in my database that has multiple references to a table
> > containing person data. The
> > references are foreign keys to the person table:
> > loan.loan_borrower_id
> > loan.loan_mortgagor_id
> > Both reference person.person_id.
> >
> > In SQL if I wanted to return a single row that contained information
> > about both the borrower and
> > the mortgagor, I would use the following sql statement:
> >
> > <1> select p1.pers_id,
> > <2> p2.pers_id,
> > <3> loan_id,
> > <4> p1.pers_first_name as borrower_name,
> > <5> p2.pers_first_name as mortgagor_name
> > <6> from loan, person p1, person p2
> > <7> where loan_borrower_id = 1 and loan_mortgagor_id = 2
> > <8> and p1.pers_id = loan_borrower_id
> > <9> and p2.pers_id = loan_mortgagor_id;
> >
> > I don't see an easy way to do this using criteria. The Criterion
> > inner class takes a stab at it,
> > but it doesn't seem to be able to do joins across different tables, so
> > it would be inadequate to
> > address the restriction clauses of lines <8> and <9> above.
> >
> > Is there a way to easily do these kinds of joins, and, if not, is
> > there a way to send a sql
> > statement directly to the data layer in the torque framework?
> >
> > Thanks for your help,
> > Daniel
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> >
> >
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: Multiple joins to same table in torque
Posted by David Ramsey <dr...@neko.com>.
Ah, you've touched on one of my hot buttons.
Criteria are good for trivial queries but become ridiculously elaborate
on anything too complex, and in many cases (as far as I can see) can't
be used at all.
And since the internals of criteria are hash tables, there's no
guarantee that the final query that's actually sent to the database
will have its terms constructed in the order you wish. This can have
repercussions: for example, Oracle performance nose-dives in many cases
if joins aren't performed in a particular order-- we're talking about
the difference between a query returning in 2-3 seconds and a query
returning in 4 minutes.
Ahem. Anyway. Yes, you can bypass queries entirely and simply write raw
JDBC code. It's easy and fun! Here's a code example:
// Count all members with field data < jumpCharacter
//
// SELECT Count(*) FROM EpgClient where UPPER(<field>) < jumpCharacter
//
StringBuffer buf = new StringBuffer(100);
buf.append( "SELECT COUNT(*) FROM " )
.append( TABLE_NAME )
.append( " WHERE UPPER(" )
.append( field )
.append( ") < '" )
.append( jumpCharacter )
.append( "'" );
String query = buf.toString();
Vector results = executeQuery( query );
Record rec = (Record)results.get(0);
return rec.getValue(1).asInt();
As you can see, you get a vector of results from the executeQuery
statement. I haven't moved to Torque yet but I think it works in a
similar fashion-- you have objects and peers and such, right? This
example just returns a "count(*)" number; for more complex queries you
need to parse the data out of the results vector yourself. Here's an
example:
//
// We've built the query string, now execute it
//
Vector qResults = BasePeer.executeQuery( query );
int numResults = qResults.size();
ArrayList results = new ArrayList( numResults );
if ( numResults > 0 )
{
EpgBaseObject obj;
Iterator iter = qResults.iterator();
while( iter.hasNext() )
{
Record row = (Record)iter.next();
obj = populateObject( row );
results.add( obj );
}
}
return results;
The key here is the "populateObject" call. In the pre-Torque world,
your peers normally have a "populateObject" method that's called
automatically as part of the "making database calls with criteria"
process. But since we're making the "executeQuery" call ourselves, we
have to call it to get what we want: a list of populated objects.
-- David Ramsey
On Nov 20, 2003, at 6:08 AM, Daniel O'Neil wrote:
> Greetings,
>
> I have a table in my database that has multiple references to a table
> containing person data. The
> references are foreign keys to the person table:
> loan.loan_borrower_id
> loan.loan_mortgagor_id
> Both reference person.person_id.
>
> In SQL if I wanted to return a single row that contained information
> about both the borrower and
> the mortgagor, I would use the following sql statement:
>
> <1> select p1.pers_id,
> <2> p2.pers_id,
> <3> loan_id,
> <4> p1.pers_first_name as borrower_name,
> <5> p2.pers_first_name as mortgagor_name
> <6> from loan, person p1, person p2
> <7> where loan_borrower_id = 1 and loan_mortgagor_id = 2
> <8> and p1.pers_id = loan_borrower_id
> <9> and p2.pers_id = loan_mortgagor_id;
>
> I don't see an easy way to do this using criteria. The Criterion
> inner class takes a stab at it,
> but it doesn't seem to be able to do joins across different tables, so
> it would be inadequate to
> address the restriction clauses of lines <8> and <9> above.
>
> Is there a way to easily do these kinds of joins, and, if not, is
> there a way to send a sql
> statement directly to the data layer in the torque framework?
>
> Thanks for your help,
> Daniel
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org