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?

Use a join, good luck

Joel Harkes
– Joel Harkes

2020-04-13 15:47:44 +00:00
Commented Apr 13, 2020 at 15:47 — Joel Harkes
– Joel Harkes, Commented Apr 13, 2020 at 15:47

Techno · Accepted Answer · 2020-04-13 15:50:28Z

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.

answered Apr 13, 2020 at 15:50

Techno

1,6911 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Muhammad Bilal Over a year ago

Thanks, it's work for me with a small modification. $productQuery->where('products.name', 'like', '%' . trim($searchKey) . '%');

Techno Over a year ago

Nice! Could you mark as answer then please. Then i'm helped too :D

Collectives™ on Stack Overflow

Optimize Laravel Query Builder with Joins

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related