Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

newbie360's avatar

any chance to make Laravel search orderBy similarity

something like this

$min_percent = '80';

Model::search($keyword)->orderBySimilarity($min_percent, 'desc')

result only include 100% to 80% and is sorted by desc

row1 (100%)
row2 (95%)
row3 (90%)
row4 (80%)

PHP function similar_text() http://php.net/manual/en/function.similar-text.php

may be can't do this with only use query ? what about

// 1. get all data from db

// 2. foreach result set calculate the similarity between (keyword , eachVarcharColumn)
similar_text($keyword, $column1, $percent1); 
similar_text($keyword, $column2, $percent2);

if ($maxPercent = max($percent1, $percent2) > $min_percent)
    $match[$id] = $maxPercent;

// 3. Sort an array in reverse order and maintain index association
arsort($match);

// 4. implode the key of $match, used on WHERE id IN ($idOrder)
$idOrder = implode(",", array_keys($match));

// 5. finally get your result something like this
SELECT * FROM `table` WHERE `id` IN ($idOrder) ORDER BY FIELD(`id`, $idOrder)

anyone doing like this ? or suggest a new method ? Thankyou so much

0 likes
6 replies
cre3z's avatar

If I understand you right you want to write a query where you you have a min_percent of say 80% and then you want to retrieve results from your db of 80% and higher? and display them in DESC order?

I think I would just do a where clause

$min_percent = '80';

Model::search($keyword)->where('percentage_column', '>=', $min_percent)->orderBy('percentage_column', 'desc')->get();
newbie360's avatar

@cre3z hmmm sorry no, may be i make an example

//users table
id | name
1 | peter
2 | tt
.
.
9999 | pe
.
10538 | something

keyword = "pe"

normally we write the query is WHERE 'name' LIKE '%$keyword%'

ok if many record match %pe% and orderBy any field asc/desc is nonsense

the record id 9999 "pe" may be showing on pagination 300

but id 9999 is 100% = keyword

so is make sense id 9999 this record MUST showing first on your screen

9999 | pe (100% match keyword)
1 | peter (may be 85% match)
10538 | something (may be 10% match)

if take too long for each request, may be catch the keyword into a table

// catch_keyword table
id | keyword | sql

so if client submit the keyword and found in the table, just use the sql

sl0wik's avatar

I assume you are trying to full-text search on a column.

  1. Google 'MySQL match against' + check https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html. You will need to play with eloquent 'raw' because it's not supported natively.
  2. If you want to do text search like a pro check elastic search.

Sorry for a non direct response to your question.

newbie360's avatar

@leber yea at least i know eloquent can't do this alone.

Thankyou for your information about fulltext

Please or to participate in this conversation.