0

I am using yajra datatable in a laravel project to generate a list. My problem is: when I join a table and try to use a raw query to extract a column, it becomes unsearchable.

My table code:

    $AAEListData = Entrepreneur::leftjoin('users', 'users.id', 'entrepreneurs.user_id')
                ->where('entrepreneurs.user_id', Auth::user()->id)
                ->groupBy('entrepreneurs.id')
                ->orderBy('entrepreneurs.created_at','desc')
                ->select([
                    'entrepreneurs.*',
                    'users.id as user',
                    DB::raw('CONCAT(IFNULL(users.user_first_name,"")," ",IFNULL(users.user_middle_name,"")," ",IFNULL(users.user_last_name,"")) as user_name')
                ]);

 return Datatables::of($AAEListData)
            ->addColumn('action', function ($AAEListData) {
                $view_btn = '<a href="' . url('entrepreneur/advance-against-expense-info/edit/' . Encryption::encodeId($AAEListData->id)) .
                    '" class="btn btn-xs btn-primary open" target="_blank" ><i class="fa fa-folder-open"></i> Open</a>';
                return $view_btn;
            })
            ->rawColumns(['action'])
            ->make(true);

The problem starts with the column user_name.

 columns: [
            {data: 'name', name: 'name'},
            {data: 'phone', name: 'phone'},
            {data: 'status', name: 'status'},
            {data: 'user_name', name: 'user_name'},
            {data: 'action', name: 'action'},

        ],

Whenever I try to search something, an error says column entrepreneurs.user_name not found. That is because it is not in that table, it comes from a joined table users. Can yajra datatable not detect this? I have searched this issue on the net and most suggestions ask converting the query collection to an array or simple avoid DB::RAW altogether. Neither solution is optimal for my situation but I am willing to do it the hard way if no other choice remains. My question is: is there a simpler way of informing datatable about which column belongs in which table? N.B: no, simply doing users.user_name does not work. That is the first thing I tried, in both controller and blade, neither worked.

Mash tan
  • 159
  • 11
  • Have you find the solution? Can you share with us the answer so you can help others. – Sharmae Reyes Sep 09 '22 at 05:50
  • unfortunately no, I ended up going around the error by simply preventing user search by turning {data: 'user_name', name: 'user_name'}, into {data: 'user_name', name: 'user_name', searchable: 'false'}, Apparently, left joins cant be handled like this. Of course, I could go the hard way by aggregating the queries, but simply turning off user name search was easier for me and does not hurt user experience much. – Mash tan Sep 09 '22 at 06:50
  • you can resolve this with a plugin if you bother to. https://github.com/yajra/laravel-datatables/issues/31 – Mash tan Sep 09 '22 at 06:53

3 Answers3

0

If your using the latest version you should define the name: table.column pattern.

farij ariefqy
  • 1
  • I already tried that and unfortunately that did not resolve the issue. As I said in my question: ( N.B: no, simply doing users.user_name does not work. That is the first thing I tried, in both controller and blade, neither worked.) – Mash tan Nov 03 '22 at 03:55
0

I think your problem is not about Yajra. "name: table.column" pattern is working to me("yajra/laravel-datatables-oracle": "^9.19").

I guess there is no column of user_name in tables of users and entrepreneurs.

it won't work below.

SELECT CONCAT(IFNULL(users.user_first_name,"")," ",IFNULL(users.user_middle_name,"")," ",IFNULL(users.user_last_name,"")) as user_name
FROM USERS
WHERE user_name = [userName];

You should make a subquery.

SELECT
        t1.*,
        users.id as user,
FROM 
    (SELECT entrepreneurs.*, 
            CONCAT(IFNULL(users.user_first_name,"")," ",IFNULL(users.user_middle_name,"")," ",IFNULL(users.user_last_name,"")) as user_name 
     FROM entrepreneurs 
     WHERE entrepreneurs.user_id = Auth::user()->id
    ) as t1
LEFT JOIN users on users.id = t1.user_id
WHERE t1.user_name = [userName]
GROUP By t1.id

----> Yajra subquery example

"name: table.column" pattern

Blade

columns: [
          { data: 'delivery_type', name: 't2.delivery_type' },          
         ],

Controller

if ($userType == 'HFF0601' || $userType == 'HFF0602') {
    $defaultQuery = 
      datatables()->of(Delivery::select([
                                 'deliveries.*', 
                                 'deliveries.status as status_code',
                                 't1.email', 
                                 't1.user_type', 
                                 't2.group_no', 
                                 't2.delivery_type',
                                 't2.delivery_type as delivery_type_code'
                                ])->join('delivery_groups AS t2', 'deliveries.grp_id', '=', 't2.id')
                                ->leftJoin('users AS t1', 'deliveries.user_id', '=', 't1.id'));
}
return $defaultQuery
         ->filter(function ($query) use ($request) {
               .
               .
               .
               .
mipqim
  • 1
  • 1
0

I tested my code and it works. but the concat in the where statement may have performance issues.

DB::raw("CONCAT(t1.name,' ',t1.last_name) as fullname")

->orWhereRaw("CONCAT(t1.name,' ',t1.last_name) like ?", "%{$serchTerms}%")

{ data: 'fullname', name: 'fullname' },

if ($userType == 'HFF0601' || $userType == 'HFF0602') {
    $defaultQuery = datatables()
                      ->of(Delivery::select(
                           [DB::raw("CONCAT(t1.name,' ',t1.last_name) as fullname"), 
                            'deliveries.*', 
                            'deliveries.status as status_code',
                            't1.email', 
                            't1.user_type', 
                            't2.group_no', 
                            't2.delivery_type', 
                            't2.delivery_type as delivery_type_code'])
                      ->join('delivery_groups AS t2', 'deliveries.grp_id', '=', 't2.id')
                      ->leftJoin('users AS t1', 'deliveries.user_id', '=', 't1.id'));
}
return $defaultQuery
    ->filter(function ($query) use ($request) {
        //Filtering date range
        //...
        //Global Search
        if ($request->has('searchterms')) {
            $serchTerms = $request->searchterms;
            $query->where(function($q) use($serchTerms) {
                $q->where('shipping_no', 'like', "%{$serchTerms}%")
                    ->orWhere('order_no', 'like', "%{$serchTerms}%")
                    ->orWhere('email', 'like', "%{$serchTerms}%")
                    ->orWhereRaw("CONCAT(t1.name,' ',t1.last_name) like ?", "%{$serchTerms}%")
                    ->orWhere('from_name', 'like', "%{$serchTerms}%")
                    ->orWhere('to_name', 'like', "%{$serchTerms}%")
                    ->orWhere('desc1', 'like', "%{$serchTerms}%")
                    ->orWhere('status', 'like', "%{$serchTerms}%");
            });
        }
    })    

columns: [
    { data: 'checkbox', name: 'checkbox', orderable: false, searchable: false},
    { data: 'id', name: 'id' },
    { data: 'group_no', name: 'group_no' },
    { data: 'delivery_type', name: 't2.delivery_type' },
    { data: 'user_id', name: 'user_id' },
    { data: 'email', name: 'email' },
    { data: 'order_no', name: 'order_no' },
    { data: 'shipping_no', name: 'shipping_no' },
    { data: 'from_name', name: 'from_name' },
    { data: 'to_name', name: 'to_name' },
    { data: 'shipping_charge', name: 'shipping_charge' },
    { data: 'status', name: 'status' },
    { data: 'fullname', name: 'fullname' },
    { data: 'action', name: 'action', orderable: false },
],
mipqim
  • 1
  • 1