CODEWITHSUNDEEP
phplaravel
harunB10
harunB10

Reputation: 5207

How to pass an array of IDs from one Laravel query to another

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?

Upvotes: 2

Views: 3310

Answers (4)

Bibhudatta Sahoo
Bibhudatta Sahoo

Reputation: 4894

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

enter image description here

Upvotes: 1

Rwd
Rwd

Reputation: 35190

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!

Upvotes: 0

aaron0207
aaron0207

Reputation: 2333

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()

Upvotes: 1

Sagar Gautam
Sagar Gautam

Reputation: 9389

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

Upvotes: 2

Related Questions