You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by pk_cayenne <pi...@uhb.fr> on 2012/05/22 12:54:48 UTC
Queries with multiple tables and results
Hello,
I am a newbie with in cayenne and in english too.
I am using cayenne 3.0.2
Here is my probleme
I have two tables let say *table1 (id1, name, surname)* and *table2(id2,
title, amount)* with oracle database
And my query is like this
select name, title, amount from table1, table2 where id1=id2
Now my question : Would you show me , how can I end up with it (concrete
example please)? (I mean my result must be objetcs []). I did not see
anything in the cayenne doc that meet my needs.
Thank you very much for your help
--
View this message in context: http://cayenne.195.n3.nabble.com/Queries-with-multiple-tables-and-results-tp4006413.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: Queries with multiple tables and results
Posted by Michael Gentry <mg...@masslight.net>.
Hi Pierre,
If you want actual Java objects back (Table1 and Table2), you could do
a normal SelectQuery(Table1.class) and add a prefetch to Table2 to
efficiently read those records in:
http://cayenne.apache.org/doc/prefetching.html
Of course, this will read in all records from Table1 and Table2, which
may be a big memory footprint.
mrg
On Tue, May 22, 2012 at 10:56 AM, pk_cayenne <pi...@uhb.fr> wrote:
> Hi Emerson,
>
> I try the Option 2 and the result return a list of *DataRow *(which
> contains my expected values)
>
> SQLTemplate query = new SQLTemplate(Table1.class,sql);
> query.setFetchingDataRows(true);
> List <DataRow> rows =(List <DataRow>) context.performQuery(query);
>
>
> Best Regards.
>
> --
> View this message in context: http://cayenne.195.n3.nabble.com/Queries-with-multiple-tables-and-results-tp4006413p4006810.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: Queries with multiple tables and results
Posted by pk_cayenne <pi...@uhb.fr>.
Hi Emerson,
I try the Option 2 and the result return a list of *DataRow *(which
contains my expected values)
SQLTemplate query = new SQLTemplate(Table1.class,sql);
query.setFetchingDataRows(true);
List <DataRow> rows =(List <DataRow>) context.performQuery(query);
Best Regards.
--
View this message in context: http://cayenne.195.n3.nabble.com/Queries-with-multiple-tables-and-results-tp4006413p4006810.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: Queries with multiple tables and results
Posted by Emerson Castañeda <em...@gmail.com>.
Hi Pierre
For that cases with queries with many tables I know about two options:
Option 1:
Coding an store procedure in you database that includes your query and then
invoque the procedure recovering the composed results from two or more
tables, someway like this:
ProcedureQuery query = new ProcedureQuery("YOURPROCEDURE");
// Set "IN" parameter values
query.addParameter("THEPARAM1", ID1);
// run query
QueryResponse result = getDataContext().performGenericQuery(query);
// QueryResponse result = performNonSelectingQuery(query);
// read OUT parameters
List outList = result.firstList();
if (outList.size() > 0) {
Map outParameterValues = (Map) outList.get(0);
Object[] r = outParameterValues.values().toArray();
return r;
}
return null;
Option 2:
Using SQLTemplate class:
String pgTemplate = "SELECT *"
+ " FROM table1, table2 WHERE"
+ " ID1 = ID2 AND ID1=X"
SQLTemplate query = new SQLTemplate(Observacion.class, pgTemplate);
query.setFetchingDataRows(true);
//query.setFetchLimit(100);
//query.setTemplate(PostgresAdapter.class.getName(), pgTemplate);
List list = performQuery(query);
Best Regards
EMERSON
On Tue, May 22, 2012 at 2:58 PM, pk_cayenne <pi...@uhb.fr> wrote:
> Hi,
> Unfortunatly i'm not referring to an "inner join" .
>
> What i see in the documentation is that i can only fetch data from a
> *single table * with the /SelectQuery(Class<?> objectClass, Expression
> e)/.
>
> But in my queries I have at least two tables. I don't how to get a resulset
> like as list.
> Something like Object[] result = context.performQuery("myQuery").
>
> I don't know if what I'm saying is understandable.
>
>
> --
> View this message in context:
> http://cayenne.195.n3.nabble.com/Queries-with-multiple-tables-and-results-tp4006413p4006594.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.
>
Re: Queries with multiple tables and results
Posted by pk_cayenne <pi...@uhb.fr>.
OK I understand.
--
View this message in context: http://cayenne.195.n3.nabble.com/Queries-with-multiple-tables-and-results-tp4006413p4013416.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: Queries with multiple tables and results
Posted by Aristedes Maniatis <ar...@maniatis.org>.
I'll chime in with the "you probably aren't doing it the Cayenne way" response. Why do you want a mixture of two different records in the one result set? I can think of two reasons:
1. You have two tables which represent similar things: Customers and Suppliers. You want to query them together to get a list of Contacts. In that case modelling them with a common superclass might be a more helpful approach. Read up on Cayenne inheritance.
2. You have a Contacts table but are trying to optimise the SQL by returning Invoices at the same time since you know you need to iterate through both. In this case, just query on Contacts and ignore Invoices. Cayenne will fetch those as you need them and reference them. contact.getInvoices() will fault them from the database as required.
If you are worried about performance of this approach, prefetching as Michael pointed you in the docs will solve that.
Ari
On 22/05/12 10:58pm, pk_cayenne wrote:
> Hi,
> Unfortunatly i'm not referring to an "inner join" .
>
> What i see in the documentation is that i can only fetch data from a
> *single table * with the /SelectQuery(Class<?> objectClass, Expression
> e)/.
>
> But in my queries I have at least two tables. I don't how to get a resulset
> like as list.
> Something like Object[] result = context.performQuery("myQuery").
>
> I don't know if what I'm saying is understandable.
>
>
> --
> View this message in context: http://cayenne.195.n3.nabble.com/Queries-with-multiple-tables-and-results-tp4006413p4006594.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.
>
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: Queries with multiple tables and results
Posted by pk_cayenne <pi...@uhb.fr>.
Hi,
Unfortunatly i'm not referring to an "inner join" .
What i see in the documentation is that i can only fetch data from a
*single table * with the /SelectQuery(Class<?> objectClass, Expression
e)/.
But in my queries I have at least two tables. I don't how to get a resulset
like as list.
Something like Object[] result = context.performQuery("myQuery").
I don't know if what I'm saying is understandable.
--
View this message in context: http://cayenne.195.n3.nabble.com/Queries-with-multiple-tables-and-results-tp4006413p4006594.html
Sent from the Cayenne - User mailing list archive at Nabble.com.
Re: Queries with multiple tables and results
Posted by Joe Baldwin <jf...@earthlink.net>.
If what you are referring to is an "INNER JOIN" then you might look into the documentation here
http://cayenne.apache.org/doc30/qualifier-expressions.html
You would need to create a Qualifier Expression object using the Cayenne JOIN Semantics, to give to the Query object.
Someone else might be able to give an example of a suitable Cayenne Expression.
Joe
On May 22, 2012, at 6:54 AM, pk_cayenne wrote:
> Hello,
> I am a newbie with in cayenne and in english too.
> I am using cayenne 3.0.2
> Here is my probleme
> I have two tables let say *table1 (id1, name, surname)* and *table2(id2,
> title, amount)* with oracle database
> And my query is like this
> select name, title, amount from table1, table2 where id1=id2
>
> Now my question : Would you show me , how can I end up with it (concrete
> example please)? (I mean my result must be objetcs []). I did not see
> anything in the cayenne doc that meet my needs.
>
> Thank you very much for your help
>
> --
> View this message in context: http://cayenne.195.n3.nabble.com/Queries-with-multiple-tables-and-results-tp4006413.html
> Sent from the Cayenne - User mailing list archive at Nabble.com.