I'm having a hard time to translate the following query into kohana's ORM.
So, if I do the following works fine:
$query = DB::query(Database::SELECT, 'SELECT id_book, MATCH(title, author, isbn) AGAINST (:str) AS score FROM tab_books WHERE status = 1 AND MATCH(title, author, isbn) AGAINST (:str) HAVING score > '.$score.' ORDER BY score DESC LIMIT 100');
However, I need to use an specific class model. So far I have:
$books = new Model_Book();
$books = $books->where('status', '=', 1);
$books = $books->where(DB::expr('MATCH(`title`,`author`,`isbn`)'), 'AGAINST', DB::expr("(:str)"))->param(':str', $search_terms);
Which works fine, except for the fact that I'm unable to use the score value. I need the score because since I changed the table engine to InnoDB, the 2nd query is returning a lot of results.
ORM here: https://github.com/kohana/orm/blob/3.3/master/classes/Kohana/ORM.php
Thank you for your time.
So, you don't use query builder, but ORM object finding. In first case you take result array on second array of objects.
Trust me, you don't want use list objects. (It's extremely slow)
$sq = DB::expr('MATCH(title, author, isbn) AGAINST (:str) AS score')
->param(":str", $search_terms);
$wq = DB::expr('MATCH(title, author, isbn)');
$query = DB::select('id_book', $sq)
->from('tab_books') // OR ->from($this->_table_name) for model method
->where('status','=',1) ->where($wq, 'AGAINST ', $search_terms)
->order_by('score', desc)->limit(100) //->offset(0)
->having('score', '>', $score);
$result = $query->execute()->as_array();
for query test:
die($query->compile(Database::instance()));
OT: Use
$books = ORM::factory('Book')->full_text($search_terms, $score);
instead $books = new Model_Book();