Query records within a foreach loop in a view

eager load the relationship

$customers =Customer::with('orders')->get();

and then each customer will have a nested collection of their orders

You wouldn't want to be running the queries in the loop. That's very wasteful and expensive (runs a query for each loop).

You'd want to eager load the orders (relationship) associated with the customers.

Like

$customers = Customer::with('orders')->where(your_condition)->get().

Now every customer would have their orders loaded.

@foreach ($customers as $customer)
<div>{{ $customer->name }}</div>
<div>{{ $customer->email }}</div>
<div>{{ $customer->phone }}</div>
<div>{{ $customer->orders->count() }}</div> (from the Orders Table)
@endforeach 

That will load the customers, and their orders, in 2 queries no matter how many customers/orders there were.

https://laravel.com/docs/5.6/eloquent-relationships#eager-loading

Makes sense.

However, what if it wasn't customers and orders, and it was 2 tables that had a many-to-many relationship based on one field.

Or let's say it's one step further with Customers and Orders... The first query brings back customers and orders, and I use the relationship to show the orders.

But now I want the tooltip to be for each Product in the order, and I want to hover over the Product to see a list of other Orders that contain that product.

But realistically it's 2 tables with the many-to-many relationship based on a single field, so the relationship doesn't seem to help, unless that would work in this case as well.

Customer::with('orders')->where(your_condition)->get() is using 2 tables. One for customers and one for orders.

@foreach ($customers as $customer)
<div>{{ $customer->name }}</div>
<div>{{ $customer->email }}</div>
<div>{{ $customer->phone }}</div>
    
    @php
        $tooltip = '';
    @endphp

    @foreach ($customer->orders as $order)
        @php
            $tooltip .= '<div>' . $order->title . '</div>';
        @endphp
    @endforeach 
    <div>{{ $tooltip }}</div>
@endforeach 

I'd be doing that (cycling through the orders to build up a tooltip per customer with all orders) in the controller though, but thought it would be easier to show this way.

Ok, but don't I have to set up the relationship in the model? I've just never set up a many to many relationship in a model before.

Yes, like in the manual that I linked to above showing eager loading. It would be a hasMany relationship. A customer hasMany orders.

In the customer model, it would be something like

public function orders()
{
    return $this->hasMany(App\Order::class);
}

Ahh.... gotcha.. I'll give it a try. Thanks!

So, it seems if I set up the one connective relationship, the eager loading will work, based on the premise that the order table has a customer_id field in it, that is a primary key.

New analogy...

I have 2 tables. each set of data is out of my control. I just receive them and update the database accordingly

Table 1: Car Sales for last year Table 2: Current Cars for Sale

Table 1 Fields: Date of Sale - (03/01/2017, 10/13/2017, etc.) Car Model - (S100, S200, S300, etc.) Price - ($10,000, $20,000, etc.) Description - (Nice Car, Super Deal, etc.) Salesperson - (Jim, Suzie, Fred, etc.)

Table 2 Fields: Car Model - (S100, S200, S300, etc.) Price - ($10,000, $20,000, etc.) Description - (Nice Car, Super Deal, etc.)

So the tables are very similar, with no real ID's for the car Models, no primary keys, and no table called "Car Models"

I run a query that lists cars Currently for sale, in a foreach, based on certain criteria, listing car model and price, etc.

I want to hover over the car model and see the past sales from last year, queried from the other table.

Though I understand eager loading, based on this setup, I don't see how Eloquent can connect the two fields via relationship of the "Models" fields since there is no ID, and no primary field on either table (Multiple of the same model sold last year and multiple of the same model currently for sale).

I know it isn't preferred, but this is why I first wanted to run a query in the foreach, with fairly small amount of records, let's say 1,400 in current cars for sale, and 5,000 in the table of sales from last year, I'm not as concerned about the multiple queries, but again, I am trying to do things the "Laravel Way" if at all possible. :)

#tryingtobeagoodstudent

You can specify keys for the relationships. It assumes field_id by default, but you can tell it which fields to use. They don't have to be primary key fields. Read the user guide: https://laravel.com/docs/5.6/eloquent-relationships

See where they are explaining return $this->hasMany('App\Comment', 'foreign_key', 'local_key'); and other things. Read the whole page.

You'd probably be joining on the car model field, but can't say without looking at the actual schema of the 2 tables.

It does not matter if you are doing the query in the view or the controller, you can't avoid a way to reliably relate one table to the other.

What you DON'T want to be doing is running a query each time around a loop. That will result in the so-called n+1 issue, where if you had 20 cars there would be 21 queries to render the page.