XF 2.0 Extended SQL query sorting

[Lukas W.]

I'm working on an autocomplete functionality that gives a bit more freedom and is capable of doing a better search than a static search with MySQLs 'LIKE' by using the levenstein distance.

The current query is:

$finder
    ->where('font_id', 'like', $finder->escapeLike($q, '%?%'))
    ->active()
    ->fetch(10);

I know this would be potentially possible to achieve by using an engineered SQL clause to search for everything with a levenstein distance smaller than a given threshold. But that is not as forgiving as I'd like to have it. If instead, I would be able to sort by the distance in ascending order, that would deliver better results. Is there a way to do this or would I need to run a direct SQL query?

 

[Jaxel]

You can use ->whereSql() to manually put in clauses.

BTW, what does active() do?

 

[Lukas W.]

You can use ->whereSql() to manually put in clauses.

I know this would be potentially possible to achieve by using an engineered SQL clause to search for everything with a levenstein distance smaller than a given threshold.

That was, what I was actually trying to say there. Thing is, that it still will only work for a certain threshold, because I can only pick all elements that are smaller than a given threshold in such a where statement, and also won't have the actual sorting by the distance. What whereSql gives me is a set that is "close enough" to slip through the threshold, but not even closesly in order or anything.

BTW, what does active() do?

Nothing fancy.

public function active() {
    $this->where('active', '=',1);

    $this->setDefaultOrder('font_id', 'ASC');

    return $this;
}

 

[Mike]

MySQL doesn't have a built in Levenshtein distance function (though I believe there are potential alternatives: http://www.artfulsoftware.com/infotree/qrytip.php?id=552), but in the absence of that, you're really going to have to select all results and do filtering/ordering in PHP.

If you can calculate distance in MySQL, then you can use that in the order clause (via a finder expression).

 

[Lukas W.]

MySQL doesn't have a built in Levenshtein distance function

I was so sure it does... don't know how I came to that belief. Thanks for pointing that out. It's about 500 items, so doing it in php should be feasible then.

If you can calculate distance in MySQL, then you can use that in the order clause (via a finder expression).

Thanks, that'll come in handy.