[ic] Searching across multiple tables

Fri Feb 8 19:19:01 2002

On Fri, Feb 08, 2002 at 06:36:09PM -0500, Jimmy Kaplowitz wrote:
> 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.
> 
> (Wow. I just looked back at the above paragraph-sentence, and it is so
> formidable I don't want to mess with it. May you have better luck.)
> 
> All help will be greatly appreciated. Thank you.

There are too many possibilities and you've not really explained what
you want or what you have for infrastructure.  Oracle or mySQL, etc?

SQL is really poor for generalized text searching.  On the one hand
you might consider dumping the sql tables with a join on the authors 
into a big text file, indexing and searching that.  On the other, you
might massage your data to build a "search" field in the glue table.

FWIW, we have done a search on author|description|year|title
that spans multiple tables.  It was done with <shudder>[query]
tags and embedded perl.  That catalog has been running for four
years or so untouched; I don't even dare look at the code.

> 
> - Jimmy Kaplowitz
> jimmy@kaplowitz.org
> _______________________________________________
> interchange-users mailing list
> interchange-users@interchange.redhat.com
> http://interchange.redhat.com/mailman/listinfo/interchange-users

-- 

Christopher F. Miller, Publisher                               cfm@maine.com
MaineStreet Communications, Inc           208 Portland Road, Gray, ME  04039
1.207.657.5078                                         http://www.maine.com/
Content/site management, online commerce, internet integration, Debian linux