Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel, $query, Trouble with Laravel Eloquent orderBy when sorting by related table column

I’m encountering an issue with Laravel Eloquent while trying to order query results by a column from a related table. Specifically, I have a recipes table and a related nutrients table, and I want to order recipes by the kcal column in the nutrients table.

Here’s the code snippet I’m using:

$query = Recipe::with('nutrients');

// more code


if ($request->has('order_by')) {
        $orderBy = $request->input('order_by');
        $direction = $request->input('order_direction', 'asc');

        // some code
            
        elseif ($orderBy === 'kcal') {
            if ($request->has('kcal')) {
                $kcal = $request->input('kcal');
                $query->whereHas('nutrients', function ($q) use ($kcal) {
                    $q->where('kcal', '<=', $kcal);
                });
            }
            $query->orderBy('nutrients.kcal', $direction);
        }
}

// some code
return RecipesResource::collection($recipes);

Models:

Recipe:
public function nutrients()
    {
        return $this->hasOne(Nutrient::class, 'recipe_id', 'id');
    }

Nutrient:
public function recipe()
    {
        return $this->hasOne(Recipe::class);
    }

However, this code is resulting in an SQL error: "Column not found: 1054 Unknown column ‘nutrients.kcal’ in ‘order clause’". It seems like Laravel is not recognizing the kcal column in the nutrients table when trying to order the results.

I’ve tried different approaches, including explicitly specifying the table name in the orderBy clause, but so far, I haven’t been able to resolve the issue.

Can anyone provide insight into why this error is occurring and how to properly order query results by a column from a related table in Laravel Eloquent?

Tags:

2

Answers


  1. 0

    No matter what is your code quality , After reviewing your code , I think the issue lies here :

     $query->whereHas('nutrients', function ($q) use ($kcal) {
                $q->where('kcal', '<=', $kcal);
            });

    Change this "$q->where(‘kcal’, ‘<=’, $kcal);" to "$q->where(‘nutrients.kcal’, ‘<=’, $kcal);" , I assumed that "nutrients" table does have ‘kcal’ column exists , Thanks if it helps.

    Login or Signup to reply.
  2. 0

    The reason this error is thrown is because laravel divides your query into multiple queries. It first queries your recipes table. After which it extracts the ids from that result and runs a wherein query on your nutrients table. This means that during your first query the nutrients table is not loaded, hence the error is being thrown that it cannot find the column.

    You can prevent this by using a join statement instead.

    $query->join('nutrients', 'id', '=', 'nutrients.id')
    ->whereHas('nutrients', function($q) use ($kcal) {
        return $q->where('kcal', '<=', $kcal);
    })
    ->orderBy('nutrients.kcal', $direction);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search