drupal query sorted by keywords occurrence in matching result

With big help from @Fky & especially @Syscall I managed to change a drupal query to search within 3 tables instead of two and to add conditions as well as keyword split and removal of whitespaces.

drupal sql conditional clauses for multiple tables?

I would like the results 1) to be ordered by keywords occurrence of the matching results and then 2) sorted by title in ASC.

Ie. searching for "banana apple orange" to return:

banana orange
apple
banana
orange

I managed to order by title but can't figure out how to sort by keyword occurrences first?

$term = strip_tags(drupal_substr($_POST['keyword'], 0, 100));
$terms = explode(' ', $term); // split using ' '
$terms = array_map('trim', $terms); // remove unwanted spaces
$termsfiltered = array_filter($terms);
 $or = db_or();
  foreach ($termsfiltered as $term) {
    $or->condition('fd.field_detailed_question_value', '%'.db_like($term).'%', 'LIKE');
    $or->condition('fb.body_value','%'.db_like($term).'%' , 'LIKE');
    $or->condition('n.title','%'.db_like($term).'%' , 'LIKE');
    }
 $query = db_select('node', 'n');

$query->fields('n');
$query->range(0,20); //LIMIT to 15 records
$query->orderBy('title', 'ASC'); //ORDER BY title
$query->leftJoin('field_data_body' , 'fb', 'fb.entity_id=n.nid');
$query->leftJoin('field_data_field_detailed_question' ,'fd', 'fd.entity_id=n.nid');
$query->condition($or);
$query->condition('n.status','1');
$stmt = $query->execute(); // execute the query (returns the "statement" to fetch).

I found one question Sort sql result by occurence of a set of keywords that mentions

Take your where clause, replace all the or with +, and make sure each individual like the statement is wrapped in parenthesis, then order by it.

I also saw an example of using $query->addExpression...

Would that be the right approach/how to do it with drupal query? Help, please :)

Update: Or combine orderby with COUNT somehow?

Solution

The linked answer seens to be the right way to do this.

Creating the ORDER BY statement for Drupal query could be done like this:

$term = strip_tags(drupal_substr($_POST['keyword'], 0, 100));
$terms = explode(' ', $term); // split using ' '
$terms = array_map('trim', $terms); // remove unwanted spaces
$termsfiltered = array_filter($terms);

$order_array = [] ; // Create an empty array to create the string
$or = db_or() ;
foreach ($termsfiltered as $term) {

    // Or condition
    $or->condition('fd.field_detailed_question_value', '%'.db_like($term).'%', 'LIKE');
    $or->condition('fb.body_value','%'.db_like($term).'%' , 'LIKE');
    $or->condition('n.title','%'.db_like($term).'%' , 'LIKE');

    // Order by array (add the concat of each fields)
    $order_array[] = '(concat(title, fd.field_detailed_question_value, fb.body_value) like \'%'.db_like($term).'%\')';
}

$query = db_select('node', 'n');
$query->fields('n');
$query->range(0,20);
$query->orderBy(implode('+',$order_array), 'desc'); // Dynamic order by
$query->leftJoin('field_data_body' , 'fb', 'fb.entity_id=n.nid');
$query->leftJoin('field_data_field_detailed_question' ,'fd', 'fd.entity_id=n.nid');
$query->condition($or);
$query->condition('n.status','1');
$stmt = $query->execute(); // execute the query (returns the "statement" to fetch).

The adds are :

$order_array = [] ; before the loop
$order_array[] = '(concat...) inside the loop
$query->orderBy(implode('+',$order_array), 'desc'); changed

Note that you could prevent errors by using (or something like that) :

if (empty($termsfiltered)) { return "No matches."; }

Another thing. Using $_POST is not a good practice in Drupal. If you come from a form, you should use $form_state['values']['keyword'] instead of $_POST['keyword']: See this example.

edit

$term = strip_tags(drupal_substr($_POST['keyword'], 0, 100));
$terms = explode(' ', $term); // split using ' '
$terms = array_map('trim', $terms); // remove unwanted spaces
$termsfiltered = array_filter($terms);

$order_array = ['title'=>[],'question'=>[],'body'=>[]] ; // Create an empty array to create the string
$or = db_or() ;
foreach ($termsfiltered as $term) {

    // Or condition
    $or->condition('fd.field_detailed_question_value', '%'.db_like($term).'%', 'LIKE');
    $or->condition('fb.body_value','%'.db_like($term).'%' , 'LIKE');
    $or->condition('n.title','%'.db_like($term).'%' , 'LIKE');

    // Order by array (add the concat of each fields)
    $order_array['title'][] = '(title like \'%'.db_like($term).'%\')';
    $order_array['question'][] = '(fd.field_detailed_question_value like \'%'.db_like($term).'%\')';
    $order_array['body'][] = '(fb.body_value like \'%'.db_like($term).'%\')';
}

$query = db_select('node', 'n');
$query->fields('n');
$query->range(0,20);
$query->orderBy(implode('+',$order_array['title']), 'desc'); // Dynamic order by
$query->orderBy(implode('+',$order_array['question']), 'desc'); // Dynamic order by
$query->orderBy(implode('+',$order_array['body']), 'desc'); // Dynamic order by
$query->leftJoin('field_data_body' , 'fb', 'fb.entity_id=n.nid');
$query->leftJoin('field_data_field_detailed_question' ,'fd', 'fd.entity_id=n.nid');
$query->condition($or);
$query->condition('n.status','1');
$stmt = $query->execute();