CODEWITHSUNDEEP
phpmysqllaravellaravel-5.1
Moussa Chaabar
Moussa Chaabar

Reputation: 501

Laravel 5.1 finding project owner email with project_id in other table

I am trying to find a users email when I know the project id.

So in my table projects for example I have a record: id: 1 user_id: 6

I know want to find the email of user 6 in my users table: for example: id: 6 email: [email protected]

so as a result I want to get [email protected]

I've tried something like this, but doesn't seem to be working:

$projectOwnerEmail = DB::table('users')
        ->select('email')
        ->where('id', '=', $input['project_id'])
        ->join('projects', 'users.id', '=', 'projects.user_id')
        ->get();

Many thanks!

Upvotes: 1

Views: 105

Answers (1)

Bogdan
Bogdan

Reputation: 44526

The query you create using the Query Builder is the following:

SELECT email
FROM users
INNER JOIN projects ON users.id = projects.user_id
WHERE id = ?

The problem with this is that when evaluating the condition, there is no way to determine if you are trying to compare the id of the users table or the id of the projects table. This results in an integrity constraint violation error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous

To fix this you just need to specify the table for the id column like so:

->where('projects.id', '=', $input['project_id'])

Also, using get will return a collection, which is ok because you can access the email like so $projectOwnerEmail[0]->email, but that feels overly complicated. Instead you can use pluck which will return a string with the email address instead:

$projectOwnerEmail = DB::table('users')
    ->select('email')
    ->where('projects.id', '=', $input['project_id'])
    ->join('projects', 'users.id', '=', 'projects.user_id')
    ->pluck('email');

Now you have the email string stored directly in the $projectOwnerEmail variable.

Upvotes: 2

Related Questions