Order By Relationship (HasOne ) in Laravel

Question

I have a distributor model with a BelongsTo relation and a user model with a HasOne relation, and I tried to retrieve them in order (verified date) using BuilderQuery.

 // User relation
 public function user()
 {
    return $this->belongsTo(User::class);
 }

 // Distributor relation
 public function distributor()
 {
    return $this->hasOne(Distributor::class);
 }

// Query
if (!empty(request("sortBy"))) {
    $column = explode("__", request("sortBy"))[0];
    $sortBy = strtoupper(explode("__", request("sortBy"))[1]);

    $query->with("distributor");

    if ($column === "verified_date" || $column === "phone") {
        // Try 1 - Errors raised
        $query->join('distributors', 'distributors.user_id', '=', 'users.id')
          ->orderBy('distributors.' . $column, $sortBy);

        // Try 2 - Errors raised
        // $query->select('users.*')
        // ->join('distributors', 'distributors.user_id', '=', 'users.id')
        // ->orderBy('distributors.' . $column, $sortBy);

        // Try 3 - No Errors raised but results returned with incorrect orders
        // $query->orderBy(Distributor::select($column)
        //     ->whereColumn('distributors.user_id', 'users.id')
        // );
    } else {
        $query->orderBy($column, $sortBy);
    }
} else {
    $query->orderBy("updated_at", "desc");
}

When run this code, I got errors saying SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous

How can I solve this?

It is not clear from your question - do you mean your `Distributor` `belongsTo` a `User`, and a `User` `hasOne` `Distributor`? If yes, why are you using Query builder and manual `join`s? You've set up your Eloquent models, let Laravel and Eloquent do the work. `User::with('distributor')->orderBy('distributors.' . $column)->get();` or something like that should do? — Don't Panic, Jul 12 '23 at 12:57

rozsazoltan · Accepted Answer · 2023-06-03T08:10:35.620

By default, if you query a single table, there are no issues with column names. However, when you query at least two tables joined together, it may happen that the same column name exists in both tables. You need to specify which table's ID you want to order by: table_name.column_name.

Your condition is that if the column name is verified_date or phone. However, your error message is referring to id. Are you sure the error is not in the else branch?

$query->orderBy($column, $sortBy); // get error (because not declared table name)

Solution

$query->orderBy('distributors.' . $column, $sortBy);

(distributors or your model's table name, maybe 'users.' . $column)

score 0 · Answer 2 · answered Jul 12 '23 at 11:35

You can do it by using 'relation name.column name'

For your code snippet, you can use:

$query->orderBy('distributor.'.$column, $sortBy);

Explanation: I guess your 'users' table has a column named 'id' and your distributor table has also a column named 'id'. So when your SQL executes it's getting confused that which table 'id' should use. So now you need to instruct the query that distributor.

Order By Relationship (HasOne ) in Laravel

2 Answers2

Solution