Level 88
You can do your own query and then manually create the pagination, see this thread: https://laracasts.com/discuss/channels/eloquent/paginate-eloquent-collection
Feb 21, 2016
8
Level 1
Subquery with Eloquent and pagination
Hello,
I have a MySQL database table with a large number of rows. I noticed that the ORDER BY clause is heavily slowing down the query. A nice solution for this is to wrap the query inside a subquery to make it run much faster. In plain MySQL, my query will be:
SELECT * FROM
(SELECT products.id, categories.name FROM products
INNER JOIN categories ON products.category_id=categories.id
) as t ORDER BY id desc
Right now I'm having trouble to translate that query to Eloquent. Since I also want Laravel to paginate the results, it should be something like this:
Product::select('products.id', 'categories.name')
->join('categories', 'products.category_id', '=', 'categories.id')
->orderBy('products.id', 'desc');
->paginate(30);
Obviously, this doesn't do the trick as I'm not sure how to produce a subquery with Eloquent and use pagination on that.
Any help is highly appreciated. Thanks!
Level 1
Thanks for your input Bobby. Your snippet in that topic shows how to use pagination afterwards, very useful imho. Do you also know how to produce a subquery with Eloquent in L5?
Level 88
I assumed you already had a working subquery! I don't think you need a subquery here right?
SELECT products.id, categories.name FROM products
INNER JOIN categories ON products.category_id=categories.id
ORDER BY products.id DESC
This can be translated to this query
$products = Product::join('categories', 'categories.id', '=', 'products.category_id')
->orderBy('products.id')
->get();
Level 1
That's right, I already had a working subquery but that's just plain MySQL.
It's true that I don't need a subquery. However, I have over a million records in the DB table and just writing it out like this makes the DB server awfully slow (query takes over 10 seconds to process):
SELECT products.id, categories.name FROM products
INNER JOIN categories ON products.category_id=categories.id
ORDER BY id desc
I discovered that the ORDER BY clause is the problem. When you leave out the ORDER BY, the query takes less than 2 seconds which is a big difference in performance.
However, I need to order the results somehow, so wrapping it up and placing the ORDER BY clause outside a subquery works perfectly and runs very fast:
SELECT * FROM
(SELECT products.id, categories.name FROM products
INNER JOIN categories ON products.category_id=categories.id
) as t ORDER BY id desc
Problem is I don't know how to write that in Eloquent..
Level 88
In that case you can do it like so
// This returns a Query builder object
$subQuery = Products::select('products.id', 'categories.name')
->join('categories', 'categories.id', '=', 'products.category_id');
$data = DB::table(DB::raw("({$subQuery->toSql()}) as t"))
->mergeBindings($subQuery->getQuery())
->orderBy('id', 'desc')
->get();
Note: I didn't tested this, but I think it should work ;)
4 likes
Level 1
It works, you're a genius! Here's my controller where I put it all together:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Pagination\LengthAwarePaginator;
use Input;
use DB;
use App\Products;
class ProductsController extends Controller {
public function show_products(Request $request) {
$subQuery = Products::select('products.id', 'categories.name')
->join('categories', 'categories.id', '=', 'products.category_id');
$data = DB::table(DB::raw("({$subQuery->toSql()}) as t"))
->mergeBindings($subQuery->getQuery())
->orderBy('id', 'desc')
->get();
$page = Input::get('page', 1);
$perPage = 30;
$offset = ($page * $perPage) - $perPage;
$products = new LengthAwarePaginator(
array_slice($data, $offset, $perPage, true),
count($data),
$perPage,
$page,
['path' => $request->url(), 'query' => $request->query()]
);
return view('show_products')
->with('products', $products);
}
}
Thanks man!
Level 1
@xitec very good answer to the requirement, I get the initial information, only where is the $request->query I guess you send the page number, but when I try to send the parameter comes to me empty, I tried to pass them with the following formats: '?page=2', 'page=2' and '2' and still does not recognize what I send, you pass it in some other way?
Level 88
Cool ;) Eloquent is awesome, but sometimes a single raw query can save the day :)
Please or to participate in this conversation.