Laravel Eloquent selecting field from relationship after joining with other tables-CodePudding

I have a query where I have joined several tables together and as they have fields with identical names I have used the select query to only pick out the fields that I require. However, i also need to include the results obtained from the with query but I do not know how to specify it in the select query.

        $display_tickets = ManualTicket::select('u.name as name', 'i.name as initiator', 'manual_tickets.status as status', 'manual_tickets.description as description', 'manual_tickets.location as location', 'manual_tickets.created_at as created_at', 'manual_tickets.initiator_id as initiator_id', 'manual_tickets.id as manual_ticket_id','manual_tickets.manual_ticket_log as manual_ticket_log_id')
            ->leftJoin('users as u', 'u.id', '=', 'manual_tickets.user_id')
            ->leftJoin('users as i', 'i.id', '=', 'manual_tickets.initiator_id')
            ->where(function ($checkClients) use($target_client_id){
                $checkClients->where('u.client_id', '=', $target_client_id)
                    ->orWhere('i.client_id', '=', $target_client_id);
            })
            ->whereBetween('manual_tickets.created_at', [$start_date->toDateString(), $end_date->addDays(1)->toDateString()])
            ->with('manual_ticket_log')
            ->orderBy("created_at", "DESC")->get();

I tried to include the manual ticket logs through the use of the code below but it says that there is no such field in the manual ticket table.

'manual_tickets.manual_ticket_log as manual_ticket_log_id')

In short, how can i include the results of the with relationship from ->with('manual_ticket_log') into the select statement

--EDIT-- (Tried to replace join with with queries)

However there appears to be any error with my SQL query which i believe stems from the whereHas portion of the code which results in this error being shown.

strtolower() expects parameter 1 to be string, object given

 $display_tickets = ManualTicket::select('*')
                ->with('user')
                ->with('initiator')
                ->with('manual_ticket_log')
                ->where(function ($checkClients) use($target_client_id){
                    $checkClients->whereHas('user', function ($checkClient) use($target_client_id){
                        $checkClient->where('client_id', '=', $target_client_id);
                    })
                    ->orWhere($checkClients->whereHas('initiator', function ($checkClient2) use($target_client_id){
                        $checkClient2->where('client_id', '=', $target_client_id);
                    }));
                })
                ->whereBetween('manual_tickets.created_at', [$start_date->toDateString(), $end_date->addDays(1)->toDateString()])
                ->orderBy("created_at", "DESC")->get();

CodePudding user response：

you should join that table to get fields form:

   $display_tickets = ManualTicket::select('u.name as name', 'i.name as initiator', 'manual_tickets.status as status', 'manual_tickets.description as description', 'manual_tickets.location as location', 'manual_tickets.created_at as created_at', 'manual_tickets.initiator_id as initiator_id', 'manual_tickets.id as manual_ticket_id','manual_tickets.manual_ticket_log as manual_ticket_log_id',
            'manual_tickets.manual_ticket_log as manual_ticket_log_id')
            ->leftJoin('users as u', 'u.id', '=', 'manual_tickets.user_id')
            ->leftJoin('users as i', 'i.id', '=', 'manual_tickets.initiator_id')
            ->leftJoin('manual_ticket_logs',function ($join){
            $join->on('manual_ticket_logs.manual_ticket_id', '=', 'manual_tickets.id')
                ->on('manual_ticket_logs.id', '=', \DB::raw("(select max(id) from manual_ticket_logs WHERE manual_ticket_logs.manual_ticket_id = manual_tickets.id)"));
            })
            ->where(function ($checkClients) use($target_client_id){
                $checkClients->where('u.client_id', '=', $target_client_id)
                    ->orWhere('i.client_id', '=', $target_client_id);
            })
            ->whereBetween('manual_tickets.created_at', [$start_date->toDateString(), $end_date->addDays(1)->toDateString()])
            ->with('manual_ticket_log')
            ->orderBy("created_at", "DESC")->get();

please make sure of the fields names in the newest join clause.

CodePudding user response：

You can try this

    $display_tickets = ManualTicket::select('u.name as name', 'i.name as initiator', 'manual_tickets.status as status', 'manual_tickets.description as description', 'manual_tickets.location as location', 'manual_tickets.created_at as created_at', 'manual_tickets.initiator_id as initiator_id', 'manual_tickets.id as manual_ticket_id','manual_tickets.manual_ticket_log as manual_ticket_log_id')
        ->leftJoin('users as u', 'u.id', '=', 'manual_tickets.user_id')
        ->leftJoin('users as i', 'i.id', '=', 'manual_tickets.initiator_id')
        ->where(function ($checkClients) use($target_client_id){
            $checkClients->where('u.client_id', '=', $target_client_id)
                ->orWhere('i.client_id', '=', $target_client_id);
        })
        ->whereBetween('manual_tickets.created_at', [$start_date->toDateString(), $end_date->addDays(1)->toDateString()])
        ->with(['manual_ticket_log' => function($query) {
                $query->select('field1', 'field2');
          }])
        ->orderBy("created_at", "DESC")->get();

Or do this like

->with('manual_ticket_log:field1, field2')