如何将一个ID数组从一个Laravel查询传递给另一个

I have 2 queries from two different tables. First one contains the IDs which I would like to use in the second query. For now I do it like this. I extract the IDs into a new array, and then use this array in ->whereIn() in second query.

$campaign = DB::connection('mysql2')->table('mc_spots')
            ->select('s_customer', 's_product', 'spotid')
            ->where('s_starttermin', '>=', date("Y-m-d", strtotime($dateFrom)))
            ->where('s_lastrun', '<=', date("Y-m-d", strtotime($dateUntil)))
            ->where('s_media', '=', $media)
            ->where(function ($query) use ($products) {
                for ($i = 0; $i < count($products); $i++) {
                    $query->orwhere('s_product', '=', $products[$i]);
                }
            })

            ->get();

$campaignID = [];

        foreach ($campaign as $c) {
            array_push($campaignID, $c->spotid);
        }

$table = DB::connection('mysql2')->table('schaltdaten_tv_de')
                ->select('*')
                ->join('epgdata_channel', 'schaltdaten_tv_de.cid', '=', 'epgdata_channel.channelid')
                ->join('mc_spots', 'schaltdaten_tv_de.ReferenceDescription', '=', 'mc_spots.spotid')
                ->whereIn('ReferenceDescription', $campaignID)
                ->groupBy('epgdata_2013.id')
                ->orderBy('StartofBreak', 'ASC')
                ->limit(500)
                ->get();

Is there more convenient way to do this without looping through every item of $campaign?

You need to pluck id from first query which gives you array of ids and you can use it in second query.

$campaign = DB::connection('mysql2')->table('mc_spots')
        ->select('s_customer', 's_product', 'spotid')
        ->where('s_starttermin', '>=', date("Y-m-d", strtotime($dateFrom)))
        ->where('s_lastrun', '<=', date("Y-m-d", strtotime($dateUntil)))
        ->where('s_media', '=', $media)
        ->where(function ($query) use ($products) {
            for ($i = 0; $i < count($products); $i++) {
                $query->orwhere('s_product', '=', $products[$i]);
            }
        })

        ->pluck('spotid');

Now, use spot id array in whereIn of second as ,

$table = DB::connection('mysql2')->table('schaltdaten_tv_de')
            ->select('*')
            ->join('epgdata_channel', 'schaltdaten_tv_de.cid', '=', 'epgdata_channel.channelid')
            ->join('mc_spots', 'schaltdaten_tv_de.ReferenceDescription', '=', 'mc_spots.spotid')
            ->whereIn('ReferenceDescription', $campaign)
            ->groupBy('epgdata_2013.id')
            ->orderBy('StartofBreak', 'ASC')
            ->limit(500)
            ->get();

Hope you understand

You can do it like this

$campaignID = $campaign->pluck('spotid');

Pluck doc:

The pluck method retrieves all of the values for a given key

And as Sagar said, it retrieves an array which is the second argument to our ->whereIn()

You can do this by use of array_column()

Like this

$campaignID=array_column($campaign,'spotid')

Make sure that $campaign must be an array. if it is object the convert it into array like this json_decode(json_encode($campaign),true)

For exapmle

$t='[{"id":49},{"id":61},{"id":5},{"id":58}]' ;
array_column(json_decode($t,true),'id')

It will give output as

As you're already joining the mc_spots table in the 2nd query you could always just add the same constraints from the 1st query:

$table = DB::connection('mysql2')->table('schaltdaten_tv_de')
    ->select('*')
    ->join('epgdata_channel', 'schaltdaten_tv_de.cid', '=', 'epgdata_channel.channelid')
    ->join('mc_spots', 'schaltdaten_tv_de.ReferenceDescription', '=', 'mc_spots.spotid')
    ->where('mc_spots.s_starttermin', '>=', \Carbon\Carbon::parse($dateFrom))
    ->where('mc_spots.s_lastrun', '<=', \Carbon\Carbon::parse($dateUntil))
    ->where('mc_spots.s_media', $media)
    ->whereIn('s_product', $products)
    ->groupBy('epgdata_2013.id')
    ->orderBy('StartofBreak', 'ASC')
    ->limit(500)
    ->get();

Hope this helps!