This query searches the data from the Table and also with its relation table columns dynamically.

//add this code in model

    public function scopeSearch($query, $keyword, $columns = [], $relationMapping = [])
    {
    
    // if you pass empty column list then it automatically get all table column or fillable column    
        if (empty($columns)) {
        
    // 1) get all table column

    //            $columns = array_except(
    //                Schema::getColumnListing($this->table), $this->guarded
    //            );
    
    // 2) get fillable column
            $columns = $this->fillable;
        }

        $query->where(function ($query) use ($keyword, $columns, $relationMapping) {
            foreach ($columns as $key => $column) {
                $clause = $key == 0 ? 'where' : 'orWhere';
                $query->$clause($this->table.'.'.$column, "LIKE", "%{$keyword}%");

                if (!empty($relationMapping)) {
                    $this->filterByRelationship($query, $keyword, $relationMapping);
                }
            }
        });
        return $query;
    }

    private function filterByRelationship($query, $keyword, $relativeTables)
    {
        foreach ($relativeTables as $relationship => $relativeColumns) {
            $query->orWhereHas($relationship, function($relationQuery) use ($keyword, $relativeColumns) {
                foreach ($relativeColumns as $key => $column) {
                    $clause = $key == 0 ? 'where' : 'orWhere';
                    $relationQuery->$clause($column, "LIKE", "%$keyword%");
                }
            });
        }
        return $query;
    }
    
    
    
    
    
// add this code in controller 


    // this is array of relation_name and its columns/fields
    
    $relationMapping = [
        'relation_1' => ['column_1', 'column_2', 'column_3'],
        'relation_2' => ['column_1', 'column_2' ],
        'relation_2' => ['column_1', 'column_2' ]
    ];
    
    // pass 3 data in this function 1. search_keyword 2.column_array or null array 3. relationMapping array
    
    Model::search("search_string", [], $relationMapping)->get();

Stats

2 likes
• 8841 views

Categories

• Eloquent
• Raw queries

Tags

• models
• eloquent
• patterns
• database