Active record FULLTEXT search MySQL using ORDER BY

[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 !

[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("");.

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

Thanks !