如何使用多个关键字Laravel 5从数据库中获取最相关的条目并对其进行排序 [英] How to get and order the most relevant entries from the database with multiple keywords Laravel 5

问题描述

我在处理多个关键字以及根据相关性查询数据库时遇到了麻烦.我想搜索每一行，如果根据我选择的列，每行匹配的关键字超过1个，则首先对这些条目进行排序.

I am having trouble handling multiple keywords and querying the database based on relevance. I want to search each row and if there is more than 1 keyword per row matching based on my selected columns, to sort these entries first.

我确实有一些工作，但是它只是拉出所有带有关键字的条目，这些关键字没有特定的顺序或相关性.

I do have something working, but it just pulls all entries with a keyword present in a column in no specific order or relevance.

以这个工作示例为例:

$search_terms = array('York', 'North Yorkshire');

$properties = Property::where(function ($q) use ($search_terms) {
            foreach ($search_terms as $value) {
                $q->orWhere('address1', 'like', "%{$value}%");
                $q->orWhere('address2', 'like', "%{$value}%");
                $q->orWhere('postcode', 'like', "%{$value}%");
                $q->orWhere('city_town', 'like', "%{$value}%");
                $q->orWhere('county', 'like', "%{$value}%");
            }
        })->paginate(25);

这有效，并拉回所有包含在我选择的任何列中的关键字的所有条目.在这种情况下，来自city_town列的约克和来自county列的北约克郡.

This works and pulls back all entries with the keywords present in any of my selected columns. In this instance York from the city_town column and North Yorkshire from the county column.

我需要查询来检查这些关键字的每一行，并带回出现这些关键字 ALL 的条目，然后是一个或多个出现的条目(我的示例现在是这样做的) ).

I need the the query to check each individual row for these keywords and bring back entries where ALL of these keywords are present, followed by where one or more is present afterwards (my example does this now).

在此先感谢任何可以提供帮助的人.

Many thanks in advance to anyone who can help.

推荐答案

好的，也许有些SQL魔术师可以为您提供更好的SQL解决方案.但是直到那时...

Okay maybe some SQL magician could give you a better SQL solution. But until then...

这就是我要使用Laravel 收藏(与php):

This is how I would do it with Laravel collections (sorting with php):

$search_terms = array('York', 'North Yorkshire');

$properties = Property::where(function ($q) use ($search_terms) {
            foreach ($search_terms as $value) {
                $q->orWhere('address1', 'like', "%{$value}%");
                $q->orWhere('address2', 'like', "%{$value}%");
                $q->orWhere('postcode', 'like', "%{$value}%");
                $q->orWhere('city_town', 'like', "%{$value}%");
                $q->orWhere('county', 'like', "%{$value}%");
            }
        })->paginate(25);

$props = ['address1', 'address2', 'postcode', 'city_town', 'county'];

$properties = $properties->sortByDesc(function($i, $k) use ($search_terms, $props) {
    // The bigger the weight, the higher the record
    $weight = 0;
    // Iterate through search terms
    foreach($search_terms as $searchTerm) {
        // Iterate through properties (address1, address2...)
        foreach($props as $prop) { 
            // Use strpos instead of %value% (cause php)
            if(strpos($i->{$prop}, $searchTerm) !== false)
                $weight += 1; // Increase weight if the search term is found
        }
    }

    return $weight;
});

$properties = $properties->values()->all();

这篇关于如何使用多个关键字Laravel 5从数据库中获取最相关的条目并对其进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！