SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: joins and full-text search
-
Jun 1, 2009, 23:20 #1
- Join Date
- Mar 2005
- Location
- Saudi Arabia
- Posts
- 1,724
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
joins and full-text search
Hi,
I'm basically trying to perform a join on multiple tables, search the records (after the join), and sort results according to relevance.
I'm not sure how to do this since I calculate the relevance in the beginning of the SELECT statement like this:
Code:SELECT table_name.*, MATCH(table_name.field) AGAINST('something' IN BOOLEAN MODE) AS relevance
-
Jun 2, 2009, 04:45 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
what exactly is the issue? i don't see any join there...
-
Jun 2, 2009, 05:28 #3
- Join Date
- Mar 2005
- Location
- Saudi Arabia
- Posts
- 1,724
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Sorry for not explaining it better.
The issue is that I don't know how to include fields from the join in the search. If I do this for example:
Code MySQL:
I get an "unknown column" error.
-
Jun 2, 2009, 07:30 #4
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
you can't do that. MATCH() requires all columns to be from the same table.
try this:
Code:SELECT table_name.* , MATCH(table_name.FIELD) AGAINST('something' IN BOOLEAN MODE) * MATCH(table2_name.FIELD) AGAINST('something' IN BOOLEAN MODE) AS relevance FROM table_name LEFT OUTER JOIN table2_name ON table2_name.id = table_name.foreign_key
Check out our new Industry News forum!
Keep up-to-date with the latest SP news in the Community Crier
I edit the SitePoint Podcast
-
Jun 2, 2009, 08:01 #5
- Join Date
- Mar 2005
- Location
- Saudi Arabia
- Posts
- 1,724
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks longneck.
By the way, is that the best way to search MySQL records? I keep duplicating tables (since I use InnoDB) just to use full-text search, and it's getting kind of tiresome.
-
Jun 2, 2009, 11:16 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Bookmarks