[C2] esql nested queries

["Mark S. Kent" <ma...@vistyx.com>]

I'm trying to find the best way to organize my XML data before doing the XSL
stylesheet, and wanted to create my queries using ESQL nesting like this:

<esql:execute-query>
  <esql:query>select foo from bar</esql:query>
  <esql:results>
    <esql:row-results>
      <data>
        <a>
        <esql:execute-query>
          <esql:query>select foo1 from bar1 where id = a</esql:query> (where
"a" id from "ancestor=1" query just before)
          <esql:results>
            <esql:row-results>
              <data first sub>
                <b>
                <esql:execute-query>
                  <esql:query>select foo2 from bar2 where name = b
</esql:query> (where "b" name from "ancestor=1" query just before)
                  <esql:results>
                    <esql:row-results>
                      <data second sub>
                        <c>
                      </data second sub>
                    </esql:row-results>
                  </esql:results>
                </esql:execute-query>
              </data first sub>
            </esql:row-results>
          </esql:results>
        </esql:execute-query>
      </data>
    </esql:row-results>
  </esql:results>
</esql:execute-query>

which produces results of:
data 1
  data first sub 1
    data second sub 1
    data second sub 2

  data first sub 2
    data second sub 1

data 2
  data first sub 1
    data second sub 1
    data second sub 2

...and so on... 

I know this is easier is XSLT to process, but my concern is for Cocoon and
the potentially huge number of times these sub-queries have to run for each
previous query result-set.  If I had 20 results from the first query and 100
from the second query, that means the third query would "run" 2000 times.
Can someone confirm for me that this is how Cocoon2 handles these sub-query
requests?  I'd rather not hammer the database like that.

My alternative was a "flat" XML structure for the third-level data (which
included the data from the level 1 and 2 queries) but was making the
processing in XSLT more difficult.

Thanks in advance!
Mark

RE: [C2] esql nested queries

[Max Larsson <ma...@gmservice.de>]

Hi,

i have no clue if it really hammers your databse. But i had similar problem.
I wanted to have a tree structure. And because that i didn't new in advance
how
deep the tree was i had to choose between just querying a fixed deep level
or
making a flat output of the tree. The flat ouput wouldn't stop on any
particula
deep, but wasn't so nice to handle in XSL if i wanted a hierarchy. Thus i
made 
just a little XSL, which transform the flat output into a hierarchy, which
i always apply to the output before i use the actual XSL.

Max

> -----Original Message-----
> From: Mark S. Kent [mailto:mark.kent@vistyx.com]
> Sent: Montag, 29. Oktober 2001 20:10
> To: cocoon-users@xml.apache.org
> Subject: [C2] esql nested queries
> 
> 
> I'm trying to find the best way to organize my XML data 
> before doing the XSL stylesheet, and wanted to create my 
> queries using ESQL nesting like this:
> 
> <esql:execute-query>
>   <esql:query>select foo from bar</esql:query>
>   <esql:results>
>     <esql:row-results>
>       <data>
>         <a>
>         <esql:execute-query>
>           <esql:query>select foo1 from bar1 where id = 
> a</esql:query> (where "a" id from "ancestor=1" query just before)
>           <esql:results>
>             <esql:row-results>
>               <data first sub>
>                 <b>
>                 <esql:execute-query>
>                   <esql:query>select foo2 from bar2 where 
> name = b </esql:query> (where "b" name from "ancestor=1" 
> query just before)
>                   <esql:results>
>                     <esql:row-results>
>                       <data second sub>
>                         <c>
>                       </data second sub>
>                     </esql:row-results>
>                   </esql:results>
>                 </esql:execute-query>
>               </data first sub>
>             </esql:row-results>
>           </esql:results>
>         </esql:execute-query>
>       </data>
>     </esql:row-results>
>   </esql:results>
> </esql:execute-query>
> 
> which produces results of:
> data 1
>   data first sub 1
>     data second sub 1
>     data second sub 2
> 
>   data first sub 2
>     data second sub 1
> 
> data 2
>   data first sub 1
>     data second sub 1
>     data second sub 2
> 
> ...and so on... 
> 
> I know this is easier is XSLT to process, but my concern is 
> for Cocoon and the potentially huge number of times these 
> sub-queries have to run for each previous query result-set.  
> If I had 20 results from the first query and 100 from the 
> second query, that means the third query would "run" 2000 
> times.  Can someone confirm for me that this is how Cocoon2 
> handles these sub-query requests?  I'd rather not hammer the 
> database like that.
> 
> My alternative was a "flat" XML structure for the third-level 
> data (which included the data from the level 1 and 2 queries) 
> but was making the processing in XSLT more difficult.
> 
> Thanks in advance!
> Mark
>