Thread: simple web search
Hello, I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. What is the most elegant way to build a single query to match search words with multiple columns? Thanks,
> I'm considering implementing a search box on my review web site > http://lesculturelles.net and am looking for a simple way to match > entered words against several columns on related tables: > show.show_name, story.title, person.firtname, person.lastname, etc. one solution would be a view: create view search_v as select 'show'::name as tab_nm, show_id as tab_pk, 'Show Name' as description, show_name as searchfrom show union select 'story'::name, story_id, 'Story Title', title from story union ... your query would be select * from search_v where '$string' ilike search this would return a list the user could use to drill down further. many ways to skin this cat. ____________________________________________________________________________________ It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/
On Fri, Feb 23, 2007 at 10:01:22AM -0800, chester c young wrote: > > I'm considering implementing a search box on my review web site > > http://lesculturelles.net and am looking for a simple way to match > > entered words against several columns on related tables: > > show.show_name, story.title, person.firtname, person.lastname, etc. > > one solution would be a view: > > create view search_v as select > 'show'::name as tab_nm, > show_id as tab_pk, > 'Show Name' as description, > show_name as search > from show > union select > 'story'::name, > story_id, > 'Story Title', > title > from story > union ... > > your query would be > select * from search_v where '$string' ilike search > > this would return a list the user could use to drill down further. Thanks, this looks promising. The union and view ideas are indeed inspiring. What is that ::name cast for?
> > create view search_v as select > > 'show'::name as tab_nm, > > show_id as tab_pk, > > 'Show Name' as description, > > show_name as search > > from show > > union select > > 'story'::name, > > story_id, > > 'Story Title', > > title > > from story > > union ... > > > What is that ::name cast for? it's not needed here - sorry. name is the data type pg uses for table names &tc. it's frequently a good idea to cast to name when when messing around in the data dictionary. ____________________________________________________________________________________ TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. http://tv.yahoo.com/
Hello Louis-David, On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: > I'm considering implementing a search box on my review web site > http://lesculturelles.net and am looking for a simple way to match > entered words against several columns on related tables: show.show_name, > story.title, person.firtname, person.lastname, etc. > > What is the most elegant way to build a single query to match search > words with multiple columns? You may want to take a look at contrib/tsearch2. Joe
I think contrib/tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ is what you need. Oleg On Fri, 23 Feb 2007, chester c young wrote: >> I'm considering implementing a search box on my review web site >> http://lesculturelles.net and am looking for a simple way to match >> entered words against several columns on related tables: >> show.show_name, story.title, person.firtname, person.lastname, etc. > > one solution would be a view: > > create view search_v as select > 'show'::name as tab_nm, > show_id as tab_pk, > 'Show Name' as description, > show_name as search > from show > union select > 'story'::name, > story_id, > 'Story Title', > title > from story > union ... > > your query would be > select * from search_v where '$string' ilike search > > this would return a list the user could use to drill down further. > > many ways to skin this cat. > > > > > ____________________________________________________________________________________ > It's here! Your new message! > Get new email alerts with the free Yahoo! Toolbar. > http://tools.search.yahoo.com/toolbar/features/mail/ > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
On Fri, Feb 23, 2007 at 01:31:14PM -0500, Joe wrote: > Hello Louis-David, > > On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: > > I'm considering implementing a search box on my review web site > > http://lesculturelles.net and am looking for a simple way to match > > entered words against several columns on related tables: show.show_name, > > story.title, person.firtname, person.lastname, etc. > > > > What is the most elegant way to build a single query to match search > > words with multiple columns? > > You may want to take a look at contrib/tsearch2. Thanks Joe, I initially wanted to avoid dipping my toe into tsearch2 but it might be what I need after all :)
On Fri, 23 Feb 2007, Louis-David Mitterrand wrote: > On Fri, Feb 23, 2007 at 01:31:14PM -0500, Joe wrote: >> Hello Louis-David, >> >> On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: >>> I'm considering implementing a search box on my review web site >>> http://lesculturelles.net and am looking for a simple way to match >>> entered words against several columns on related tables: show.show_name, >>> story.title, person.firtname, person.lastname, etc. >>> >>> What is the most elegant way to build a single query to match search >>> words with multiple columns? >> >> You may want to take a look at contrib/tsearch2. > > Thanks Joe, I initially wanted to avoid dipping my toe into tsearch2 but > it might be what I need after all :) Don't be afraid, it's not so difficult to start. Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83