[ic] Nested Queries

[Dan B]

At 01:47 PM 4/12/2001 -0400, you wrote:
>Hello all...
>
>I'm trying to setup a nested query... here is what I have (most pulled from
>mailing list archives):
>
>[query sql="SELECT * FROM package_data WHERE pid='[value package]'" list=1
>interpolate=1]
>         [set token][sql-param sku][/set]
>         [mvasp tables="products"]
>         <%
>
>         my $retval = "";
>         my $dbh = $Sql{products};
>         my $sql_query = "SELECT * FROM products WHERE
>sku='$Scratch->{token}'";
>
>         my $sth = $dbh->prepare($sql_query) or return error_message("can't
>open products database");
>         my $rc  = $sth->execute() or return HTML("can't open products
>database");
>
>         while($sku = $sth->fetchrow_arrayref()) {
>                 $retval .= "$sku->[0]|";
>         }
>
>         return $retval;
>         %>
>         [/mvasp]
>[/query]
>
>When I pull up the page, I get nothing; no results... I'm running IC 4.6.0
>under RH Linux 7.  Any help would be appreciated...

This is probably my fault.  I was trying to do nested queries with perl, 
because the docs said that [query] was not nestable.  It turns out that the 
docs were out of date and query *is* nestable (so you don't have to do perl).

Here's what Heins said to me (after I got the perl working):

http://developer.akopia.com/archive/interchange-users/2001/msg01564.html

Quoting Dan B (db@cyclonehq.dnsalias.net):
 > Solved it, works perfect.  :-)  Thanks to Christopher F. Miller and the
 > mailing list archive.  Hopefully this will come in handy to the next person
 > searching the archives.
 >

And though I jumped in late, there actually isn't any reason you can't nest a
query. I don't know why I never remembered to remove the "canNest" check for
Tagref.pm, but that is actually a legacy from the less-capable MV3 parser.

This nested query:

     [query list=1 prefix=outer sql="select category from products"]
         [query
                 list=1 prefix=inner
                 sql="
                     SELECT sku,description
                     FROM products
                     WHERE category = '[outer-code]'
                 "][outer-code] : [inner-code] : [inner-param description]<BR>
         [/query]
     [/query]

seems to work fine. Of course you have to have separate prefix= things
so the placeholders get substituted correctly, but it will work. As will
a loop in a loop, etc. Not the best way to get great performance, though. 8-)

-- 
Red Hat, Inc., 131 Willow Lane, Floor 2, Oxford, OH  45056
phone +1.513.523.7621 fax 7501 <mheins@redhat.com>

Experience is what allows you to recognize a mistake the second
time you make it. -- unknown

Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com