Nested SQL Query in Laravel Controller

Question

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]);

}

Damien Pirsy · Accepted Answer · 2016-01-26 12:04:23Z

1

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;

edited Jan 26, 2016 at 12:04

answered Jan 26, 2016 at 11:49

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tomantford Over a year ago

I was thinking of using a JOIN but I will have multiple values in the dbBids table. The $query->pluck('act_id'); is giving an error [non-object]. I am expecting multiple records for dbQuotes so will need to use the ->get()

tomantford Over a year ago

It is: ->get(); $act_id = $query->pluck('act_id');

Damien Pirsy Over a year ago

Sorry, it's lists() for the Query Builder, my mistake: laravel.com/docs/5.2/queries#retrieving-results Updated answer

tomantford Over a year ago

Still getting Call to a member function lists() on a non-object

tomantford Over a year ago

Yes, I also tried replacing the get with lists but still get the non-object error. Interestingly in phpStorm the analysis is saying lists() is depreciated which is odd.

|

Collectives™ on Stack Overflow

Nested SQL Query in Laravel Controller

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related