Laravel datatable yajra select from 2 tables with condition

Question

I'm using yajra datatable package and I have some orders tables, there are :

orders : id,order_code,duration,status .....
order_products (storing order from available product) : id,order_id(reference from orders),product_id,type,price ,created_at ....
order_vps_custom (storing order from custom product) : id,order_id(reference from orders),package,ram,cpu,created_at...

There is 2 cases :

case 1 : If user choose from available product

I store order_code and etc into orders table as parent and then insert into table order_packages as child table of orders

case 2 : If user custom vps order

I store order_code and etc into orders table as parent and then insert into table order_vps_custom as child table of orders

And I have no idea to select with eager load,with expected output on my page like below:

VPS | REGIESTERED AT | STATUS

order_packages and order_vps_custom on same table

I've try with this on controller:

$model = Order::with(['order_products','vps_custom'])->get();
    
$dTable = DataTables()->of($model)->addIndexColumn()
             ->editColumn('vps',function($data){
                 return $data->vps->package;
               })

Order (model) :

public function order_products()
{
    return $this->hasMany(OrderProduct::class,'order_id','id');
}

public function vps_custom()
{
    return $this->hasMany(OrderVpsCustom::class,'order_id','id');
}

here is my result array :

Can anyone help me out ?

0x00b0 0x00b0 · Accepted Answer · 2020-08-08T17:31:39

It's solved but without eager loading and using LEFT JOIN, if anyone could help me changed to eager loading please answer

 $model = DB::table('orders')
                ->select('orders.*','order_products.*','order_vps_customs.*')
                ->join('order_products','orders.id','=','order_products.order_id','left')
                ->join('order_vps_customs','orders.id','=','order_vps_customs.order_id','left')
                ->get();

        // dd($model->toArray());
        $dTable = DataTables()->of($model)->addIndexColumn()
            ->editColumn('package',function($data){
                
                $package = $data->product_name;
                if (empty($data->product_name)) 
                {
                    $package = $data->package;
                }

                return $package;
            })
            ->editColumn('registered_at',function($data){
                return date('d, M Y',strtotime($data->created_at));
            })
            ->editColumn('due_date',function($data){
                return date('d, M Y', strtotime("+".$data->duration." year",strtotime($data->created_at)));
            })
            ->editColumn('duration',function($data){
                return $data->duration." Tahun";
            })
            ->editColumn('total',function($data){
                return "-";
            })
            ->editColumn('status',function($data){
                $html = '';
                if ($data->status == 'pending') 
                {
                    $html = '<label class="badge badge-secondary">Pending</label>';
                }
                else if ($data->status == 'aktif') 
                {
                    $html = '<label class="badge badge-success">Aktif</label>';
                }
                else
                {
                    $html = '<label class="badge badge-warning">Non - Aktif</label>';
                }

                return $html;
            })
            ->rawColumns(['status']);

        return $dTable->make(true);

Laravel datatable yajra select from 2 tables with condition

1 Answers