CODEWITHSUNDEEP
phplaraveleloquent
Adam Lambert
Adam Lambert

Reputation: 1431

Laravel - multi-table insert with relationships

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

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:

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

Upvotes: 1

Views: 1866

Answers (2)

tebowner
tebowner

Reputation: 45

DB::transaction(...)

or

DB::beginTransaction();

You can rollback the transaction via the rollBack method:

DB::rollBack();

Lastly, you can commit a transaction via the commit method:

DB::commit();

Upvotes: 1

Namoshek
Namoshek

Reputation: 6544

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

  1. 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.

  2. 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().

Upvotes: 2

Related Questions