Ad

Laravel 5.1 Left Join Alias Issue Two Foreign Key On Same Table

- 1 answer

I have defined a two foreign keys from the warehouse_transfer table to the warehouse table.

I have tried two ways to implement my Laravel Eloquent Query but in both ways in does not working

Two ForeignKey Referencing Same Table,

warehouse_transfer:

1   id                  int(11)         

2   date                timestamp

3   from_warehouse_id   int(11)    references warehouse

4   to_warehouse_id     int(11)    references warehouse

5   staff_id            int(11)

6   itp                 timestamp

7   utp                 timestamp   

warehouse:

1   id                  int(11)         

2   name                varchar(500)

Now on the time of retrieve data i want name from warehouse table,

Step - 1:

WarehouseTransfer::select("warehouse_transfer.*,staff.name as staff_name,warehouse.name AS from_warehouse_name,warehouse.name AS to_warehouse_name")
                ->leftjoin('staff', 'staff.id', '=', 'warehouse_transfer.staff_id')
                ->leftJoin('warehouse', 'warehouse.id', '=', 'warehouse_transfer.from_warehouse_id')
                ->leftJoin('warehouse', 'warehouse.id', '=', 'warehouse_transfer.to_warehouse_id')
                ->paginate(10)

which says,

1066 Not unique table/alias: oops_warehouse

Step - 2:

WarehouseTransfer::select("warehouse_transfer.*,staff.name as staff_name,w1.name AS from_warehouse_name,w2.name AS to_warehouse_name")
                ->leftjoin('staff', 'staff.id', '=', 'warehouse_transfer.staff_id')
                ->leftJoin('warehouse AS w1', 'w1.id', '=', 'warehouse_transfer.from_warehouse_id')
                ->leftJoin('warehouse AS w2', 'w2.id', '=', 'warehouse_transfer.to_warehouse_id')
                ->paginate(10)

which says,

Column not found: 1054 Unknown column oops_w1.id in 'on clause'

So ultimately issue is alias on leftjoin table is not working

Ad

Answer

I was missing DB::raw() that i have used and problem has been solved,

// Warehouse Transfer From
if (in_array('from_warehouse_id', $columns)) {
    $q->addSelect(DB::raw("fromw.id as from_warehouse_id, fromw.name from_warehouse_name"))
    ->leftjoin(DB::raw('oops_warehouse AS fromw'), DB::raw('fromw.id'), '=', 'warehouse_transfer.from_warehouse_id');
}

// Warehouse Transfer To
if (in_array('to_warehouse_id', $columns)) {
    //$q->addSelect('warehouse_transfer.to_warehouse_id', 'warehouse.name AS to_warehouse_name');
    $q->addSelect(DB::raw("tow.id as to_warehouse_id, tow.name to_warehouse_name"))
    ->leftjoin(DB::raw('oops_warehouse AS tow'), DB::raw('tow.id'), '=', 'warehouse_transfer.to_warehouse_id');
}
Ad
source: stackoverflow.com
Ad