Search relevance
| From: | Ron Piggott | Date: | Tue, 15 Feb 2011 21:55:16 +0000 |
| Subject: | Search relevance | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-47681@lists.php.net to get a copy of this message | ||
I would like to have the search results display in order of relevance --- and essentially add these
two lines to the query.
HAVING relevance > 0.2
ORDER BY relevance DESC
However with how I built the database and the search spanning several tables I am unsure how to do
this. Could someone help me please?
Ron
SELECT
ministry_profiles.reference,
ministry_profiles.organization,
ministry_profiles_activity.activity
FROM
(
(
( ministry_profiles LEFT OUTER JOIN ministry_profiles_listing_details ON
ministry_profiles.reference =
ministry_profiles_listing_details.ministry_profile_reference )
LEFT OUTER JOIN ministry_profiles_activity ON
ministry_profiles.reference =
ministry_profiles_activity.ministry_profiles_reference )
LEFT OUTER JOIN ministry_profile_categories ON
ministry_profiles.reference =
ministry_profile_categories.ministry_profiles_reference )
LEFT OUTER JOIN ministry_categories ON
ministry_profile_categories.ministry_categories_reference =
ministry_categories.reference
WHERE
(
MATCH( ministry_profiles.organization,
ministry_profiles.address_line_1,
ministry_profiles.address_line_2,
ministry_profiles.city,
ministry_profiles.province_state,
ministry_profiles.postal_zip_code,
ministry_profiles.country,
ministry_profiles.telephone,
ministry_profiles.toll_free,
ministry_profiles.fax, ministry_profiles.email,
ministry_profiles.website )
AGAINST
('$search')
AND
ministry_profiles.live =1
)
OR
(
MATCH( ministry_categories.category )
AGAINST
('$search')
)
OR
(
MATCH( ministry_profiles_activity.activity )
AGAINST
('$search')
AND ministry_profiles_activity.live =1
)
OR
(
MATCH( ministry_profiles_listing_details.contact,
ministry_profiles_listing_details.year_founded,
ministry_profiles_listing_details.volunteer_opportunities,
ministry_profiles_listing_details.employment_opportunities,
ministry_profiles_listing_details.members_of,
ministry_profiles_listing_details.major_events,
ministry_profiles_listing_details.associate_member_of,
ministry_profiles_listing_details.registration_number,
ministry_profiles_listing_details.fund_raising,
ministry_profiles_listing_details.accredited_by,
ministry_profiles_listing_details.facebook,
ministry_profiles_listing_details.twitter )
AGAINST
('$search')
AND
ministry_profiles_listing_details.live =1
)
GROUP BY ministry_profiles.reference