Active record FULLTEXT search MySQL using ORDER BY

Active record FULLTEXT search MySQL using ORDER BY - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active record FULLTEXT search MySQL using ORDER BY (/showthread.php?tid=40932)

Active record FULLTEXT search MySQL using ORDER BY - El Forum - 04-22-2011

[eluser]Madoc[/eluser]
Hi all,

I am trying to do a FULLTEXT search query through Active records and I struggle on the ORDER BY. I want to order the records by FULLTEXT search score. For that I need to use the MATCH .. AGAINST statement on my select (or maybe on my order by).

But when I try to do so, active record adds single quotes to the MATCH word and I do not know how to get out of this. Here is my model code:

Code:
$match_sql = 'MATCH (name,description,tags,address1,address2) AGAINST(\''.$string_search.'\')';

        //SELECT FIELDS

        $this->db->select(array(

            'venue.id',

            'venue.name',

            'venue.description',

            'venue.address1',

            'venue.address2',

            'venue.postcode',

            'venue.slug',

            'venue.rating',

            'city.id as city_id',

            'city.name as city_name',

            'region.name as region_name',

            'country.name as country_name',

            $match_sql . ' as search_score'

            ));

        //FROM

        $this->db->from('venue');

        $this->db->join('city','venue.city_id = city.id');

        $this->db->join('region','city.region_id = region.id','left');

        $this->db->join('country','country.id = region.country_id','left');

        $this->db->where($match_sql, NULL, FALSE);

        //extra where to only get approved venues

        $this->db->where('venue.approved_date is not null');

        $this->db->order_by("search_score", "desc");

For people insterested, the output error is:

Code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('pool') as search_score FROM (`venue`) JOIN `city` ON `venue`.`city_id` = `city' at line 1

SELECT `venue`.`id`, `venue`.`name`, `venue`.`description`, `venue`.`address1`, `venue`.`address2`, `venue`.`postcode`, `venue`.`slug`, `venue`.`rating`, `city`.`id` as city_id, `city`.`name` as city_name, `region`.`name` as region_name, `country`.`name` as country_name, `MATCH` (name,description,tags,address1,address2) AGAINST('pool') as search_score FROM (`venue`) JOIN `city` ON `venue`.`city_id` = `city`.`id` LEFT JOIN `region` ON `city`.`region_id` = `region`.`id` LEFT JOIN `country` ON `country`.`id` = `region`.`country_id` WHERE MATCH (name,description,tags,address1,address2) AGAINST('pool') AND `venue`.`approved_date` is not null ORDER BY `search_score` desc

Many thanks !

Active record FULLTEXT search MySQL using ORDER BY - El Forum - 04-22-2011

[eluser]Victor Michnowicz[/eluser]
Some of the active record methods allow you to pass a parameter (FALSE) in order to prevent the text from getting escaped. If I were you I would ditch active records for this query. Just put all your SQL in a $this->db->query("");.

Active record FULLTEXT search MySQL using ORDER BY - El Forum - 04-23-2011

[eluser]Madoc[/eluser]
I resigned myself to use non active records queries as you suggested Sad

Thanks !