Combine two/three tables in a view

replied 7 years ago

I assume each order has a product_id and a client_id?

I see 2 main approaches:

generating the entire table on the back-end with one query, or
using multiple queries to get the axes & data and putting them together on the front-end

For the first approach, the key is a cross join. Cross joins create all possible pairings of the entries of two tables. Then if you left join that result with the orders, you'll have every possible pairing, with orders attached when they exist. (Note that this would need to be more complex if you have more than one order per product/client pair.)

// untested, but something like this
$orderTable = Product::crossJoin('clients')->leftJoin('orders', function ($join) {
    $join->on('products.id', '=', 'orders.product_id')->on('clients.id', '=', 'orders.client_id');
})->get();

These entries will already basically be in table order, though you might want to use a groupBy clause or two if you want to enforce structure.

0

tdhsmith

replied 7 years ago

Solution

In the second approach, you'd have 3 separate queries:

$products = Product::all();
$clients = Client::all();
$orders = Order::all()->groupBy('client_id')->groupBy('product_id', true);

Then in the view you'd just loop through those entries and display them. For example in Blade it would be something like:

<table>
@foreach ($products as $product)
    <tr>
    @foreach ($clients as $client)
        <td>{{ array_get($orders, ($product->id . '.' . $client->id), 'No orders') }}</td>
    @endforeach
    </tr>
@endforeach
</table>

There's probably a better way to do the order collection keying and retrieval, but groupBy+array_get was the first idea off the top of my head...

This is the approach I personally prefer, because (in small projects at least) it's more readable to me and doesn't rely on SQL magic. But each has its benefits at different scales and different needs.

0

lameboy

replied 7 years ago

Thank you @tdhsmith! I think I'm on theright way thanks to you. I'm trying to use the second approach but what I get is a table full with 'No orders'.

In my orders table I do not have the real ID of the product (the one that is it's id in the products table) because In my orders table I'm writing all the information for the product from products table. In a future moment when I delete or change one product in products table I want to have all old orders as they were in the moment they are placed.

I have another column in my products table - productId. There I'm entering ID's manually (used for sorting in another place). I think I should use this column for the above purpose.

The columns in my orders table: id, id_order, price, name, unit, qty, productId, client_id, discount, ...

The columns in my products table: id, name, price, unit, productId, ...

So, now my view have

<table>
@foreach ($products as $product)
    <tr>
    @foreach ($clients as $client)
        <td>{{ array_get($orders, ($product->productId . '.' . $client->id), 'No orders') }}</td>
    @endforeach
    </tr>
@endforeach
</table>

The result is a table with columns = number of clients, rows = number of products and 'No orders' in the cells.

Where I'm wrong?

Thank you very much for your help!

Last updated 7 years ago.

0

Moderators