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`
