Example for sorting, searching, and paginating directly in the Eloquent query builder

ApiController.php

<?php

   /**
     * Returns a list of workers.
     *
     * Generates a list of workers, optionally paginated, sorted and/or filtered.
     * This page requires authentication.
     * Request type: GET
     */
    public function listWorkers()
    {
        $get = $this->_app->request->get();

        $size = isset($get['size']) ? $get['size'] : null;
        $page = isset($get['page']) ? $get['page'] : null;
        $sort_field = isset($get['sort_field']) ? $get['sort_field'] : "balance";
        $sort_order = isset($get['sort_order']) ? $get['sort_order'] : "asc";
        $filters = isset($get['filters']) ? $get['filters'] : [];
        $format = isset($get['format']) ? $get['format'] : "json";
        
        // Access-controlled page
        if (!$this->_app->user->checkAccess('uri_workers')) {
            $this->_app->notFound();
        }
        
        // Allow filtering by last task
        if (isset($filters['last_task'])) {
            $taskFilter = $filters['last_task'];
        } else {
            $taskFilter = null;
        }
        
        $query = Worker::joinUser()->joinLatestTask($taskFilter)->with(['user' => function ($subquery) {
            return $subquery->exclude(['password', 'secret_token']);
        }, 'latestTask']);
            
        // Count unpaginated total
        $total = $query->count();
        
        // Apply filters
        $filtersApplied = false;
        foreach ($filters as $name => $value) {
            if ($name != 'last_task') {
                $query = $query->like($name, $value);
                $filtersApplied = true;
            }
        }
        
        $total_filtered = $query->count();
        
        // Sort "last task" column by date
        if ($sort_field == "last_task") {
            $sort_field = "last_task_date";
        }
        
        $query = $query->orderBy($sort_field, $sort_order);    
        
        // Paginate
        if ( ($page !== null) && ($size !== null) ){
            $offset = $size*$page;
            $query = $query->skip($offset)->take($size);
        }
        
        $collection = collect($query->get());
           
        // Render
        $this->render($collection, $total, $total_filtered, 'workers', function($collection) {
            $collection->transform(function ($item) {
                // Note that latestTask is a relation, while latest_task is an attribute that we set
                if ($item->latestTask){
                    $item->latest_task = $item->latestTask->task;
                } else {
                    $item->latest_task = "";
                }
                return $item;
            });
            return $collection;
        });
    }      

Worker.php

<?php

namespace UserFrosting;

use Illuminate\Database\Capsule\Manager as Capsule;
use Illuminate\Database\Eloquent\SoftDeletes;

/**
 * Worker Class
 *
 * Represents a worker, which is a special type of user.
 *
 * @author Alex Weissman
 *
 * @property string name
 */
class Worker extends UFModel
{

    /**
     * @var string The id of the table for the current model.
     */ 
    protected static $_table_id = "worker";

    public $timestamps = true;
    
    /**
     * Use soft deletes, so we can maintain related entities (tasks, etc).
     */
    use SoftDeletes;
    
    /**
     * Get the user account associated with this worker.
     */
    public function user()
    {
        return $this->belongsTo('UserFrosting\User', 'user_id');
    }
    
    /**
     * Get all tasks for this worker.
     */
    public function tasks()
    {
        return $this->hasMany('UserFrosting\Task', 'worker_id');
    }
    
    /**
     * Get all paychecks for this worker.
     */
    public function paychecks()
    {
        return $this->hasMany('UserFrosting\Paycheck', 'worker_id');
    }

    /**
     * Joins the worker's user record directly, so we can do things like sort, search, paginate, etc.
     */
    public function scopeJoinUser($query)
    {
        return $query->select('worker.*', 'user.user_name as user_name')->join('user', 'worker.user_id', '=', 'user.id');
    }
    
    /**
     * Load basic user info for this worker.
     */
    public function scopeWithUserStub($query)
    {
        return $query->with(['user' => function ($subQuery) {
            return $subQuery->select('id', 'user_name', 'display_name', 'title');
        }]);
    }
    
    /**
     * Joins the worker's most recent task directly, so we can do things like sort, search, paginate, etc.
     */
    public function scopeJoinLatestTask($query, $taskFilter = null)
    {
        $query = $query->select('worker.*', 'task.completed_at as last_task_date');
    
        if ($taskFilter) {
            $filterClause = Capsule::raw("
            (select id from task where
            task.worker_id = worker.id
            and task.description like ?
            order by completed_at desc
            limit 1)");
            return $query->leftJoin('task', 'task.id', '=', $filterClause)->setBindings(["%$taskFilter%"]);
        } else {
            $filterClause = Capsule::raw("
            (select id from task where
            task.worker_id = worker.id
            order by completed_at desc
            limit 1)");
            return $query->leftJoin('task', 'task.id', '=', $filterClause);
        }  
    }
    
    /**
     * Get the most recent task for this worker.
     */
    public function latestTask()
    {
        return $this->hasOne("UserFrosting\Task", "worker_id")->latest();
    }    
}