Filament•3mo ago

modifyQueryUsing ->join changes $record->id

Building a Dashboard widget to display Team Notes. Works except using join or leftJoin changes $record->id from the original query table to the "joined" table. I want to use the original table id for a link to the record and it changes to the id of the "joined' table .

return $table
->query(Persona::query())
->modifyQueryUsing(fn(Builder $query) =>
    $query
    ->where('am_notes','!=','')
    //  Jacks up the links
    ->join('users', 'users.id', '=', 'personas.client_id')
    ->where(function(Builder $query) use ($client_id){
        if ($client_id) {
            $query->where('client_id',  $client_id);
        }
    })
    ->where(function(Builder $query) use ($csm_id, $am_id){
        if ($csm_id) {
            $query->where('users.customer_support_manager_id', $csm_id);
        }
        if ($am_id) {
            $query->where('users.account_manager_id', $am_id);
        }
    })
)

->columns([
    Tables\Columns\TextColumn::make('client.name')->sortable()
        //changes with join
        ->url(fn ($record): string => "/clients/$record->client_id/edit"),
    Tables\Columns\TextColumn::make('li_email')
        //changes with join
        ->url(fn (Persona $record): string => "/personas/$record->id/edit")
        ->openUrlInNewTab(),
    Tables\Columns\TextColumn::make('am_notes')
        ->label('Instructions'),
]);

return $table
->query(Persona::query())
->modifyQueryUsing(fn(Builder $query) =>
    $query
    ->where('am_notes','!=','')
    //  Jacks up the links
    ->join('users', 'users.id', '=', 'personas.client_id')
    ->where(function(Builder $query) use ($client_id){
        if ($client_id) {
            $query->where('client_id',  $client_id);
        }
    })
    ->where(function(Builder $query) use ($csm_id, $am_id){
        if ($csm_id) {
            $query->where('users.customer_support_manager_id', $csm_id);
        }
        if ($am_id) {
            $query->where('users.account_manager_id', $am_id);
        }
    })
)

->columns([
    Tables\Columns\TextColumn::make('client.name')->sortable()
        //changes with join
        ->url(fn ($record): string => "/clients/$record->client_id/edit"),
    Tables\Columns\TextColumn::make('li_email')
        //changes with join
        ->url(fn (Persona $record): string => "/personas/$record->id/edit")
        ->openUrlInNewTab(),
    Tables\Columns\TextColumn::make('am_notes')
        ->label('Instructions'),
]);

Solution:

How about this? Basically flipped the logic, and used a whereHas instead of the join to do some of the logic. ``` return $table ->query(Persona::query())...

Jump to solution

2 Replies

Solution

Tonkawuck•3mo ago

How about this? Basically flipped the logic, and used a whereHas instead of the join to do some of the logic.

return $table
    ->query(Persona::query())
    ->modifyQueryUsing(function(Builder $query) {
        $query
            ->where('am_notes','!=','');
        
        if ( $client_id ) {
            $query->where('client_id',  $client_id);
        }

        if ($csm_id) {
            $query->whereHas('users', fn(Builder $query) => $query->where('customer_support_manager_id', $csm_id));
        }

        if ($am_id) {
            $query->whereHas('users', fn(Builder $query) => $query->where('account_manager_id', $am_id));
        }
    })
    ->columns([
        Tables\Columns\TextColumn::make('client.name')->sortable()
            //changes with join
            ->url(fn ($record): string => "/clients/$record->client_id/edit"),
        Tables\Columns\TextColumn::make('li_email')
            //changes with join
            ->url(fn (Persona $record): string => "/personas/$record->id/edit")
            ->openUrlInNewTab(),
        Tables\Columns\TextColumn::make('am_notes')
            ->label('Instructions'),
    ]);

return $table
    ->query(Persona::query())
    ->modifyQueryUsing(function(Builder $query) {
        $query
            ->where('am_notes','!=','');
        
        if ( $client_id ) {
            $query->where('client_id',  $client_id);
        }

        if ($csm_id) {
            $query->whereHas('users', fn(Builder $query) => $query->where('customer_support_manager_id', $csm_id));
        }

        if ($am_id) {
            $query->whereHas('users', fn(Builder $query) => $query->where('account_manager_id', $am_id));
        }
    })
    ->columns([
        Tables\Columns\TextColumn::make('client.name')->sortable()
            //changes with join
            ->url(fn ($record): string => "/clients/$record->client_id/edit"),
        Tables\Columns\TextColumn::make('li_email')
            //changes with join
            ->url(fn (Persona $record): string => "/personas/$record->id/edit")
            ->openUrlInNewTab(),
        Tables\Columns\TextColumn::make('am_notes')
            ->label('Instructions'),
    ]);

ddoddsrOP•3mo ago

This is very promising . The vars $client_id,$csm_id, $am_id are Undefined in the if statements that contain the where, whereHas . I just had to change to ->modifyQueryUsing(function(Builder $query) use ($client_id,$csm_id, $am_id) { adding the use and change $query->whereHas('users', to $query->whereHas('client', to fit my models. (for other readers benefit). Thanks that worked!

Gaming

Programming

modifyQueryUsing ->join changes $record->id