Wednesday 13 July 2016

How to Order the Select Query results based on Search Text Expression in Drupal 7?

Let us consider as user contents are stored in a indexed field "associatedetails" as below:
Row1 Contains : "AjayKumar Gudivada";
Row2 Contains : "Ajay Kumar Gudivada";
Row3 Contains : "Gudivada";
Now, if a user searches for text "Kumar", we need to show only the results where the searched text exists. And show the results where the searched text matches starting of word in priority and display the rest.
$searchtext = "Kumar";
$query = db_select('custom_users', 'u');
    $query->fields('u', array('email', 'phone_number', 'mobile_number', 'work_location'));
    $c = db_or()
        ->condition('associatedetails', db_like($searchtext) . '%', 'LIKE')
        ->condition('associatedetails', '% ' . db_like($searchtext) . '%', 'LIKE');
    $query->condition('status', 1, '=');
    $query->condition($c);
    $query->addExpression('CASE WHEN associatedetails LIKE :db_condition_placeholder_1 THEN 2 WHEN associatedetails LIKE :db_condition_placeholder_2 THEN 1 ELSE 0 END','order_col');
    $query->orderBy('order_col','DESC');
    $query->range(0, 10);
    $result = $query->execute();
Now, the results will show as Row2 in first position and Row1 in second position.

No comments:

Post a Comment

Your comment is so valuable as it would help me in my growth of knowledge.