[ic] Does ORDER BY rand() work in sql query ?

[interchange-users@icdevgroup.org (Mike Heins)]

Quoting Steffen Dettmer (steffen@dett.de):
> * Kevin Walsh wrote on Sat, Jun 29, 2002 at 10:47 +0100:
> > > as "select * from products
> > >      where PurchasedZip LIKE 10101  ORDER BY rand() LIMIT 500"
> > > and I can do the same search in IC with no problem if I use the following
> > > form with the exception of the "rand()" sorting:
> > > 
> > >  <form action="[process-search]" method="post">
> > >  <input type="hidden" name="mv_searchtype" value="sql">
> > >  <input type="hidden" name="mv_matchlimit" value="500">
> > >  <input type="text" size="20" name="searchzip" value="">
> > >  <input type="hidden" name="mv_sql_query" value="select * from products
> > >      where PurchasedZip LIKE searchzip ORDER BY rand() LIMIT 500">
> > >  <input type="submit" name="mv-todo" value="Search">
> > >  </FORM>
> > > 
> [...]
> > Instead of this, Interchange does a "SELECT * FROM yourtable" and
> > then parses the results using the SQL::Statement module and your
> > "SQL". I'm quoting "SQL" here because the SQL::Statement module is
> > not a full SQL parser - as you have found.
> 
> Does that mean, that IC fetches *all* product records from DBMS
> in that case?! Would be horrible slow!

Yes, but if you have an "eq" spec in a coordinated search, or use
mv_like_spec, it will use those to fetch only the records that
match those.

This can be quite fast for searching a category, because you
can use SQL to perform the fast select of the indexed category
and use MV's multi-field search stuff to do the rest:

      [page href=scan
	    arg="
		co=yes
		sf=category
		se=Tools
		op=eq
	
		sf=:*
		se=[value searchterm]
		op=rm
	    "]Search</A>

Otherwise you can use a query...there are ways to pass variables
to template a search.

   [value name=searchterm1 set="foo"]
   [value name=searchterm2 set="bar"]

   [query
	list=1
	values="searchterm1 searchterm2"
	sql="
		SELECT *
		FROM products
		WHERE field1 = '%s'
		WHERE field2 = '%s'
	"]
   [sql-param field1]
   [sql-param field2]
   [/query]

Of course if you need something more elaborate than that, perhaps
conditional upon contents of the search terms, you can build your own
query term in embedded perl.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike@perusion.com>

"Laughter is inner jogging." -- Norman Cousins