Query Table and Return Results based on a different table.

Hello All, First time posting on here. I am stuck at the moment. What I have are 3 models: Locations, Items, Stock. A relation is made between the Items and Stock then Stock has a relation to Locations.

What I am looking to do is return all Items (the model) that has Stock given the location. I have all of the models set up and the FKs configured between the tables but I don't know how to form the query using Eloquent's interface, I know it's going to be a join of some kind but it is just not ringing a bell.

None the less here is the db migrations.

Locations:

Schema::create('locations', function (Blueprint $table) {
    $table->bigIncrements('id');
    
    $table->bigInteger('company_id')->unsigned();
    $table->foreign('company_id')
        ->references('id')->on('companies')
        ->onDelete('cascade');
    
    $table->boolean('enabled')->default(true);
    $table->softDeletes();
    $table->timestamps();
});

Items:

Schema::create('items', function (Blueprint $table) {
    $table->bigIncrements('id');
    
    $table->bigInteger('company_id')->unsigned();
    $table->foreign('company_id')
        ->references('id')->on('companies')
        ->onDelete('cascade');
    
    $table->string("name")->nullable();
    $table->string("sku")->nullable()->unique();
    $table->boolean('is_discontinued')->default(false);
    $table->softDeletes();
    $table->timestamps();
});

Stock:

Schema::create('item_stock', function (Blueprint $table) {
    $table->bigIncrements('id');
    
    $table->bigInteger('company_id')->unsigned();
    $table->foreign('company_id')
        ->references('id')->on('companies')
        ->onDelete('cascade');
    
    $table->bigInteger('item_id')->unsigned();
    $table->foreign('item_id')
        ->references('id')->on('items')
        ->onDelete('cascade');
    
    $table->bigInteger('location_id')->unsigned();
    $table->foreign('location_id')
        ->references('id')->on('locations')
        ->onDelete('cascade');
    
    $table->bigInteger('qty')->default(0);
    $table->softDeletes();
    $table->timestamps();
});

Tray2

6 years ago

Best Answer

Level 73

I find it easier to write the SQL first then convert it to Eloquent if need be.

Since you have the location id in the Stock we only need to use stock and items.

So something like

select i.* from items i, stock s where i.company_id = s.company_id and location_id = <the location_id given>

That would translate into something like

DB::table('items')
            ->join('stock', 'items.company_id', '=', 'stock.company_id')
            ->select('items.*')
        ->where('items.location_id', $location->id 
            ->get();

1 like

nhalstead

6 years ago

Level 1

Thanks, That helped me figure it out, this is what I've got that seems to be working for me. For other people that may stumble upon this:

    /**
     * Get the Items the company has in stock at this location.
     *
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function getItems()
    {
        $items = Item::join("item_stocks", 'items.id', '=', 'item_stocks.item_id')
            ->select('items.*')
            ->where('item_stocks.location_id', $this->id);
        return $items->get();
    }

Please or to participate in this conversation.