[thelist] Nested Queries (long example code)

[Joshua Olson]

----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Wednesday, August 21, 2002 6:02 PM
Subject: Re: [thelist] Nested Queries (long example code)


> > Any questions?
>
> just one
>
> what the heck was that???

:-)  You make me laugh.  Actually, this is a coding of the psuedo-code I
discussed earlier:

- select checked email addresses
- select checked groups

- loop:
   - place selected email addresses into email dictionary object
   - place selected groups into group dictionary object
   - is group dictionary object empty?
     - no, pull one group out of dictionary and select all emails and
sub-groups for that group.  Remove group from dictionary object.
     - yes, jump out of loop
- repeat loop

- send email to everybody in email dictionary object

> no offence, i was making a light-hearted joke  ;o)
>
> > the underlying logic is digestible by the average programmer
>
> then i must be way below average

I guess you had to remember the psuedo-code then.

> any time i see coldfusion logic which takes query results and plays around
> with them, in arrays and structures and whatnot, my eyes start to bleed,
> because that sort of manipulation is usually not necessary
>
> then if i see a SELECT inside a loop, i know i'm in for a rough ride

In this case, it probably is a bit overkill and not optimized.  However, I
do not intend on exploring better architectural options in the same breath
as presenting this code, since optimization is not my goal in this case.

<big breath>

> plus, when there is a one-to-many structure horizontally within each row,
> i.e. not even first normal form, this does not bode well for query
> simplicity or efficiency

It's not really any normalization that I know of either.  It's a simple
many-to-many relationship with only one table of data and one table handling
the actual relationshipships.

> any chance i could talk you into a better structure?

Well, yeah.  But, not in this same breath.  :-)

> if so, could you please walk me through this? you have emailids and
groups,
> right? and the groups can be included in other groups? can one group be
> included in more than one other group (i.e. is it a group hierarchy or a
> many-to-many)? can an emailid belong to more than one group?

No, yes, yes, yes, and yes.

The walk-through is in the psuedo-code.  Basically, however, it's doing a
brute force attack on finding all the selected objects emails and group
assignments.  Then for those group assignments, finding it's sub-groups or
directly assigned emails... it's making one big list of emails and splitting
it out the back end.  Kinda like a horse does with hay.  :-)

I'd still like to get a coupla hours of one-on-one time with you--though I
doubt that will ever happen.

-joshua