Month: January 2021

If your application is running slow or making a lot of database queries, follow the below performance optimization tips to improve your application loading time.

I shared tips for optimizing your mysql, eloquent and raw database queries.

1. Retrieving large datasets

This tip mainly focuses on improving the memory usage of your application when dealing with large datasets.

If your application needs to process large set of records, instead of retrieving all at once, you can retrieve a subset of results and process them in groups.

To retrieve a large set of results from a table called posts, we would usually do like below.

$posts = Post::all(); // when using eloquent
$posts = DB::table('posts')->get(); // when using query builder

foreach ($posts as $post){
 // Process posts
}

The above examples will retrieve all the records from the posts table and process them. What if this table has 1 million rows? You will easily run out of memory.

To avoid issues when dealing with large datasets, you can retrieve a subset of results and process them as below.

option 1: Using chunk

// when using eloquent
$posts = Post::chunk(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

// when using query builder
$posts = DB::table('posts')->chunk(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

The above example retrieves 100 records from the posts table, processes them, retrieves another 100 records and processes them. This cycle will continue until all the records are processed.

This approach will make more database queries but highly memory efficient. Usually processing of large datasets will be done in the background. So it is ok to make more queries when running in the background to avoid running out of memory when processing large datasets.

option 2: Using cursor

// when using eloquent
foreach (Post::cursor() as $post){
   // Process a single post
}

// when using query builder
foreach (DB::table('posts')->cursor() as $post){
   // Process a single post
}

The above example will make a single database query, retrieve all the records from the table, and hydrate eloquent models one by one. This approach will make only one database query to retrieve all the posts. But uses php generator to optimize the memory usage.

when can you use this?

Though this greatly optimizes the memory usage on the application level, the memory usage on the database instance will still be higher because we are retrieving all the entries in a table.

It is better to use cursor If your web app running your application has less memory, and the database instance has more memory. However, if your database instance does not have enough memory, it is better to stick to chunk.

option 3: Using chunkById

Note: This feature is available only in laravel 8 and higher.

// when using eloquent
$posts = Post::chunkById(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

// when using query builder
$posts = DB::table('posts')->chunkById(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

The major difference between chunk and chunkById is that chunk retrieves based on offset and limit. Whereas chunkById retrieves database results based on an id field. This id field usually be an integer field, and in most cases it would be an auto incrementing field.

The queries made by chunk and chunkById were as follows.

chunk

select * from posts offset 0 limit 100

select * from posts offset 101 limit 100

chunkById

select * from posts order by id asc limit 100

select * from posts where id > 100 order by id asc limit 100

Generally limit and offset are slower, and we should try to avoid usage of them. This article explains in detail about the problem with using offset. As chunkById is using the id field which is an integer field, and the query is using a where clause, the query will be much faster.

when can you use chunkById? – If your application is running laravel 8 or higher – If your database table has an id column which is an integer field.

2. Select only the columns you need

Usually to retrieve results from a database table, we would do the following.

$posts = Post::find(1); //When using eloquent
$posts = DB::table('posts')->where('id','=',1)->first(); //When using query builder

The above code will result in a query as below

select * from posts where id = 1 limit 1

As you can see, the query is doing a select *. This means it is retrieving all the columns from the database table. This is fine if we really need all the columns from the table.

Instead, if we need only specific columns(id, title), we can retrieve only those columns as below.

$posts = Post::select(['id','title'])->find(1); //When using eloquent
$posts = DB::table('posts')->where('id','=',1)->select(['id','title'])->first(); //When using query builder

The above code will result in a query as below

select id,title from posts where id = 1 limit 1

3. Use pluck when you need exactly one or two columns from the database

This tip focuses more on the time spent after the results are retrieved from the database. This does not affect the actual query time.

As I mentioned above, to retrieve specific columns, we would do

$posts = Post::select(['title','slug'])->get(); //When using eloquent
$posts = DB::table('posts')->select(['title','slug'])->get(); //When using query builder

When the above code is executed, it does the following behind the scenes.

• Executes select title, slug from posts query on the database
• Creates a new Post model object for each row it retrieved(For query builder, it creates a PHP standard object)
• Creates a new collection with the Post models
• Returns the collection

Now, to access the results, we would do

foreach ($posts as $post){
    // $post is a Post model or php standard object
    $post->title;
    $post->slug;
}

The above approach has an additional overhead of creating Post model for each and every row and creating a collection for these objects. This would be best if you really need the Post model. But if all that you need is those two values, you can do the following.

$posts = Post::pluck('title', 'slug'); //When using eloquent
$posts = DB::table('posts')->pluck('title','slug'); //When using query builder

When the above code is executed, it does the following behind the scenes.

• Executes select title, slug from posts query on the database
• Creates an array with title as array value and slug as array key.
• Returns the array(array format: [ slug => title, slug => title ])

Now, to access the results, we would do

foreach ($posts as $slug => $title){
    // $title is the title of a post
    // $slug is the slug of a post
}

If you want to retrieve only one column, you can do

$posts = Post::pluck('title'); //When using eloquent
$posts = DB::table('posts')->pluck('title'); //When using query builder
foreach ($posts as  $title){
    // $title is the title of a post
}

The above approach eliminates the creation of Post objects for every row. Thus reducing the memory usage and time spent on processing the query results.

I would recommend using the above approach on new code only. I personally feel going back and refactoring your code to follow the above tip is not worthy of the time spent on it. Refactor existing code only if your code is processing large datasets or if you have free time to spare.

4. Count rows using query instead of collection

To count the total no of rows in a table, we would normally do

$posts = Post::all()->count(); //When using eloquent
$posts = DB::table('posts')->get()->count(); //When using query builder

This will generate the following query

select * from posts

The above approach will retrieve all the rows from the table, load them into a collection object, and counts the results. This works fine when there are less rows in the database table. But we will quickly run out of memory as the table grows.

Instead of the above approach, we can directly count the total no of rows on the database itself.

$posts = Post::count(); //When using eloquent
$posts = DB::table('posts')->count(); //When using query builder

This will generate the following query

select count(*) from posts

Counting rows in sql is a slow process and performs very poorly when the database table has so many rows. It is better to avoid counting of rows as much as possible.

5. Avoid N+1 queries by eager loading relationship

You might have heard of this tip a million times. So I will keep it as short and simple as possible. Let’s assume you have the following scenario

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        return view('posts.index', ['posts' => $posts ]);
    }
}

// posts/index.blade.php file

@foreach($posts as $post)
    <li>
        <h3></h3>
        <p>Author: </p>
    </li>
@endforeach

Above code is retrieving all the posts and displaying the post title and its author on the webpage. The above code assumes you have an author relationship on your post model.

Executing the above code will result in running following queries.

select * from posts // Assume this query returned 5 posts
select * from authors where id = { post1.author_id }
select * from authors where id = { post2.author_id }
select * from authors where id = { post3.author_id }
select * from authors where id = { post4.author_id }
select * from authors where id = { post5.author_id }

As you can see, we have one query to retrieve posts, and 5 queries to retrieve authors of the posts(Since we assumed we have 5 posts.) So for every post it retrieved, it is making one separate query to retrieve its author.

So if there are N number of posts, it will make N+1 queries( 1 query to retrieve posts and N queries to retrive author for each post). This is commonly known as N+1 query problem.

To avoid this, eager load the author’s relationship on posts as below.

$posts = Post::all(); // Avoid doing this
$posts = Post::with(['author'])->get(); // Do this instead

Executing the above code will result in running following queries.

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

6. Eager load nested relationship

From the above example, consider the author belongs to a team, and you wish to display the team name as well. So in the blade file you would do as below.

@foreach($posts as $post)
    <li>
        <h3></h3>
        <p>Author: </p>
        <p>Author's Team: </p>
    </li>
@endforeach

Now doing the below

$posts = Post::with(['author'])->get();

Will result in following queries

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id = { author1.team_id }
select * from teams where id = { author2.team_id }
select * from teams where id = { author3.team_id }
select * from teams where id = { author4.team_id }
select * from teams where id = { author5.team_id }

As you can see, even though we are eager loading authors relationship, it is still making more queries. Because we are not eager loading the team relationship on authors.

We can fix this by doing the following.

$posts = Post::with(['author.team'])->get();

Executing the above code will result in running following queries.

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id in( { author1.team_id }, { author2.team_id }, { author3.team_id }, { author4.team_id }, { author5.team_id } )

So by eager loading the nested relationship, we reduced the total no of queries from 11 to 3.

7. Do not load belongsTo relationship if you just need its id

Imagine you have two tables posts and authors. Posts table has a column author_id which represents a belongsTo relationship on the authors table.

To get the author id of a post, we would normally do

$post = Post::findOrFail(<post id>);
$post->author->id; 

This would result in two queries being executed.

select * from posts where id = <post id> limit 1
select * from authors where id = <post author id> limit 1

Instead, you can directly get the author id by doing the following.

$post = Post::findOrFail(<post id>);
$post->author_id; // posts table has a column author_id which stores id of the author

When can I use the above approach?

You can use the above approach when you are confident that a row always exists in authors table if it is referenced in posts table.

8. Avoid unnecessary queries

Often times, we make database queries which are not necessary. Consider the below example.

<?php

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        $private_posts = PrivatePost::all();
        return view('posts.index', ['posts' => $posts, 'private_posts' => $private_posts ]);
    }
}

The above code is retrieving rows from two different tables(ex: posts, private_posts) and passing them to view. The view file looks as below.

// posts/index.blade.php

@if( request()->user()->isAdmin() )
    <h2>Private Posts</h2>
    <ul>
        @foreach($private_posts as $post)
            <li>
                <h3></h3>
                <p>Published At: </p>
            </li>
        @endforeach
    </ul>
@endif

<h2>Posts</h2>
<ul>
    @foreach($posts as $post)
        <li>
            <h3></h3>
            <p>Published At: </p>
        </li>
    @endforeach
</ul>

As you can see above, $private_posts is visible to only a user who is an admin. Rest all the users cannot see these posts.

The problem here is, when we are doing

$posts = Post::all();
$private_posts = PrivatePost::all();

We are making two queries. One to get the records from posts table and another to get the records from private_posts table.

Records from private_posts table are visible only to the admin user. But we are still making the query to retrieve these records for all the users even though they are not visible.

We can modify our logic to below to avoid this extra query.

$posts = Post::all();
$private_posts = collect();
if( request()->user()->isAdmin() ){
    $private_posts = PrivatePost::all();
}

By changing our logic to above, we are making two queries for the admin user and one query for all other users.

9. Merge similar queries together

We some times need to make queries to retrieve different kinds of rows from the same table.

$published_posts = Post::where('status','=','published')->get();
$featured_posts = Post::where('status','=','featured')->get();
$scheduled_posts = Post::where('status','=','scheduled')->get();

The above code is retrieving rows with a different status from the same table. The code will result in making following queries.

select * from posts where status = 'published'
select * from posts where status = 'featured'
select * from posts where status = 'scheduled'

As you can see, it is making 3 different queries to the same table to retrieve the records. We can refactor this code to make only one database query.

$posts =  Post::whereIn('status',['published', 'featured', 'scheduled'])->get();
$published_posts = $posts->where('status','=','published');
$featured_posts = $posts->where('status','=','featured');
$scheduled_posts = $posts->where('status','=','scheduled');

select * from posts where status in ( 'published', 'featured', 'scheduled' )

The above code is making one query to retrieve all the posts which has any of the specified status and creating separate collections for each status by filtering the returned posts by their status. So we will still have three different variables with their status and will be making only one query.

10. Add index to frequently queried columns

If you are making queries by adding a where condition on a string based column, it is better to add an index to the column. Queries are much faster when querying rows with an index column.

$posts = Post::where('status','=','published')->get();

In the above example, we are querying records by adding a where condition to the status column. We can improve the performance of the query by adding the following database migration.

Schema::table('posts', function (Blueprint $table) {
   $table->index('status');
});

11. Use simplePaginate instead of Paginate

When paginating results, we would usually do

$posts = Post::paginate(20);

This will make 2 queries. 1 to retrieve the paginated results and another to count the total no of rows in the table. Counting rows in a table is a slow operation and will negatively effect the query performance.

So why does laravel count the total no of rows?

To generate pagination links, Laravel counts the total no of rows. So, when the pagination links are generated, you know before-hand, how many pages will be there, and what is the past page number. So you can navigate to what ever the page you want easily.

On the other hand, doing simplePaginate will not count the total no of rows and the query will be much faster than the paginate approach. But you will lose the ability to know the last page number and able to jump to different pages.

If your database table has so many rows, it is better to avoid paginate and do simplePaginate instead.

$posts = Post::paginate(20); // Generates pagination links for all the pages
$posts = Post::simplePaginate(20); // Generates only next and previous pagination links

When to use paginate vs simple paginate?

Look at the below comparison table and determine if paginate or simple paginate is right for you

12. Avoid using leading wildcards(LIKE keyword)

When trying to query results which match a specific pattern, we would usually go with

select * from table_name where column like %keyword%

The above query will result in a full table scan. If We know the keyword occurs at the beginning of the column value, We can query the results as below.

select * from table_name where column like keyword%

13. avoid using SQL functions in where clause

It is always better to avoid SQL functions in where clause as they result in full table scan. Let’s look at the below example. To query results based on the certain date, we would usually do

    $posts = POST::whereDate('created_at', '>=', now() )->get();

This will result in a query similar to below

select * from posts where date(created_at) >= 'timestamp-here'

The above query will result in a full table scan, because the where condition isn’t applied until the date function is evaluated.

We can refactor this to avoid the date sql function as below

    $posts = Post::where('created_at', '>=', now() )->get();

select * from posts where created_at >= 'timestamp-here'

14. avoid adding too many columns to a table

It is better to limit the total no of columns in a table. Relational databases like mysql, can be leveraged to split the tables with so many columns into multiple tables. They can be joined together by using their primary and foreign keys.

Adding too many columns to a table will increase the individual record length and will slow down the table scan. When you are doing a select * query, you will end up retrieving a bunch of columns which you really do not need.

15. separate columns with huge data into their own table

This tip is from personal experience and is not a standard way of architecting your database tables. I recommend to follow this tip only if your table has too many records or will grow rapidly.

If a table has columns which stores large amounts of data(ex: columns with a datatype of TEXT), it is better to separate them into their own table are into a table which will be less frequently asked.

When the table has columns with large amounts of data in it, the size of an individual record grows really high. I personally observed it affected the query time.

Consider a case where you have a table called posts with a column of content which stores the blog post content. The content for blog post will be really huge and often times, you need this data only if a person is viewing this particular blog post.

So separating this column from the posts table will drastically improve the query performance.

16. Better way to retrieve latest rows from a table

When we want to retrieve latest rows from a table, we would often do

$posts = Post::latest()->get();
// or $posts = Post::orderBy('created_at', 'desc')->get();

The above approach will produce the following sql query.

select * from posts order by created_at desc

The query is basically ordering the rows in descending order based on the created_at column. Since created_at column is a string based column, it is often slower to order the results this way.

If your database table has an auto incrementing primary key id, yhen in most cases, the latest row will always have the highest id. Since id field is an integer field and also a primary key, it is much faster to order the results based on this key. So the better way to retrieve latest rows is as below.

$posts = Post::latest('id')->get();
// or $posts = Post::orderBy('id', 'desc')->get();

select * from posts order by id desc

17. optimize mysql inserts

We so far looked into optimizing select queries for retrieving results from a database. Most cases we only need to optimize the read queries. But sometimes we find a need to optimize insert and update queries. I found an interesting article by atymic on optimizing mysql inserts be sure to check it out.

18. Inspect and optimize queries

There is no one universal solution when optimizing queries in laravel. Only you know what your application is doing, how many queries it is making, how many of them are actually in use. So inspecting the queries made by your application will help you determine and reduce the total number of queries made.

There are certain tools which helps you in inspecting the queries made on each and every page.

Note: It is recommended not to run any of these tools on your production environment. Running these on your production apps will degrade your application performance and when compromised, unauthorized users will get access to a lot of sensitive information.

• Laravel Debugbar – Laravel debugbar has a tab called database which will display all the queries executed when you visit a page. Visit all the pages in your application and look at the queries executed on each page.
• Clockwork – Clockwork is same as laravel debugbar. But instead of injecting a toolbar into your website, it will display the debug information in developer tools window or as a standalone UI by visiting yourappurl/clockwork.
• Laravel Telescope – Laravel telescope is a wonderful debug companion while developing laravel applications locally. Once telescope is installed, you can access the dashboard by visitng yourappurl/telescope. In the telescope dashboard, head over to queries tab, and it will display all the queries being executed by your application.

I hope, you enjoyed reading this article. Check out my latest posts and follow me on twitter to get notified when i post a new one.

Laravel Macros are a great way of expanding Laravel’s core macroable classes and add additional functionality needed for your application. In simple word, Laravel Macro is an approach to add some missing functionality to Laravel’s core component with a piece of code which doesn’t exist in the Laravel class. To implement a Laravel Macro, Laravel gives a PHP trait called Macroable. You can check Illuminate\Http\Response class of Laravel, which implements the Macroable trait, which implies you can extend the Illuminate\Http\Response class using a macro static method.

Registering a new macro

All macros should be registered inside the boot method in the service provider. You can create a dedicated service provider for macros, or you can add macros in the AppServiceProvider, shipped with the default Laravel installation. That is totally up to you.

Using anonymous function

This approach is a straightforward approach to add a new macro is putting it just inside the boot method for AppServiceProvider.

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        Collection::macro('name it here', function(){
            // Pass your actual code here.
        });
    }
}

And you are done.

Here is an example of toUpper macro:

use Illuminate\Support\Str;

Collection::macro('toUpper', function () {
    return $this->map(function ($value) {
        return Str::upper($value);
    });
});

$collection = collect(['first', 'second']);

$upper = $collection->toUpper();

// ['FIRST', 'SECOND']

Using mixins

If you want to use lots of macros from a particular Laravel class. For this situation, your boot method of service provider most likely gets bigger with various macros, and the code begins looking messy.

To isolate your code, you can utilize mixins.

For this approach, you should utilize a mixin static method on the macroable class, and pass your mixin class as an argument.

Let’s take a look at an example. Let’s say, we have a Macros/QueryBuilderMacros.php file with the following content.

class QueryBuilderMacros
{
    public function one()
    {
        // macro content
    }

    protected function two()
    {
        // macro content
    }

    private function three()
    {
        // will not become a macro
    }
}

The following code is the example of mixin to register QueryBuilderMacros class with its methods.

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        Builder::mixin(new QueryBuilderMacros());
    }
}

That is it. Methods of QueryBuilderMacros class, one and two will become available on each Builder class instance. Method three declared as private, will remain accessible just for methods in QueryBuilderMacros class, you know since private should remain private.

Conclusion

Laravel’s macros are a compelling component, which opens a lot of conceivable outcomes to expand the Laravel framework, and helps to get rid of repeating the same logic across the application.

It’s almost need to give feature for remove multiple records using checkbox, if you are developing e-commerce application or any big web application then you must give feature to delete multiple records.

So in this post, i will let you know how to delete multiple records with checkbox in laravel 5, laravel 6, laravel 7 and laravel 8 application. here i also give multiple delete and you can also delete single records. Few days ago i posted for confirmation before delete record, so you can read from here : Laravel 5 – Confirmation before delete record from database example.

In this example, i simply created “products” table with id, name, details, created_at and updated_at columns. I also added mysql query for add dummy records. Here i use jquery for select all checkboxs and delete all records. So finally you have to follow some step and get the layout like as bellow.

Preview:

Step 1: Create products Table with Dummy Records

Here, you have to create “products” table then you can run mysql query for dummy records. You can create products table using migration and then also create some dummy records using seeder. So now i just simple sql query.

Dummy Records Query:

INSERT INTO `products` (`id`, `name`, `details`, `created_at`, `updated_at`) VALUES
(1, 'Laravel', 'Laravel posts', NULL, NULL),
(3, 'PHP', 'PHP posts', NULL, NULL),
(4, 'JQuery', 'JQuery posts', NULL, NULL),
(5, 'Bootstrap', 'Bootstrap posts', NULL, NULL),
(6, 'Ajax', 'Ajax posts', NULL, NULL);

