Inner query in where clause laravel

Question

I have a database in which there are two tables driver_company_map and company what i do is i get company_code from driver_company_map table and i pass it in the where clause of another query which i perform on company table my both separate query works like this

$result = DB::connection($this->masterDb)->table('driver_company_map')
        ->where('driver_code', $driverCode) //i get the $driverCode from function parameter
        ->select('company_code')
        ->first();
        $companyCode = $result->company_code;

I use the above $companyCode in the below query

$result = DB::connection($this->masterDb)->table('company')
        ->where('code', $companyCode)
        ->select('db_connection')
        ->first();
        $clientDb = $result->db_connection;

The above logic works fine but i want both as nested query i tried it but not giving the correct result below is my code

$result = DB::connection($this->masterDb)->table('company')
        ->where('code', function($companyCode_query){
            $companyCode_query->select('company_code')
            ->from('driver_company_map')
            >where('driver_code', $driverCode);
        })->get()
        ->select('db_connection')
        ->first();
        $clientDb = $result->db_connection;

Welcome to SO. I guess you can find solution for your question here: https://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in — Shreeraj, Aug 23 '18 at 07:40

score 0 · Accepted Answer · edited Aug 23 '18 at 10:56

0

Try this:

$result = DB::connection($this->masterDb)->table('company')
    ->select('db_connection')
    ->join('driver_company_map', 'company.code', 'driver_company_map.company_code')
    ->where('driver_company_map.driver_code', $driverCode)
    ->first();

Refer to this: https://laravel.com/docs/5.6/queries#joins

edited Aug 23 '18 at 10:56

Sarvesh Chavan

73
1
3
7

answered Aug 23 '18 at 07:21

Mike Kevin Castro

99
1
11

Thanks you the answer is correct just one mistake `$result = DB::connection($this->masterDb)->table('company') ->select('db_connection') ->join('driver_company_map', 'company.code', 'driver_company_map.company_code') ->where('driver_company_map.driver_code', $driverCode) ->first();` `driver_company_map.driver_code` will come instead of this `driver_company_map.company_code` – Sarvesh Chavan Aug 23 '18 at 07:53

Inner query in where clause laravel

1 Answers1