[thelist] CFQuery sub search...

[.jeff]

susan,

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: Susan Wallace
>
> Rudy gave me the answer to what I was trying to figure
> out, but as you all pointed out, there is a "catch". I
> found the limitation with the IN clause, but because of
> this data, I haven't been able to narrow it to less than
> 255 yet (to get that "Magic Number")  to see how to
> break up the list into chunks... I'm using MSSQL Server
> 7.
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

ah, a database server which uses sybase database engine, hence the 255 item
limit in the IN clause.  that's why i posted the chunked list tip to begin
with.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> This is a reasonable assumption, but because of the data
> that I am working with here, the initial search almost
> always returns a list well over 255 - sometimes in the
> thousands.
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

in writing the bit you're responding to, i actually assumed (correctly it
appears) that you would be getting records back in the number of the
thousands.  i still think that (thinking in general here) searches that
return records in the thousands is uncommon.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> (I can describe the app if you think it would help, I
> don't know of a better way to do this given the data I
> am working with...)
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

if you'd like to do that offlist i'm game.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> So, if I use the tip below, (chunkArray), where is the
> "catch"...
>
> If I follow you, it will take what I currently have:
>
> <input
>  type="hidden"
>  name="thepks"
>  value="#ValueList(Instrument.Title_ID)#">
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

yes, that's how you pass the list of ids to the following page.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> and instead of a hidden form field, make a variable
> and then make chunks, using this syntax:
>
> WHERE id IN (#chunkList#)
>
> put it back into my search.
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

no, you still need the hidden field to pass the list of ids from the
previous search.  also, you only need to include the list of ids in your
next search if the user selected "search within...", otherwise you just
abandon that data and start from scratch.

so, user does a search.  user does another search, this time checking the
"search within..." checkbox.  take the contents of the hidden form field and
run it through the code i posted in the tip.  take the variable and use it
in your WHERE clause:

WHERE (id IN (#chunkList#))

*if* your list of ids ends up getting broken up into chunks, then it will
get sent to the database server as multiple IN statements (thanks ron for
picking out the AND/OR syntax mistake):

WHERE (id IN (1,2,3,4,5,6,7,8,9,10)
   OR  id IN (11,12,13,14,15,16,17,18,19,20)
   OR  id IN (21,22,23,24,25,26,27,28,29,30))

in other words, #chunkList# will be equivalent to:

1,2,3,4,5,6,7,8,9,10)
   OR  id IN (11,12,13,14,15,16,17,18,19,20)
   OR  id IN (21,22,23,24,25,26,27,28,29,30

then, include the search criteria for the new search.  this step is
critical.  you want to limit the search by indexed columns first and then
perform your text matches.  this is faster than doing it the other way
around.

WHERE (id IN (#chunkList#))
  AND [search criteria]

does this make more sense?

good luck,

.jeff

http://evolt.org/
jeff at members.evolt.org
http://members.evolt.org/jeff/