Optimize Laravel Query Builder with Joins

Question

I'm trying to optimize the following query in Laravel using query builder and joins:

Product::select(DB::raw('
            products.*
            ,(select name from users where users.id=products.user_id) as user_name
          '))
                ->where(function ($query) use ($searchKey) {
                    if (trim($searchKey) != '') {
                        $query->where('name', 'like', '%' . trim($searchKey) . '%');
                    }
                })
                ->orderBy($orderBy, $orderType)
                ->paginate(10);

Is there any further optimization or best practice that I can apply to this query?

Answer 1

Maybe something like this:

//Start your query as usual, but just join the user data in. Do not use paginate function yet as this will trigger the query to execute.
$productQuery = Product
    ::selectRaw('products.*, users.name as user_name')
    ->join('users', 'users.id', 'products.user_id')
    ->orderBy($orderBy, $orderType);
//Check (for each param) if you want to add where clause
//You dont need to nest the where function, unless you have more filters, and need to group statements together
if(!empty(trim($searchKey))) {
    $productQuery->where('name', 'like', '%' . trim($searchKey) . '%');
}
//Execute query
$products = $productQuery->paginate(10);

Note that the query builder only touches the db with specific functions like chunk, get, first or paginate(there are more). When building the query, you have full freedom of adding filters/ordering/grouping untill you execute the query.

I hope it helps, please let me know if it worked for you.