[ic] Secondary ITL searches on ITL searches/queries - For your Toolbox

[Paul Jordan]

Hi List
I thought this was important enough to send to the list, as I think this
went pretty much unnoticed in 2004. Mike Heins added the ability to run
secondary ITL searches off an initial loop or sql query. Here is part of his
commit:
---------------
User:      heins
Date:      2004-07-23 03:03:40 GMT
* Make query results available for filtering RefSearch. For example:
	[query 	search-label=foo
		row-count=1
		sql="
			select * from products where prod_group = 'Hand
Tools'
		" /] entries in prod_group=Hand Tools

	<p>Searching the reference for category=Shears...</p>

	[loop search="
			st=ref
			re=foo
			co=yes
			sf=category
			se=Shears
			rf=*
		" ]

	[loop-param sku]<br>
		-- prod_group=[loop-param prod_group]<br>
		-- category=[loop-param category]<br>
	[/loop]
-------------------------------

I found this to be a great addition (and I just found it today!). Basically
keep this in mind for the similar situations...

I frequently use the [query] tag and do moderately complex searches for CRM
type reproting and displaying. This is fine using the power of SQL however
there comes a point at which, if you want free form searching on the data,
that you have to bow to the limitations of your SQL database's searching
ability. I use MYSQL, and FULL TEXT searching is not that easy (you need
special indexes) and especially if you are JOINing 3 or 4 tables!

So, I solved this by doing a complex but efficient [query] joining tables
and parring down results, then using the above loop to freeform text search
across all fields. I think doing everything in Mysql would have been a
terrible feat and probably just as (in)efficient as this way.

So, keep this in your toolbox -

Also, I am thinking of asking a consultant about the possibility of doing a
[timed-build] for a SearchRef. For example, my above situation was for an
address book. I was contemplating using timed-build for each users address
book, this would have produced an output file (html,resutls,etc) and I could
have displayed it efficiently, rebuilding only on infrequent addressbook
edits. However, this would not work well because I'd not be able to search
on that SearchRef (there would be no SearchRef using a timed-build, simply
the output). So, I was thinking of the what-if of having a timed-build
SearchRef. Slightly less efficient than a timed-build of output however one
can use Mike's post loop's to search on the data...

Basically something to solve frequent searches of a set, set of results. Why
query the DB everytime I want to display the useres addressbook (email
application)? And further, why lose the efficiency of timed-build if you
want to search that addressbook?

Anybody have any comments?

Paul