Skip to content

Instantly share code, notes, and snippets.

@mkwsra
Created December 25, 2021 14:40
Show Gist options
  • Save mkwsra/79a075f50bbcfe713169ed80946d11ee to your computer and use it in GitHub Desktop.
Save mkwsra/79a075f50bbcfe713169ed80946d11ee to your computer and use it in GitHub Desktop.
Laravel - Code snippet - SQL REGEXP search with users full name not only first & last
namespace App\Http\Controllers;
// ....
public static function sanitizeAndPrepareForRegexSearch($searchQuery)
{
// General input sanitization
$sanitized = trim(filter_var($searchQuery, FILTER_SANITIZE_STRING));
// Reserved chars for regex operator
$excludedChars = config('app.excluded_chars'); // ['(', ')', '[', ']', '#', '@', '?', '!', '_', '-', '/', '\\', '^', '~']
$searchQuerySanitized = str_replace($excludedChars, '', $sanitized);
// Optional manipulations:
// tweaking the query to meet better results. like replacing '+' with the translated word 'plus'
$searchQuerySanitized = trim(str_replace('+', ' '.trans('strings.plus_word').' ', $searchQuerySanitized));
// Prepare for Regex search by replacing each space with pipe.
$searchQueryRegex = preg_replace('/\s* \s*/', '|', $searchQuerySanitized);
return $searchQueryRegex;
}
// Main example.
if ($request->has('q') && ($searchKeyword = $request->input('q'))) {
$searchQuery = Controller::sanitizeAndPrepareForRegexSearch($searchKeyword);
$yourModel = $yourModel->whereRaw($columnName.' REGEXP "'.$searchQuery.'"');
}
// 2nd example. User search by full name.
$users = User::newModelInstance();// newModelInstance() or any other function/scopes/where closure(s).
if ($request->has('q') && ($searchKeyword = $request->input('q'))) {
$searchQuery = Controller::sanitizeAndPrepareForRegexSearch($searchKeyword);
$users = $users->searchInName($searchKeyword, $searchQuerySanitized);
}
/**
* @param $query
* @param $searchQuerySanitized
* @param $implodeSearchQuery
* @param bool $isFull
*/
public function scopeSearchInName($query, $searchQuerySanitized, $implodeSearchQuery, $isFull = true)
{
if ($searchQuerySanitized) {
if ($isFull) {
if (is_null($this->last)) {
$query->where(function ($query) use ($implodeSearchQuery) {
$query->whereRaw('users.first REGEXP "'.$implodeSearchQuery.'"');
$query->orWhereRaw('users.last REGEXP "'.$implodeSearchQuery.'"');
});
$query->orderByRaw('FIELD(CONCAT_WS(\' \',first,last), "'.$searchQuerySanitized.'") desc');
$query->orderByRaw('FIELD(first, "'.$this->first.'") desc');
$query->orderByRaw('FIELD(last, "'.$this->first.'") desc');
} else {
$query->whereRaw('concat_ws(\' \',first,last) REGEXP "'.$implodeSearchQuery.'"');
$query->orderByRaw('FIELD(CONCAT_WS(\' \',first,last), "'.$searchQuerySanitized.'") desc');
$query->orderByRaw('FIELD(first, "'.$this->first.'") desc');
$query->orderByRaw('FIELD(last, "'.$this->last.'") desc');
}
} else {
$query->whereRaw('concat_ws(\' \',first,last) REGEXP "'.$implodeSearchQuery.'"');
$query->orderByRaw('FIELD(CONCAT_WS(\' \',first,last), "'.$searchQuerySanitized.'") desc');
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment