CODEWITHSUNDEEP
phpmysqllaravellaravel-5
tomantford
tomantford

Reputation: 182

Nested SQL Query in Laravel Controller

I have two queries running in my controller. I need a value from the first query to be passed into the second. I want the result of both these queries sent to my view. 

public function jobs()
{


    $query = DB::table("dbQuotes")
        ->leftJoin("dbACT", "dbQuotes.act_id", "=", "dbACT.ID")
        ->leftJoin("dbOpps", "dbQuotes.act_id", "=", "dbOpps.contactID")
        ->leftjoin('dbBids', 'dbQuotes.act_id','=',
            DB::raw('dbBids.quote_id AND dbBids.user_id = '. Auth::user()->id))
        ->where("dbQuotes.active", "=", "1")
        ->select("dbQuotes.*", "dbACT.*", "dbBids.*",
            (DB::raw('date_format(dbQuotes.posted_date, "%d/%m/%Y %H:%i") as posted_date')),
            (DB::raw('date_format(dbOpps.expected_date, "%d/%m/%Y") as expected_date')))
        ->groupBy("dbQuotes.id")
        ->orderBy("posted_date", "desc")
        ->get();

$passinvaluehere = $query->dbQuotes.act_id


    $bids = DB::table("dbBids")

        ->where("quote_id", "=", $passinvaluehere)
        ->get();


    return view('jobs', ['query' => $query,'bids' => $bids]);

}

My query works and the view is established in the correct way if I replace the passed value with a number, i.e "8763". My question is how, within this function, can I pass the value/s of dbQuotes.act_id into this second query?

***UPDATED Code from answer: [error Call to a member function lists() on a non-object]

public function jobs()
{


    $query = DB::table("dbQuotes")
        ->leftJoin("dbACT", "dbQuotes.act_id", "=", "dbACT.ID")
        ->leftJoin("dbOpps", "dbQuotes.act_id", "=", "dbOpps.contactID")
        ->leftJoin('dbBids', 'dbQuotes.act_id','=',
            DB::raw('dbBids.quote_id AND dbBids.user_id = '. Auth::user()->id))
        ->where("dbQuotes.active", "=", "1")
        ->select("dbQuotes.*", "dbACT.*", "dbBids.*",
            (DB::raw('date_format(dbQuotes.posted_date, "%d/%m/%Y %H:%i") as posted_date')),
            (DB::raw('date_format(dbOpps.expected_date, "%d/%m/%Y") as expected_date')))
        ->groupBy("dbQuotes.id")
        ->orderBy("posted_date", "desc")
        ->get();

    $act_id = $query->lists('act_id');

    $bids = DB::table("dbBids")
        ->whereIn("quote_id", $act_id)
        ->get();


    return view('jobs', ['query' => $query,'bids' => $bids]);

}

Upvotes: 1

Views: 1022

Answers (1)

Damien Pirsy
Damien Pirsy

Reputation: 25435

If you have multiple records (as per the ->get() method) you have two ways: either you loop over the Collection and make a query each iteration (bad) or you create an array of ids and use a whereIn in the second query (better):

$passinvaluehere = $query->lists('act_id');
// https://laravel.com/docs/5.2/queries#retrieving-results
// this creates and array of `act_id` s    

$bids = DB::table("dbBids")
        ->whereIn("quote_id", $passinvaluehere)
        ->get();
// you now have a Collection of multiple $bids

If you expect only a single records from your first query, you need to change the fetcher method, using first() instead, or else take only the first element of your actual collection, something like first($query) or $query[0]

$query = DB::table("dbQuotes")
         ....
         ->first();
$passedvaluehere = $query->act_id;

Upvotes: 1

Related Questions