Trying to get mark values from MySQL in Laravel

Question

I have a task in which i need to select all drivers from database in Laravel and highlight in green those ones which are available. This is the error i get:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_id' in field list is ambiguous (SQL: SELECT first_name,user_id FROM users as u inner join users_vehicles group by u.users_id)

Database table structure:

Here is my code so far:

UserModel.php

<?php

namespace App\Models;
use DB;

use Illuminate\Database\Eloquent\Model;

class UserModel extends Model
{
    //
    
    public function svivozaci(){
        
        $query=DB::SELECT("SELECT first_name,user_id FROM users as u inner join users_vehicles group by u.first_name");
        return $query;
    }
}

admin.blade.php

@extends('layout')

@section('content')

<h1>Admin page </h1>

@foreach($data as $d)

{{$d->first_name}} <br>

@endforeach

@endsection

The error says it all: `user_id` is ambiguous because it's present in the `users` table as well as in the `users_vehicles` table. You shall choose which user_id to select. — D. Petrov, Jul 03 '20 at 13:20
Does this answer your question? [How to solve Integrity constraint violation: 1052 Column 'agent\_id' in where clause is ambiguous](https://stackoverflow.com/questions/56007261/how-to-solve-integrity-constraint-violation-1052-column-agent-id-in-where-cla) — MrEvers, Jul 03 '20 at 13:24
I get now this error: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'phpfinal.u.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: SELECT first_name,u.user_id FROM users as u inner join users_vehicles group by u.first_name) — Arkan Kalu, Jul 03 '20 at 13:25

OMR · Answer 1 · 2020-07-03T14:57:41.297

0

you have both columns with name user_id in users,users_vehicles ...

just add u. before user_id in select field ...

 $query=DB::SELECT("SELECT first_name,u.user_id FROM users as u inner join users_vehicles group by u.first_name,u.last_name");

but i don't recommend using group by without aggregation, you may use distinct instead:

 $query=DB::SELECT("SELECT first_name,u.user_id FROM users as u inner join users_vehicles")->distinct()->get();

edited Jul 03 '20 at 14:57

answered Jul 03 '20 at 13:19

OMR

11,736
5
20
35

I get now this error: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'phpfinal.u.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: SELECT first_name,u.user_id FROM users as u inner join users_vehicles group by u.first_name) – Arkan Kalu Jul 03 '20 at 13:25

score 0 · Answer 2 · answered Jul 03 '20 at 14:21

I guess you need to change you query to something like:

SELECT u.first_name, u.user_id 
FROM users as u 
INNER JOIN users_vehicles USING (user_id)
GROUP BY u.first_name, u.user_id

However, if you need just names of the users who have vehicles, it would be better to go with query like:

SELECT u.first_name, u.user_id
FROM users as u
WHERE EXISTS (SELECT * FROM users_vehicles uv WHERE uv.user_id = u.user_id)

Check this question to see comparison of JOIN vs EXISTS

Trying to get mark values from MySQL in Laravel

2 Answers2