How to use Laravel Pagination with queries in raw DB::select() format

Question

To get some database information at times it is simpler to make a querie with raw sql than to use multiple models, maps functions and a magic touch to reach the same goal, however the problem of using a raw querie or a raw expression with ease we have to use the function DB::select() and exactly this feature that creates a problem that the return of the data is not compatible with the function of Laravel called paging or Database Pagination that already comes ready with the framework, in my case I tried to use the following way in my controller:

$sql = "SELECT * FROM CLIENTES CL, PEDIDOS PD WHERE PD.ID_CLIENTE = CL.ID";

public function obterDados(){
    return DB::select($sql)->simplePaginate(10);
}

Then the returned result is the error below:

Symfony\Component\Debug\Exception\FatalThrowableError (E_ERROR) "Call to a member function simplePaginate() on array"

Then how to use DB::select() with the pagination function of the Laravel framework?

Answer 1

The answer is as follows, to use the method simplePaginate() it is necessary that my function obterDados() return me an object Builder and not an object array because is the Builder object that contains the method simplePaginate().

The only way I could find to use the framework pagination without changing the SQL query was create a view in the database. This way I could create a template to access this view, this way I was able to solve my problem and the function in my controller was like this:

$sql = "SELECT * FROM CLIENTES CL, PEDIDOS PD WHERE PD.ID_CLIENTE = CL.ID";

public function obterDados(){
    return ViewPedidosClientes::simplePaginate(15);
}