Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have a request who is returning me the list of users but with the wrong id, i want the id of the table 'users' but it giving me the id from the table 'cfds'
public static function getListFormateurEM(){
$sql = User::join('cfds', 'users.id_cfds', '=', 'cfds.id')
->with('grades')
->with('cfds')
->whereRaw('isFormateur = true AND isChefdeCours = false AND isExterieur = false AND isEM = 1');
return $sql;
}
There is my request, and i don't understand why it's giving me the 'cfds' id instead of what i want, thank you for your help!
@elslay Why are you manually creating a join? Define a relationship between your user model and “cfds” model instead: https://laravel.com/docs/master/eloquent-relationships
When you’re joining on the cfds table, its id column will probably be overriding the id value of the corresponding users row.
Well i already have this relationship :
public function cfds()
{
return $this->belongsTo('App\Models\Cfd', 'id_cfds');
}
But it's look like it's not working
I confirm it's not working there is the generated request :
"
select * from users where isFormateur = true AND isChefdeCours = false AND isExterieur = false AND cfds.isEM = 1 and (((date_controle is null AND (date_mutation < DATE_SUB( curdate() , INTERVAL 6 MONTH))) or (date_prev_controle < curdate()) or (date_controle is null and date_mutation is null)) AND (date_depart IS NULL OR curdate() < date_depart ))"
and so he don't know the column isEM. I don't understand why
you can querying the relation
User::whereHas('cfds', function($query) {
$query->where('isEm', 1);
})->with('cfds')->get();
But where this is going please?
it will return collection of users where has cfds.isEm = 1 with its cfds.
you can add your query as your need,
Thx Bunnypro you're my hero, since sunday i was blocked and it's working now!
@bunnypro well it was a bug, it returning me the list without the condition "isEM=1"
i write this
public static function getListFormateurEM(){
$query = User::with('grades')
->whereRaw('isFormateur = true AND isChefdeCours = false AND isExterieur = false');
User::whereHas('cfds', function($query) {
$query->where('isEM', 1);
})->with('cfds')->get();
return $query;
}
you have to combine your query, don't separate it, unless you want some different result.
rerurn User::whereHas('cfds', function($query) {
$query->where('isEM', 1);
})->with('cfds')->where( ... )->get();
where is isFormsteur, isChefdeCours, and isExterieur belongs to ?
isFormateur and isChefdeCours and isExterieur are in the table users, but he can't find the column isEM (in the table cfds)
public static function getListFormateurEM(){
$sql = User::whereHas('cfds', function($query) {
$query->where('isEM' , 1);
})->with('cfds')->whereRaw('isFormateur = true AND isChefdeCours = false AND isExterieur = false')->get();
return $sql;
}
then the query should be
User::whereHas('cfds', function($query) {
$query->where('isEM', 1);
})->with('cfds')
->where('isFormateur', true)
->where('isChefdeCours', false)
->where('isExterieur', false)->get();
where is isEM belongs to ?
I tried it and i have "Argument 2 passed to Illuminate\Database\Query\Builder::whereRaw() must be of the type array, boolean given"
with my code who is using my request :
public static function getRetardFondDeSalleEM(){
$sql = User::getListFormateurEM()
->WhereRaw('(((`date_controle` is null AND (date_mutation < DATE_SUB( curdate() , INTERVAL 6 MONTH))) or (date_prev_controle < curdate()) or (date_controle is null and date_mutation is null)) AND (date_depart IS NULL OR curdate() < date_depart ))');
return $sql;
}
The repository :
$nbFormateurSPenRetard = User::getRetardFondDeSalleEM()->orderBy('date_controle','asc')->get();
The view :
@foreach ($nbFormateurSPenRetard as $unItem)
<tr class="danger">
<td class="text-primary text-center"><strong>{!!$unItem->grades->trigramme!!} {!!$unItem->nom!!} {!!$unItem->prenom!!}</strong></td>
<td class="text-primary text-center">
@if (empty(substr($unItem->cfds->clair,4)))
<strong>EMB</strong>
@else
<strong>{!! substr($unItem->cfds->clair,4) !!}</strong>
@endif
</td>
<td class="text-primary text-center">
{!! $unItem->date_controle !!}
</td>
<td class="text-primary text-center">
{!! $unItem->date_prev_controle !!}
</td>
<td class="text-primary text-center"><strong>
@if ($unItem->nbComm!=0)
<span class="badge">{!! $unItem->nbComm !!}</span>
@endif
</strong>
</td>
<td class="text-center">
<a href="{!!URL::to('fondDeSalle/'.$unItem->id.'/edit')!!}" class='btn btn-success btn-xs' title="Contrôler">
<i class="fa fa-edit fa-lg"></i>
</a>
</td>
</tr>
@endforeach
And isEM is in the table 'cfds'
public function cfds()
{
return $this->belongsTo('App\Models\Cfd', 'id_cfds');
}
the error message tells you that you are passing boolean to the wehreRaw method somewhere, it should be an array.
Yeh it's explicit but i don't understand why he don't find isEM before or how to give him an array.
the second argument of whereRaw is optional, so you don't have to pass it.
I understand but in this case he receive a null, "Call to a member function WhereRaw() on null", i really don't understand and i don't find a more simple method
can you post the code that makes error ?
yes of course, thank you :
FatalErrorException in User.php line 350:
Call to a member function WhereRaw() on null
in User.php line 350
at FatalErrorException->__construct() in compiled.php line 2094
at HandleExceptions->fatalExceptionFromError() in compiled.php line 2089
at HandleExceptions->handleShutdown() in compiled.php line 0
at User::getRetardFondDeSalleEM() in FondDeSalleRepository.php line 34
at FondDeSalleRepository->index() in FondDeSalleController.php line 20
at FondDeSalleController->index() in compiled.php line 8923
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:8923}() in compiled.php line 8923
at Controller->callAction() in compiled.php line 8992
at ControllerDispatcher->call() in compiled.php line 8972
at ControllerDispatcher->Illuminate\Routing\{closure}() in compiled.php line 9643
at call_user_func:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9643}() in compiled.php line 9643
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 9625
at call_user_func:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9625}() in compiled.php line 9625
at Pipeline->then() in compiled.php line 8973
at ControllerDispatcher->callWithinStack() in compiled.php line 8958
at ControllerDispatcher->dispatch() in compiled.php line 7907
at Route->runWithCustomDispatcher() in compiled.php line 7878
at Route->run() in compiled.php line 7531
at Router->Illuminate\Routing\{closure}() in compiled.php line 9643
at call_user_func:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9643}() in compiled.php line 9643
at Pipeline->Illuminate\Pipeline\{closure}() in Authenticate.php line 53
at Authenticate->handle() in compiled.php line 9635
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9635}() in compiled.php line 9635
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 9625
at call_user_func:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9625}() in compiled.php line 9625
at Pipeline->then() in compiled.php line 7532
at Router->runRouteWithinStack() in compiled.php line 7520
at Router->dispatchToRoute() in compiled.php line 7505
at Router->dispatch() in compiled.php line 2310
at Kernel->Illuminate\Foundation\Http\{closure}() in compiled.php line 9643
at call_user_func:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9643}() in compiled.php line 9643
at Pipeline->Illuminate\Pipeline\{closure}() in Debugbar.php line 51
at Debugbar->handle() in compiled.php line 9635
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9635}() in compiled.php line 9635
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 2925
at VerifyCsrfToken->handle() in compiled.php line 9635
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9635}() in compiled.php line 9635
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 12994
at ShareErrorsFromSession->handle() in compiled.php line 9635
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9635}() in compiled.php line 9635
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 11586
at StartSession->handle() in compiled.php line 9635
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9635}() in compiled.php line 9635
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 12731
at AddQueuedCookiesToResponse->handle() in compiled.php line 9635
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9635}() in compiled.php line 9635
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 12668
at EncryptCookies->handle() in compiled.php line 9635
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9635}() in compiled.php line 9635
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 2982
at CheckForMaintenanceMode->handle() in compiled.php line 9635
at call_user_func_array:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9635}() in compiled.php line 9635
at Pipeline->Illuminate\Pipeline\{closure}() in compiled.php line 9625
at call_user_func:{C:\wamp64\www\embTools\bootstrap\cache\compiled.php:9625}() in compiled.php line 9625
at Pipeline->then() in compiled.php line 2257
at Kernel->sendRequestThroughRouter() in compiled.php line 2240
at Kernel->handle() in index.php line 54
at {main}() in index.php line 0
And the query :
public static function getListFormateurEM(){
User::whereHas('cfds', function($query) {
$query->where('isEM', 1);
})->with('cfds')
->where('isFormateur', true)
->where('isChefdeCours', false)
->where('isExterieur', false)->get();
}
i mean the php code not the error message. :))
ah, add the return then.
return User:: ...
haven't you hear about query scope? its better than static function, https://laravel.com/docs/5.4/eloquent#local-scopes
oh sorry!
the interessant part of user.php :
public function cfds()
{
return $this->belongsTo('App\Models\Cfd', 'id_cfds');
}
public static function getListFormateurEM(){
User::whereHas('cfds', function($query) {
$query->where('isEM', 1);
})->with('cfds')
->where('isFormateur', true)
->where('isChefdeCours', false)
->where('isExterieur', false)->get();
}
public static function getRetardFondDeSalleEM(){
$sql = User::getListFormateurEM()
->WhereRaw('(((`date_controle` is null AND (date_mutation < DATE_SUB( curdate() , INTERVAL 6 MONTH))) or (date_prev_controle < curdate()) or (date_controle is null and date_mutation is null)) AND (date_depart IS NULL OR curdate() < date_depart ))');
return $sql;
}
the interessant part of view :
@foreach ($nbFormateurSPenRetard as $unItem)
<tr class="danger">
<td class="text-primary text-center"><strong>{!!$unItem->grades->trigramme!!} {!!$unItem->nom!!} {!!$unItem->prenom!!}</strong></td>
<td class="text-primary text-center">
@if (empty(substr($unItem->cfds->clair,4)))
<strong>EMB</strong>
@else
<strong>{!! substr($unItem->cfds->clair,4) !!}</strong>
@endif
</td>
<td class="text-primary text-center">
{!! $unItem->date_controle !!}
</td>
<td class="text-primary text-center">
{!! $unItem->date_prev_controle !!}
</td>
<td class="text-primary text-center"><strong>
@if ($unItem->nbComm!=0)
<span class="badge">{!! $unItem->nbComm !!}</span>
@endif
</strong>
</td>
<td class="text-center">
<a href="{!!URL::to('fondDeSalle/'.$unItem->id.'/edit')!!}" class='btn btn-success btn-xs' title="Contrôler">
<i class="fa fa-edit fa-lg"></i>
</a>
</td>
</tr>
@endforeach
the line of the repository :
$nbFormateurSPenRetard = User::getRetardFondDeSalleEM()->orderBy('date_controle','asc')->get();
at getListFormateurEM method add the return
public static function getListFormateurEM() {
return User:: ..
}
Did it and i get a "Missing argument 2 for Illuminate\Support\Collection::where(), called in C:\wamp64\www\embTools\app\User.php on line 350 and defined"
public static function getListFormateurEM(){
return User::whereHas('cfds', function($query) {
$query->where('isEM', 1);
})->with('cfds')
->where('isFormateur', true)
->where('isChefdeCours', false)
->where('isExterieur', false)->get();
}
ah, you are still querying after call getListFormateurEM isn't it?
then remove ->get() from getListFormateurEM method.
your model would be better if you are using query scope.
Yes i do, i will look at this
And without the get bim error "SQLSTATE[42S22]: Column not found: 1054 Champ '(((date_controle is null AND (date_mutation < DATE_SUB( curdate() , INTERVAL 6 MONTH))) or (date_prev_controle < curdate()) or (date_controle is null and date_mutation is null)) AND (date_de' inconnu dans where clause (SQL: select * from users where (select count(*) from cfds where users.id_cfds = cfds.id and isEM = 1) >= 1 and isFormateur = 1 and isChefdeCours = 0 and isExterieur = 0 and (((``date_controle`` is null AND (date_mutation < DATE_SUB( curdate() , INTERVAL 6 MONTH))) or (date_prev_controle < curdate()) or (date_controle is null and date_mutation is null)) AND (date_depart IS NULL OR curdate() < date_depart )) is null order by date_controle asc)", i'm going crazy with this ! i hate eloquent!
i think your raw query is the cause, eloquent is doing well.
check is there a typo in your raw query ? maybe you type miss the column name.
i try to use the scope :
public function scopeNonMute($query)
{
return $query->where('(((`date_controle` is null AND (date_mutation < DATE_SUB( curdate() , INTERVAL 6 MONTH))) or (date_prev_controle < curdate()) or (date_controle is null and date_mutation is null)) AND (date_depart IS NULL OR curdate() < date_depart ))');
}
public static function getRetardFondDeSalleEM(){
$sql = $users = User::getListFormateurEM()->scopeNonMute()->orderBy('created_at')->get();
return $sql;
}
And i don't think i have a similar request but not with the condition isEM=1 who is working :
public static function getListFormateur(){
$sql = User::with('grades')
->with('cfds')
->whereRaw('isFormateur = true AND isChefdeCours = false AND isExterieur = false');
return $sql;
}
This request is working perfectly. So i'm just lost.
Please or to participate in this conversation.