foreach in Laravel controller not looping over correctly

Question

$customers = DB::table('customers')
    ->Join('customer_types', 'customer_types.id', '=', 'customers.customer_type')
    ->Join('countries', 'countries.id', '=', 'customers.country_id')
    ->select(
        'customers.*',
        'customer_types.customer_type_name',
        'countries.country_name'
    )
    ->get();

foreach ($customers as $result) {
    $prices = Pricing::whereIn('id', explode(',', $result->pricelist_id))->get();
}

I have the above code what am trying to achieve is to use the first query to fetch the actual value contained in pricelist_id.

pricelist_id is a collection of many ids ([1, 2, 3]) like that but when I execute the code it returns same value in all rows how can I make it iterate correctly.

Answer 1

You could maybe key your array.

$customers = DB::table('customers')
    ->Join('customer_types', 'customer_types.id', '=', 'customers.customer_type')
    ->Join('countries', 'countries.id', '=', 'customers.country_id')
    ->select(
        'customers.*',
        'customer_types.customer_type_name',
        'countries.country_name'
    )
    ->get();

$prices = []
foreach ($customers as $result) {
    $prices[$result->id] = Pricing::whereIn('id', explode(',', $result->pricelist_id))->get();
}

@foreach ($customers as $customer)
    {{ $customer->id }},
    ....
    @foreach ($price[$customer->id] as $price)
        {{ $price->... }}
    @endroreach
@endforeach

This is not ideal. You're making 1 extra query per customer. Instead of having a pricelist_id field in your customers table, you should make it add another table between customers and pricings

+-----------+                                    +----------+
| customers |        +------------------+        | pricings |
+-----------+        | customer_pricing |        +----------+
| id        |---+    +------------------+    +---| id       |
| ...       |   |    | id               |    |   | ...      |
+-----------+   +--->| customer_id      |    |   +----------+
                     | pricing_id       |<---+
                     | ...              |
                     +------------------+