jrmypttrsn
1 week ago

addSelect not working as I think it should

Posted 1 week ago by jrmypttrsn

Let me preface this by saying SQL isn't my strong suit and I'm still trying to wrap my brain around the subquery capabilities in Laravel 6.

When I run the query below I end up with a 1054 error (SQLSTATE[42S22]: Column not found: 1054 Unknown column 'addresses.address' in 'field list')

$inspections = DB::table('inspections')
    ->join('clients', 'inspections.client_id', 'clients.id')
    ->join('contacts', 'inspections.client_id', 'contacts.id')
    ->join('jobs', 'inspections.job_id', 'jobs.id')
    ->join('staff', 'inspections.inspector_id', 'staff.id')
    ->addSelect(['phone' => Phone::select('phone_type', 'phone_number')
        ->whereColumn('phoneable_id', 'inspections.client_id')
        ->orderBy('phone_type', 'desc')
    ])
    ->addSelect(['address' => Address::select('address', 'city', 'state', 'zip', 'country')
        ->whereColumn('addressable_id', 'inspections.client_id')
        ->where('address_type', 'Location')
    ])
    ->select(
        'inspections.id',
        'inspections.client_id',
        'clients.name',
        'contacts.email',
        'inspections.scope',
        'inspections.gps',
        'jobs.new',
        'jobs.new_land',
        'jobs.new_crop',
        'jobs.priority',
        'inspections.inspector_id',
        'staff.first_name',
        'staff.last_name',
        'inspections.inspection_at',
        'inspections.confirmed',
        'addresses.address',
        'addresses.city',
        'addresses.state',
        'addresses.zip',
        'addresses.country',
        'phones.phone_type',
        'phones.phone_number'
    )
    ->get();

Any idea what I'm doing wrong and ways I could make this query more efficient?

Please sign in or create an account to participate in this conversation.