CODEWITHSUNDEEP
phplaraveleloquent
robert theboss
robert theboss

Reputation: 111

Laravel Eloquent: How to retrieve array of values for each column in select?

I have a 'clients_campaigns' table that has three columns. User_id, company_id and campaign_id. I want to get a list of all company_id's and campaign id's that have a specific user_id. With these two lists I will search through two other tables, Companies and Campaigns. By using each list, I will find the names associated to the id's.

I am unsure how to create two lists that'll contain each type of id's. Below is my attempt and the code I have so far.

            $ids = \DB::table('clients_campaigns')->select('company_id','campaign_id')
                                                  ->where('user_id',$request->user_id)
                                                  ->pluck('company_id','campaign_id');
            if($ids !== null){
                //use those id's to retrieve the company names and campaign names
               Company::select('name')->whereIn('id', ...);
               Campaign::select('name')->whereIn('id',...);

Upvotes: 0

Views: 3855

Answers (2)

lagbox
lagbox

Reputation: 50481

If you really want to do it this way you are not far off but you really don't need the first pluck since there will only be 3 fields anyway. You can then pluck the company_id and campaign_id columns separately to be used in the next 2 queries:

$ids = DB::table('clients_campaigns')
    ->where('user_id', $request->user_id)
    ->get(['company_id', 'campaign_id']);

if ($ids->notEmpty()) {
    $companyNames = Company::whereIn('id', $ids->pluck('company_id'))
        ->pluck('name');
    $campaignNames = Campaign::whereIn('id', $ids->pluck('campaign_id'))
        ->pluck('name');
}

If you used pluck on the first query you would have a list keyed by campaign_id then you would have to use just the keys for the Campaign query and just the values for the Company query.

You also have the option of passing query objects to the whereIn calls instead.

Upvotes: 2

James
James

Reputation: 16339

Taking your current example, you could retrive the results from the first query into a Collection and then use the collection to extract your results for each subsequent query.

Putting this into practice would look like this:

$ids = \DB::table('clients_campaigns')->select('company_id','campaign_id')
    ->where('user_id',$request->user_id)
    ->get();
    ->pluck('company_id','campaign_id');

if ($ids->count()) {
    //use those id's to retrieve the company names and campaign names
    Company::select('name')->whereIn('id', $ids->pluck('company_id'));
    Campaign::select('name')->whereIn('id', $ids->pluck('campaign_id'));
}

Upvotes: 0

Related Questions