[ic] Searching across multiple tables

Mike Heins interchange-users@interchange.redhat.com
Sat Feb 9 00:01:00 2002


Quoting Jimmy Kaplowitz (jimmy@kaplowitz.org):
> Hello Interchangers. I have two tables, a products table and an authors
> table, that are related many-to-many (by a glue table called bookauthors
> with a column for the products table and a column for the authors table,
> among others), and I want to do an interchange 'or' search on two
> fields from the products table and two fields from the authors table. how
> do I do this? My current search, which is only in one of the tables,
> looks like:
> 
> [set gensearch]os=yes/fi=products/sf=title/sf=description[/set]
> 
> (which is then specified as mv_profile and a search spec provided in
> mv_searchspec.)
> 
> I would also be glad to use a SQL query joining the various tables, but
> I don't know a way to use SQL directly except by using the [query] tag,
> which is for in-page searches and not forms or links, or by using the
> sq= one-click parameter, which IIRC uses the SQL::Statement module and
> parses the text database all the time, while I would prefer to use the
> MySQL SQL engine which I have set up to work with my database.
> 

There is actually a way to join Interchange searches -- it does require
a common primary key, though.


 [page
		search.0="
				sf=prod_group
				se=Tools
				"

		search.1="
				sf=description
				se=Set
		"

		search.2="
				sf=pricing:w10
				se=15
				op=<
				nu=1
		"
        ]Search two tables</A>


If you change the search spec on search.2 to 14, you will change the results.
Here are URLs into the demo for both:

    http://demo.ic.redhat.com/cgi-bin/ic2/scan/sf=prod_group/se=Tools/nu=0/os=0/ne=0/op=rm/bs=0/cs=0/sg=0/sf=description/se=Set/nu=0/os=0/ne=0/op=rm/bs=0/cs=0/sg=0/sf=pricing:w10/se=15/op=%3C/nu=1/os=0/ne=0/bs=0/cs=0/sg=0/co=yes.html

    http://demo.ic.redhat.com/cgi-bin/ic2/scan/sf=prod_group/se=Tools/nu=0/os=0/ne=0/op=rm/bs=0/cs=0/sg=0/sf=description/se=Set/nu=0/os=0/ne=0/op=rm/bs=0/cs=0/sg=0/sf=pricing:w10/se=14/op=%3C/nu=1/os=0/ne=0/bs=0/cs=0/sg=0/co=yes.html

The same thing can be achieved with a form, of course. If you use the
text search or op=eq on the db search, you can get reasonable speed.
Glimpse is fastest of all, of course, for large databases.

Again, this may not apply to your search and you may end up slicing and
dicing CGI values and putting them into a [query ...] tag.

-- 
Red Hat, Inc., 3005 Nichols Rd., Hamilton, OH  45013
phone +1.513.523.7621      <mheins@redhat.com>

Research is what I'm doing when I don't know what I'm doing.
-- Wernher Von Braun