Laravel query builder. How to make the query shorter?

  laravel, laravel-query-builder, sql

I need to prepare data for datatables, and i need to count total amount of filtered data of the query, but i cant do it with just one query because of limit so i must do almost the same query but with no limit option for pagination, and it makes the amount of my code x2. Is it possible to make this code shorter and beautiful?

public function data($request)
{
    $staff = auth()->user();
    $role = Role::find($staff->role_id)->alias;
    $isAdmin = $role == 'admin';

    $columns = ['id', 'name', 'shop', 'hash', 'status'];

    $limit = $request->input('length');
    $start = $request->input('start');
    $order = $columns[$request->input('order.0.column')];
    $directions = $request->input('order.0.dir');
    $searchValue = $request->input('search.value');

    $filters = explode(',', $request->input('columns.7.search')['value']);
    foreach ($filters as $key => $value) {
        if ($value !== "") {
            array_push($shops, $value);
        }
    }
    if ($searchValue) $filters[] = $searchValue;

    $nfcs = NfcMark::offset($start)->select('nfc_marks.*')
        ->leftJoin('shops as s', 's.id', 'nfc_marks.shop_id')
        ->when(!$isAdmin, function ($q) {
            $shop_ids = $this->shopRepository->shopsIdsByOwner(auth()->user()->id);
            return $q->whereIn('shop_id', $shop_ids);
        })
        ->when($searchValue, function ($q) use ($filters) {
            foreach ($filters as $filter) {
                $q->orWhere('shops.name', 'LIKE', '%' . $filter . '%');
            }
        })
        ->limit($limit)
        ->orderBy($order, $directions)
        ->get();

    $nfcs_count = NfcMark::query()
        ->leftJoin('shops as s', 's.id', 'nfc_marks.shop_id')
        ->when(!$isAdmin, function ($q) {
            $shop_ids = $this->shopRepository->shopsIdsByOwner(auth()->user()->id);
            return $q->whereIn('shop_id', $shop_ids);
        })
        ->when($searchValue, function ($q) use ($filters) {
            foreach ($filters as $filter) {
                $q->orWhere('shops.name', 'LIKE', '%' . $filter . '%');
            }
        })->get();

    $totalFiltered = count($nfcs_count);
    $totalData = $totalFiltered;

    $data = array();

    if (!empty($nfcs)) {
        foreach ($nfcs as $item) {
            $nestedData['id'] = $item->id;
            $nestedData['name'] = $item->name;
            $nestedData['shop'] = $item->shop_id;
            $nestedData['hash'] = $item->hash;
            $nestedData['status'] = $item->status;

            $data[] = $nestedData;
        }
    }

    $json_data = array(
        "draw" => intval($request->input('draw')),
        "recordsTotal" => intval($totalData),
        "recordsFiltered" => intval($totalFiltered),
        "data" => $data
    );

    return json_encode($json_data);

}

Source: Laravel

Leave a Reply