cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Painter-Wakefield <paint...@mc.duke.edu>
Subject Re: dynamic query help
Date Tue, 01 Jun 2004 14:27:21 GMT




off the top of my head, start with this (easiest) to get it working.  If it
performs well, you can leave it like this, but for better performance take
a look at http://cocoon.apache.org/2.1/userdocs/xsp/esql.html#Groups
which will let you use a single join query to accomplish your goal.

...
<esql:query>select id, area from ukareas order by area</esql:query>
<esql:results>
      <esql:row-results>
            <area>
                  <name><esql:get-string column="area"/></name>
                  <esql:execute-query>
                        <esql:query>
                              select county from county, navmap
                              where navmap.cid = county.id
                              and aid= <esql:parameter
type="int"><esql:get-int column="id"/></esql:parameter>
                              order by county
                        </esql:query>
                        <esql:results>
                              <counties>
                                    <esql:row-results>
                                          <county><esql:get-string
column="county"/></county>
                                    </esql:row-results>
                              </counties>
                        </esql:results>
                  </esql:execute-query>
            <area>
      </esql:row-results>
</esql:results>

-Christopher



|---------+---------------------------->
|         |           Jason Lane       |
|         |           <jason@root10.net|
|         |           >                |
|         |                            |
|         |           06/01/2004 09:51 |
|         |           AM               |
|         |           Please respond to|
|         |           users            |
|         |                            |
|---------+---------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                      
                       |
  |       To:       users@cocoon.apache.org                                              
                       |
  |       cc:                                                                            
                       |
  |       Subject:  dynamic query help                                                   
                       |
  >--------------------------------------------------------------------------------------------------------------|




Hi list,

I have this ESQL query to my DB:

<esql:query>select id, area from ukareas order by area</esql:query>

This just returns the column rows "ID" and "AREA"

I also have this table which maps tables ukareas and county

create table navmap (
             aid integer,
             cid integer,
             primary key (aid,cid),
             foreign key (id) references ukareas(id),
             foreign key (id) references county(id)
);

I then have this query:

<esql:query>select county from county, navmap where navmap.cid =
county.id and aid= 'X ' order by county</esql:query>

Where 'X' is the result of ukareas.id column, so essentially I'd like
to do something like

for (int i = 0; i &lt; id.length; i++) {
//where id = ukareas.id
             <esql:query>select county from county, navmap where navmap.cid
=
county.id and aid= '<xsp:expr>id[i]</xsp:expr>' order by
county</esql:query>
}
  essentially I'd be outputing something like:

South - East
             Surrey, Kent, etc...
Anglia
             Norfolk, Suffolk, etc...

I have a very, very rough idea of how I could do this but need some
pointers.

Many thanks all.

Jason




---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org


Mime
View raw message