Laravel - multi-table insert with relationships

Question

I have 3 tables that should be populated when creating an order and I would like to know the best way to do this.

Tables

Orders
- id (pk)
- company_id
- address_id (join to items table)
- date
- source
- ...

Addresses
- id (pk)
- company_id
- name
- email
- address
- ...

Items
- id (pk)
- order_id (join to orders table)
- product_id
- qty
- ...

Relationships

Address table belongsTo the orders table
Order hasMany items
We have a multi-tennant (single db) structure so the company_id is used to separate data between companies. Both the order and the address belongsTo the company.

Creation

My understanding is that the address would need to be created first to get the ID. Then the order would be created, followed by the items.

I would also like to ensure that if any insert fails, no data would be saved (as in a SQL transaction).

I wonder if anyone can point my in the right direction for the best way to do this? So far I have the following

$address = OrderAddress::create([
    'company_id' => Auth::user()->company_id,
    'name' => 'test test',
    'email' => '[email protected]',
    'address' => '78 test street',
]);
$order = $address->order()->create([
    //'address_id' => populated by model
    'company_id' => Auth::user()->company_id,
    'date' => '2018-03-23',
    'source' => 'mystore'
]);
foreach($items as $item){
    OrderItem::create([
        'order_id' => $order->order_id,
        'product_id' => $item->product_id,
        'qty' => $item->qty
    ]);
}

My main issues are that:

this does not work as a transaction, i.e. a failure will not create a roll back.
Im not sure I am using the model relationships to their full potential.

Namoshek Namoshek · Accepted Answer · 2018-04-29T18:32:28

There is basically two improvements you can make. One for each of your issues.

To wrap everything in a transaction, you can put the create statements in a DB::transaction(...) block. For reference, have a look at the manual. There is also another way of using transactions described in there.
The same relationship create method you used when creating your Order can also be applied to the OrderItems: $order->items()->create([...]).

The final code snippet could look something like this:

DB::transaction(function () {
    $address = OrderAddress::create([
        'company_id' => Auth::user()->company_id,
        'name' => 'test test',
        'email' => '[email protected]',
        'address' => '78 test street',
    ]);
    $order = $address->order()->create([
        'company_id' => Auth::user()->company_id, // actually, this information is already available through the `address`
        'date' => '2018-03-23',
        'source' => 'mystore'
    ]);
    foreach($items as $item){
        $order->items()->create([
            'order_id' => $order->order_id,
            'product_id' => $item->product_id,
            'qty' => $item->qty
        ]);
    }
});

Please make sure to change the relationship name if it is called orderItems() instead of items().

Laravel - multi-table insert with relationships

2 Answers