Request for New Feature: Integration of "whereLike & whereHasLike" Syntax in Eloquent Builder

[Raza9798]

I am writing to share an idea that I believe would greatly enhance the functionality of Eloquent Builder, specifically concerning search queries utilizing the "whereLike" functionality.

Currently, in my projects, I've been utilizing a custom scope named "whereLike" within my models to facilitate search operations based on partial matches across multiple columns. Here's a snippet of the implementation I've been using:

    public function scopeWhereLike($query, $columns, $q){
        return $query->orwhere(function ($query) use ($columns, $q) {
            foreach ($columns as $column) {
                $query->orWhere($column, 'like', '%'.$q.'%');
            }
        });
    }

    public function scopeWhereHasLike($query, $relation, $columns, $q){
        return $query->orwhereHas($relation, function ($query) use ($columns, $q) {
            foreach ($columns as $column) {
                $query->Where($columns[0], 'like', '%'.$q.'%')->orwhere($column, 'like', '%'.$q.'%');
            }
        });
    }

This approach has been effective, but I envision an even more streamlined and expressive solution by integrating a native "whereLike" syntax directly into the Eloquent Builder.

The proposed syntax could potentially resemble the following:

 return Receiving::with("fk_vendor_id")
        ->whereLike(["invoice_no"], $request->q)  // custom scope for like
        ->WhereHasLike("fk_vendor_id", ["name", "primary_phone"], $request->q) // custom scope for relational like
        ->leftJoin('vendors', 'receivings.fk_vendor_id', '=', 'vendors.id') 
        ->selectRaw("receivings.*,  $request->column as text_column, CONCAT(invoice_no, ' : ', vendors.name, ' : ', vendors.primary_phone) as text")
        ->groupBy("$request->column")
        ->get()
        ->take(10);

By incorporating this feature directly into Eloquent Builder, it would not only simplify the codebase but also enhance readability and maintainability across projects. This native support for "whereLike" queries would undoubtedly streamline the development process for search functionality, making it more intuitive for developers to implement and maintain.

I believe this addition would be immensely beneficial to the Laravel community, as it aligns with the framework's ethos of promoting elegant and efficient solutions for common development tasks.

I am excited about the potential of this feature and would greatly appreciate your consideration in integrating it into future releases of Laravel.

[ghost]

Hi. Side note: like '%fff% is not recommended because it can't use indexes. If you need that you might be using fulltext search instead (this is also not builtin eloquent).

[ghost]

@Raza9798 Fulltext search is ok for searching words or words starting with. If you need to search bcd in abcdef then full text search is not the answer.

[Raza9798]

@marius-mcp the request I posted to integrate was like which is for searching data bcd in abcdef any how thanks for the replies where i learned about Fulltext and indexing mechanism. would you think that the request I made was helpful for developers who are working with like queries?

[ghost]

@Raza9798 You will see what others will reply.

[shaedrich]

To avoid having '%fff%' everywhere, there should also be shortcuts for whereStartsLike() ('fff%') and whereEndsLike() ('%fff'), similar to str_pad(). Perhaps, there even might be whereBetweenLike() ('f%f')

[shaedrich]

For full-text search, database engines tend to have their own implementations:

• DB2: contains
• MySQL: match … against
• Oracle: contains
• PostgreSQL: @@/WildSpeed
• SQL Server: contains

(Source: https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning)

So, a common denominator would be something like whereContains()

[Raza9798]

As we get Upvotes for this discussion I am closing this discussion and creating an issue for the implementation process
ISSUE : #50331

[ghost]

This is not an issue...

[Raza9798]

we create issues on GitHub to make PR therefore we can track the progress of the ticket @marius-mcp issues are not only for the bug reporting.

[Raza9798]

@shaedrich

To avoid having '%fff%' everywhere, there should also be shortcuts for whereStartsLike() ('fff%') and whereEndsLike() ('%fff'), similar to str_pad(). Perhaps, there even might be whereBetweenLike() ('f%f')

Building such syntax could helps to resolve the code duplication in our codebase, mainly their should be two search cases direct column search and relational column search. both cases might have same syntax with the little differences as you mentioned whereStartsLike, whereEndsLike for search like queries, we can optimize it with a single syntax as whereLike using the parameter like below, we can use the default parameter as full

User::whereLike('column', 'start', $request->query)->get();         // %ABC
User::whereLike('column', 'end', $request->query)->get();          // BCD%
User::whereLike('column', 'full', $request->query)->get();        // %ABCD%

as the same method mentioned above if developers needs to search on relationship we could use as below

User::whereHasLike('relationship', 'column',  'start', $request->query)->get();
User::whereHasLike('relationship', 'column',  'end', $request->query)->get();
User::whereHasLike('relationship', 'column',  'full', $request->query)->get();

@shaedrich Do you suggesting any improvements

[shaedrich]

I think, if they are gonna be implemented, there'd be no harm in supporting both syntaxes, one being a shortcut/fallback for the other.

Will 'full' be the default and therefore optional (e.g. User::whereLike('column', 'full', $request->query)->get(); would equal User::whereLike('column', $request->query)->get();?

[Raza9798]

I think, if they are gonna be implemented, there'd be no harm in supporting both syntaxes, one being a shortcut/fallback for the other.

Will 'full' be the default and therefore optional (e.g. User::whereLike('column', 'full', $request->query)->get(); would equal User::whereLike('column', $request->query)->get();?

Yes when it is default it should be like
User::whereLike('column', $request->query)->get();
User::whereHasLike('column', $request->query)->get();