[thelist] Nested Queries (long example code)

[Joshua Olson]

----- Original Message -----
From: "Feingold Josh S" <Josh.S.Feingold at irs.gov>
Subject: re: [thelist] Nested Queries

> Joshua:
> That's what I have been doing.  Can you offer some pseudocode based on
this
> structure?

Ok, I've decided to code this thing in CF and MS SQL 2k.

Here's the sample DB structure I'm using:

table "object"
~~~~~~~~~~~~~~~~~~~~~~~~~~~
id numeric(9) IDENTITY PRIMARY KEY,
is_group tinyint DEFAULT 0,
name varchar(100) NULL,
property_1 varchar(100) NULL,
property_2 varchar(100) NULL,
property_3 varchar(100) NULL,
...
property_x varchar(100) NULL

table "membership"
~~~~~~~~~~~~~~~~~~~~~~~~~~~
group_id numeric(9) NOT NULL,
member_id numeric(9) NOT NULL


Couple of things to note in this example, both groups and leaf objects are
stored in the same table and are differentiated by the flag "is_group"

Assume that form.object_ids is a comma delimited list of all the id's of the
objects we are interested in.

So, here's some CF code that works on this structure:

<cfset emails = StructNew()>
<cfset groups = StructNew()>
<cfset used_groups = StructNew()>

<cfquery name="get_objects" datasource="user_demo" dbtype="OLEDB">
  SELECT id, is_group
  FROM object
  WHERE id IN (#form.object_ids#)
</cfquery>

<cfloop query="get_objects">
  <cfif Val(is_group)>
    <cfset groups[id] = id>
  <cfelse>
    <cfset emails[id] = id>
  </cfif>
</cfloop>

<cfset not_done = "1">
<cfset i = "1">

<cftry>
  <cfloop condition="not_done">
    <cfif i GT "100">
      <cfthrow message="Error.  Recursion factor too deep... or possibly
some other type of error.">
    </cfif>
    <cfset group_id = ListFirst(StructKeyList(groups))>
    <cfset t = StructInsert(used_groups, group_id, group_id, "0")>
    <cfset t = StructDelete(groups, group_id)>
    <cfquery name="get_objects" datasource="user_demo" dbtype="OLEDB">
      SELECT id, is_group
      FROM object
      WHERE id IN (SELECT member_id
                   FROM membership
                   WHERE group_id = #group_id#)
    </cfquery>
    <cfloop query="get_objects">
      <cfif Val(is_group)>
        <cfset groups[id] = id>
      <cfelse>
        <cfset emails[id] = id>
      </cfif>
    </cfloop>
    <cfset not_done = StructCount(groups)>
    <cfset i = i + "1">
  </cfloop>
  <cfcatch></cfcatch>
</cftry>

<cfquery name="get_objects" datasource="user_demo" dbtype="OLEDB">
  SELECT property_1
  FROM object
  WHERE id IN (#StructKeyList(emails)#)
</cfquery>

<cfoutput query="get_objects">
  #property_1#<br />
</cfoutput>

~~~~~~~~~~~~~~~~~~~~~~~~~~~

The resultant output will be a list of "property_1" for all the objects we
explicitly selected as well as objects that are implicitly selected because
of group membership.

The used_groups structure, in case you are wondering, is only there to catch
cyclic grouping.  CF will throw an error when the key is added and will not
insert redundant groups back onto the stack after it's already been cleared.

Any questions?

-joshua

P.S.  I know this is not efficient by any means, but the underlying logic is
digestible by the average programmer, whereas the really neat solutions
require a lot more thinking.  :-)  I think I may make this into a article.