CODEWITHSUNDEEP
phpmysqllaravel
Payne
Payne

Reputation: 543

Error: Integrity constraint violation: 1052 when Join in Laravel Eloquent

I am trying to join two table together by specifying their relationship (i.e foreign key and local key) and using find(id). Ab initio, I used where and get(). It didn't gave the same error then I commented out the where clause to use find($id)

"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select `votes`.`flavour`, `users`.`email` from `votes` inner join `users` on `votes`.`user_id` = `users`.`id` and `id` = 1 limit 1)",

This is the function for displaying based on the selection public function show($id) {

         $dbconnect = DB::table('votes')
         ->join('users', 'votes.user_id', '=', 'users.id')
         //->where('votes.id', $id)
         ->select('votes.id','votes.date_of_birth','votes.voter_ip','votes.flavour', 'users.email')
         ->find($id);
         $vote = auth()->user()->$dbconnect;
        if (!$vote) {
            return response()->json([
                'success' => false,
                'message' => 'Vote with id ' . $id . ' not found'
            ], 400);
        }

        return response()->json([
            'success' => true,
            'data' => $vote->toArray()
        ], 400);
    }

Show all: in this function I am supposed to use join as well but I only used votes table it displayed accurately I need to join the votes and users table on votes.user_id = users.id 

public function index()
        {
            $votes = auth()->user()->votes;

            return response()->json([
                'success' => true,
                'data' => $votes
            ]);
        }

Thank you

Upvotes: 3

Views: 2265

Answers (1)

Jerodev
Jerodev

Reputation: 33216

It's because you are using the ->find($id) function. This function will look for the primary key of the model (in this case id) and use this as the filter. However, because of the join, this field is ambiguous.

To solve this, you can add the filter manually:

$dbconnect = DB::table('votes')
     ->join('users', 'votes.user_id', '=', 'users.id')
     ->select('votes.id','votes.date_of_birth','votes.voter_ip','votes.flavour', 'users.email')
     ->where('votes.id', $id)
     ->first();

Upvotes: 2

Related Questions