pagination on complex sql

Why don't you just cache the queries result for a set period of time?

I think its better to limit your SQL statement, its faster,

$users = DB::table('users')->skip(10)->take(5)->get();

http://laravel.com/docs/5.0/queries

You can use the Skip and Take on your select Query

@jackpopp not sure what you mean... @bingvanmoorsel $documents = DB::select( DB::raw($selectstatement), $binders)->skip(10)->take(5)->get(); this is OK (i dont think so) ? can i use the out of the box pagination display by supplying the value and rendering?

I think you should be fine. skip the amount you already had, and take the amount per page of your pagination.

$documents = DB::select( DB::raw($selectstatement), $binders)->skip(10)->take(5)->get(); ... skip fails

Cant you use a model? If you use a model Laravel does the pagination part for you ?

I dont think you can use skip and take if you use DB::raw, in that case you need to add the limit and offset option to the query yourself.

Dont use DB::raw if you dont need to

My idea would be when the query is run you cache the result and then you could just look in the cache for that query instead of hitting the database so it would speed things up. You could then just run the paginator on the result, but many that suggestion isn't applicable to your use case.

if you saw this complex sql ... very dynamic and not sure how i could....

Could you share your SQL ?

Otherwise, at the end of your SQL add a LIMIT and OFFSET.

select documents.*,metatags_1.value_string as field_1,metatags_2.value_string as field_2,metatags_3.value_integer as field_3,metatags_4.value_string as field_4,metatags_5.value_date as field_5 FROM documents LEFT JOIN metatags as metatags_1 ON ((documents.id = metatags_1.document_id) and (metatags_1.field_id = 1)) LEFT JOIN metatags as metatags_2 ON ((documents.id = metatags_2.document_id) and (metatags_2.field_id = 2)) LEFT JOIN metatags as metatags_3 ON ((documents.id = metatags_3.document_id) and (metatags_3.field_id = 3)) LEFT JOIN metatags as metatags_4 ON ((documents.id = metatags_4.document_id) and (metatags_4.field_id = 4)) LEFT JOIN metatags as metatags_5 ON ((documents.id = metatags_5.document_id) and (metatags_5.field_id = 5)) where (documents.type_id = 2) and (UPPER(metatags_2.value_string) like :search_1_1) and (UPPER(documents.name) like :search_1_2) and ( (metatags_5.value_date >= :search_1_3) and (metatags_5.value_date <= :search_2_3) ) ORDER BY documents.name desc

did the sql scare you off :) @bingvanmoorsel

Going to be honest, a little scared by the table names more than anything!

As mentioned previously, I think without looking at changing the table structure / relationships, you're going to need to insert the offset and limit manually into the query. Of course, if you do this, be very careful of SQL injection.

structure is basically a documents table with a details table metatags. each document is assigned a category/Type. Each Type has Fields. Each Field has a type (int, string, date, etc). Each document has metatags - field id, value for that document... this was the best way i could do the SQL but i am sure it could be done better :)

Type : 1 = Invoice, 2=Mortgage, etc

Fields : 1=Invoice # (typeid = 1), 2=Register Date (type_id=1), 3=Buyer Name (type_id=2)

Document : 1 = 12345.pdf (type_id=1) , 2=M23677.pdf (type_id=2)

Metatags : document_id=1, field_id=1, value_integer=12345 ; document_id=1, field_id=2, value_date=2015-01-01 ; document_id=2, field_id=3, value_integer=23677;

Not sure how open you are to structure reconfigurations, but it would be worth you looking into polymorphism if you are. http://laravel.com/docs/5.0/eloquent#polymorphic-relations

You could have the following models:

• Document
• Invoice
• Mortgage... etc

Where all are a morph of "document". The invoice table would have metadata relating only to invoices, and mortgage meta for mortgages etc.

Doing this, you could fetch all invoices, fetch everything (documents) or whatever you please, and do pagination fairly easily for each of these combinations.

That being said, if you are too far in to the project to make this change, I'd just do the manual pagination insertion.

@taijuten I kind of understand this BUT ... the Invoice and Mortgage example are defined by the user in an individual account... these are NOT object model i can design before but would need to be designed (and altered) dynamically. I would love ot be able to say Invoice:all(); ... To do this as a user created a new Type I would need to build the model?

@tebowner I know you wont like the idea of doing that but: Why dont you convert the query into the query builder, without the DB::raw. If you build it in your query builder you can actualy use the ->skip() and ->take(). This makes your query secure aswell.

@bingvanmoorsel the issue is CAN my monster dynamic query fit into a query builder? you can see the query in previous comment

@tebowner

i think most of it can be changed to the laravel Querybuilder, everything you cant add normaly ( like the join condistions) you can use the DB::raw.

I also think you can add a OFFSET and LIMIT in your query. get the value's out of your paginator.

let me know ;-)

i dont know ... too confusing to add the joins which grabs the dynamic fields etc etc ... will be looking

I had to paginate a many to many relationship and made this code. Hopefully it may be useful for someone. However, it may contain unnoticed bugs, but it seems to work fine). Improvements would be appreciated :)

In controller

public function index(Request $request)
{
    $searchTerm = Input::get('search');

    $searchOperator = Input::get('searchOperator') ? Input::get('searchOperator') : 'LIKE';

    $offset = Input::get('offset') ? Input::get('offset') : 5;
  
    $data =  $this->repository->resultsPaginated($searchOperator, $searchTerm, $offset),

    return view('pages.index', compact('data'));
}

In repository

 public function resultsPaginated($searchOperator, $searchTerm, $offset) {
    $currentPage = LengthAwarePaginator::resolveCurrentPage();

    if($currentPage >= 1)
        $skip = ($currentPage -1) * $offset;
    else
        $skip = 0;

    $query = DB::connection('sqlite_nessus')->table('t1')
        ->join('t2', 't1.id', '=', 't2.t1id')
        ->join('t3', 't3.id', '=', 't2.t3id')
        ->where('t3.someColumn', $searchOperator, $searchTerm);

    $count = $query->count();

    $results = $query
        ->skip($skip)
        ->take($offset)
        ->select('t1.id', 't2.id', 't3.id')
        ->get();
    

    return new LengthAwarePaginator($results, $count, $offset, $currentPage, [
        'path' => LengthAwarePaginator::resolveCurrentPath(),
        'offset' => $offset
    ]);
}