Hi Argyn,
You might try:
<page>
<xsp:logic>
String id;
</xsp:logic>
<esql:connection...
<esql:execute-query>
<esql:query>select ID from M...</esql:query>
<esql:results>
<esql:row-results>
<xsp-logic>
id = <esql:get-string column="ID"/>;
</xsp:logic>
...
</esql:execute-query>
<esql:execute-query>
<esql:query>select f1 from F where ID =
<xsp:expr>id</xsp:expr></esql:query>
<esql:results...
...
</esql:execute-query>
...
</esql:connection>
</page>
Although you have to write a bit of Java, it is minimal. I think the ESQL
examples had another technique for passing values between queries, but this
worked for me!
The only puzzling thing is you state at the beginning that you need to do
this for each ROW in table M, so don't the F & C queries need to be
sub-queries of the table M query anyway?
HTH
Perry
-----Original Message-----
From: Argyn Kuketayev [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 26 March 2002 4:30
To: '[EMAIL PROTECTED]'
Subject: Cocoon2: ESQL nested queries optimization
> I've to make a report on the table M, then for each row I
> need to show corresponding rows from tables F and C.
>
> Something like this:
>
> <rowset name="M">
> <row>
> <ID>1</ID>
>
> <rowset name="F">
> <row>
> <f_name>f1</f_name>
> <row>
> ...
> </rowset>
>
> <rowset name="C">
> <row>
> <c_name>c1</c_name>
> <row>
> ....
> </rowset>
> </row>
> ...
> </rowset>
>
I can make a nested query. It works fine. But it seems to me that it makes
sql select queries from C and F tables in a database for every row in M
table.
If I'd write a Java code, I'd do this:
select * from M where ...;
then I'd make two other calss like below:
select * from C where C.ID in (...)
select * from F where F.ID in (...)
where (...) a comma separated list of IDs from query to M.
I'd lke to avoid writing any Java code, because I'm developing the reporting
module, and wish users could limply define their queries in XML/XSP files.
What would you suggest to optimize querying with ESQL?
thanks,
Argyn
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail: <[EMAIL PROTECTED]>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail: <[EMAIL PROTECTED]>
