CODEWITHSUNDEEP
phplaravelsearchrelation
Hardist
Hardist

Reputation: 1983

Laravel relation search function

I know where the following error is coming from but I do not know how to fix it.

The error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'expenses.date' in 'where clause' (SQL: select count(*) as aggregate from expenses_sections where expenses.date LIKE %2015-12%)

What is causing it (in my controller):

if($view === 'section') {
    $query = Section::query();
        $query->with(['expenses' => function($query) use ($search){
            $query->where('date', 'LIKE', '%2015-' . $search . '%')->get();
        }]);
}

I have a select menu which I use to change the layout of the page. I have another select menu which I use to display results from a certain month only. The above error is what I am getting when I select a month, because the query is looking in the wrong table (because of the above code). The results are displayed using a relation between Expenses and Sections. What I basically want is to search for results in the expenses table, instead of the expenses_sections table, ofcourse without breaking the relation, because based on the relation, my layout is being displayed.

Expense Model:

class Expense extends Model
{
    public function section()
    {
        return $this->belongsTo('App\Http\Models\Expenses\Section');
    }
}

Section Model:

class Section extends Model
{
    public function expenses()
    {
        return $this->hasMany('App\Http\Models\Expenses\Expense');
    }
}

Upvotes: 0

Views: 333

Answers (1)

Thomas Kim
Thomas Kim

Reputation: 15911

Query constraints should always return the query builder instance. You should not call get within your closure so try fixing it like this:

$query = Section::query();
$query->with(['expenses' => function($query) use ($search) {
    $query->where('date', 'LIKE', '%2015-' . $search . '%');
}]);

This will fetch all sections and eager load the relationships. The ones that don't fit the constraints will just have an empty expenses relationship. If you only want sections that fit the constraints, then you should use whereHas method.

$query = Section::query();
$query->with('expenses')->whereHas('expenses', function($query) use ($search) {
    $query->where('date', 'LIKE', '%2015-' . $search . '%');
});

Upvotes: 1

Related Questions