Eloquent: Joining a table to itself (Many-To-Many)

You should be forewarned, this is a snippets post. I find many to many joins a bit clunky, so this is a good reminder for next time 🙂

On a side project, I need to link one or many Stores to other Stores. (A store applies to sell products from another store, creating a web of store permissions).

Create the table

The first step to the many to many is making the table. Laravel best practice is usually amodelname_zmodelname. Since both of my joins are to Store, my table is supplier_store_shipper_store.

      Schema::create('supplier_store_shipper_store', function (Blueprint $table) {
            $table->increments('id');
            $table->integer("supplier_store_id");
            $table->integer("seller_store_id");
            $table->boolean("is_approved")->default(0);
            $table->timestamp("access_requested_at")->nullable()->null();
            $table->timestamp("approved_at")->nullable()->null();
            $table->timestamps();
        });

Seller Store is the requesting store, Supplier Store is the approving store. is_approved, access_requested_at, approved_at are all pivot fields that we need to keep track of as part of the relationship.

There’s no additional ID fields required in the store table, this new table holds everything about the relationship.

Define the relationship in the model

By creating a belongsToMany relationship with the table name, both key names, and pivot fields we want, Laravel will quickly tell us which stores have been approved to sell.

Insert a row in the Many to Many table.

Inserting a row in the many to many table is as simple as calling the function above that we just defined and then attach. Note how we are also passing in the pivot fields in an associative array as the second parameter.

$supplier_store->approvedToSellFromStores()->attach($store, [
                'is_approved'=>0,
                'access_requested_at'=>Carbon::now(),
                'approval_key'=>$approval_key
            ]);

Now we’ve got a record in our database documenting the seller store has requested access to the supplier store.

Modifying the pivot row

In this case, a store applies for access and the other store grants access . That’s two operations on the pivot row, so we need to be able to update it.

I find updating the pivot rows a bit clunky. First we load the store, and then load the pivot, and then filter the pivot. The remaining rows are the rows we need to operate on.

$records = $supplier->approvedStores()->wherePivot('approval_key','=',$approval_key)->wherePivot('is_approved','=',0);

if($records->count() > 0){

    $records->updateExistingPivot($seller, array('is_approved' => 1, 'approved_at'=>Carbon::now()));

Calling updateExistingPivot with an associative array as the second parameter (as before!) allows us to update the pivot, and grant the seller access to the supplier products.

Back in our product directory, we can now use a nice helper function to make sure the row exists and the seller is approved to sell the product – making sure is_approved = 1 if you’re following along!

@if($store->hasApprovalToSell($product->store))

After some profiling, I’ll likely need to add an index to both supplier & seller store IDs in the database migration before this app goes to production.