MySql Query build using Kohana's ORM

Question

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.

bato3 bato3 · Accepted Answer · 2017-10-03T15:48:51

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();

MySql Query build using Kohana's ORM

1 Answers