Step 2: Create new Routes

In this step, we are doing from scratch so we will add three routes, one for display data and another for delete request, then third for remove all selected data. So you have to simply add three new routes in your laravel application.

routes/web.php

Route::get('myproducts', '[email protected]');
Route::delete('myproducts/{id}', '[email protected]');
Route::delete('myproductsDeleteAll', '[email protected]');

Step 3: Add ProductController

Here, we will create new ProductController file to handle request of created three new route. In this Controller we define three method, index(), destroy() and deleteAll(). method will handle route request. So let’s create new controller and put code:

app/Http/Controllers/ProductController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class ProductController extends Controller
{
    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $products = DB::table("products")->get();
        return view('products',compact('products'));
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
    	DB::table("products")->delete($id);
    	return response()->json(['success'=>"Product Deleted successfully.", 'tr'=>'tr_'.$id]);
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Http\Response
     */
    public function deleteAll(Request $request)
    {
        $ids = $request->ids;
        DB::table("products")->whereIn('id',explode(",",$ids))->delete();
        return response()->json(['success'=>"Products Deleted successfully."]);
    }
}

Step 4: Add Blade File

In last step, we will create products.blade.php file and write code of jquery for delete and delete all function. So let’s create products.blade.php file and put bellow code:

resources/views/products.blade.php

<!DOCTYPE html>
<html>
<head>
    <title>Laravel 5 - Multiple delete records with checkbox example</title>

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-confirmation/1.0.5/bootstrap-confirmation.min.js"></script>
    <meta name="csrf-token" content="">

</head>
<body>

<div class="container">
    <h3>Laravel 5 - Multiple delete records with checkbox example</h3>
    <button style="margin-bottom: 10px" class="btn btn-primary delete_all" data-url="">Delete All Selected</button>
    <table class="table table-bordered">
        <tr>
            <th width="50px"><input type="checkbox" id="master"></th>
            <th width="80px">No</th>
            <th>Product Name</th>
            <th>Product Details</th>
            <th width="100px">Action</th>
        </tr>
        @if($products->count())
            @foreach($products as $key => $product)
                <tr id="tr_">
                    <td><input type="checkbox" class="sub_chk" data-id=""></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td>
                         <a href="" class="btn btn-danger btn-sm"
                           data-tr="tr_"
                           data-toggle="confirmation"
                           data-btn-ok-label="Delete" data-btn-ok-icon="fa fa-remove"
                           data-btn-ok-class="btn btn-sm btn-danger"
                           data-btn-cancel-label="Cancel"
                           data-btn-cancel-icon="fa fa-chevron-circle-left"
                           data-btn-cancel-class="btn btn-sm btn-default"
                           data-title="Are you sure you want to delete ?"
                           data-placement="left" data-singleton="true">
                            Delete
                        </a>
                    </td>
                </tr>
            @endforeach
        @endif
    </table>
</div> <!-- container / end -->

</body>

<script type="text/javascript">
    $(document).ready(function () {

        $('#master').on('click', function(e) {
         if($(this).is(':checked',true))  
         {
            $(".sub_chk").prop('checked', true);  
         } else {  
            $(".sub_chk").prop('checked',false);  
         }  
        });

        $('.delete_all').on('click', function(e) {

            var allVals = [];  
            $(".sub_chk:checked").each(function() {  
                allVals.push($(this).attr('data-id'));
            });  

            if(allVals.length <=0)  
            {  
                alert("Please select row.");  
            }  else {  

                var check = confirm("Are you sure you want to delete this row?");  
                if(check == true){  

                    var join_selected_values = allVals.join(","); 

                    $.ajax({
                        url: $(this).data('url'),
                        type: 'DELETE',
                        headers: {'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')},
                        data: 'ids='+join_selected_values,
                        success: function (data) {
                            if (data['success']) {
                                $(".sub_chk:checked").each(function() {  
                                    $(this).parents("tr").remove();
                                });
                                alert(data['success']);
                            } else if (data['error']) {
                                alert(data['error']);
                            } else {
                                alert('Whoops Something went wrong!!');
                            }
                        },
                        error: function (data) {
                            alert(data.responseText);
                        }
                    });

                  $.each(allVals, function( index, value ) {
                      $('table tr').filter("[data-row-id='" + value + "']").remove();
                  });
                }  
            }  
        });

        $('[data-toggle=confirmation]').confirmation({
            rootSelector: '[data-toggle=confirmation]',
            onConfirm: function (event, element) {
                element.trigger('confirm');
            }
        });

        $(document).on('confirm', function (e) {
            var ele = e.target;
            e.preventDefault();

            $.ajax({
                url: ele.href,
                type: 'DELETE',
                headers: {'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')},
                success: function (data) {
                    if (data['success']) {
                        $("#" + data['tr']).slideUp("slow");
                        alert(data['success']);
                    } else if (data['error']) {
                        alert(data['error']);
                    } else {
                        alert('Whoops Something went wrong!!');
                    }
                },
                error: function (data) {
                    alert(data.responseText);
                }
            });

            return false;
        });
    });
</script>

</html>

Now we are ready to run our example so run bellow command for quick run:

php artisan serve

Now you can open bellow url on your browser:

http://localhost:8000/myproducts

I hope it can help you….

Laravel is a free, open-source PHP framework, created by Taylor Otwell. It follows a Model-View-Controller (MVC) design pattern. Laravel reuses the existing components of different frameworks which helps to create a perfect and outstanding web application. If you are familiar with PHP, Advance PHP it will make your task easier. Laravel is secure and prevents web attacks using their CSRF (Cross-site Request Forgery) protection.

While developing a web application some times we need to use multiple databases because of project requirement or large scale projects, in this time laravel allows to use multiple database connections. You can easily learn how to use multiple databases with laravel in this blog.

Add Database Connection Detail in .env

DB_HOST_SECOND="DB HOST HERE"
DB_PORT_SECOND="DB PORT HERE"
DB_DATABASE_SECOND="DATABASE NAME HERE"
DB_USERNAME_SECOND="DB USERNAME HERE"
DB_PASSWORD_SECOND="DB PASSWORD HERE"

As you can see, you can go with multiple if you want to use more databases, also you can go with other database engines. Here we use MySQL.

Configure database detail in config/database.php

add detail in the connections array.

<?php

return [
   'connections'=>[
     'mysql'=>[
       'driver' => 'mysql',
       'url' => env('DATABASE_URL'),
       'host' => env('DB_HOST', '127.0.0.1'),
       'port' => env('DB_PORT', '3306'),
       'database' => env('DB_DATABASE', 'forge'),
       'username' => env('DB_USERNAME', 'forge'),
       'password' => env('DB_PASSWORD', ''),
     ],
     'mysql_second'=>[
       'driver' => 'mysql',
       'url' => env('DATABASE_URL'),
       'host' => env('DB_HOST_SECOND', '127.0.0.1'),
       'port' => env('DB_PORT_SECOND', '3306'),
       'database' => env('DB_DATABASE_SECOND', 'forge'),
       'username' => env('DB_USERNAME_SECOND', 'forge'),
       'password' => env('DB_PASSWORD_SECOND', ''),
     ]
   ]
];

?>

Note: Add All Detail Of Database Connection Here we add only database detail for learning purposes.

Now We learn How to use this connection with Schema, Query, and Eloquent Model.

Schema Builder

With Schema Builder, You can use any connection by simply run the connection() method:

Schema::connection('mysql_second')->create('table_name', function($table){
   // entire code here
});

Query Builder

Similar to Schema Builder, you can define a connection in Query Builder:

$posts = \DB::connection('mysql_second')->select('id','title')->get();

Eloquent Model

Similar to Query Builder, you can define a connection in the Eloquent Model:

<?php

namespace App\Models;

class Post extends Eloquent {
   protected $connection = 'mysql_second';
}

?>

Also, you can use the connection in custom join queries. with this simple example:

\DB::table('posts')->join('mysql_second.types as secondDbType','posts.code','=','secondDbType.code')->first();

If you have any queries or doubts about this topic please feel free to contact us. We will try to reach you.

Thank You.