Free Continuous integration(CI) for laravel using github actions

Free Continuous integration(CI) for laravel using github actions

https://ift.tt/3pwZDxj

Github provides continuous integration(CI) build pipelines using their service called github actions.

Github actions CI builds are called workflows, and a workflow can be triggered when a certain event happens on your github repository.

Events like a commit is made, a pull request is opened, etc will trigger workflow runs on github actions.

If you are working with a team of developers, Github actions can help you validate the pull requests by running required test cases against a pull request, So we can confidently merge a pull request when it is opened and all the test cases are passed.

Github actions provides unlimited build minutes for public repositories. For private repositories, The free account gives us 2000 build minutes/month. For a solo developer, This is more than enough.

Laravel CI generally consists of various steps to make sure the application runs smoothly when it goes to production. We will create a github actions workflow to perform the following tasks when a pull request is opened.

  • Verify if we can install composer dependencies
  • Verify if npm dependencies can be successfully installed
  • Run the frontend assets build command to verify if we can successfully minify our css and js files
  • Verify if we can run migrations on a database without any issues(We will use a temporary sqlite database for this)
  • Run our unit tests and make sure the code changes in the pull request did not break existing functionality.

Once the above tasks are successfully executed without any issues, Then we can confidently merge the pull request if the code changes looks good.

Create Workflow File For Laravel

All the workflow files should reside inside .github/workflows directory inside your project root.

So first, Create these two directories.

mkdir .github && mkdir .github/workflows

Workflow files uses yaml syntax to define the tasks. A single workflow file can contain multiple tasks.

Let’s create our workflow file inside .github/workflows directory. I am going to call this file laravel-ci.yml. You can use any file name you want. Just make sure the file extension is .yml or .yaml.

touch .github/workflows/laravel-ci.yml

Open the file in your favourite code editor and add the following code in it.

name: Laravel CI

on:
  pull_request:
    branches: [ master, staging ]

jobs:
  laravel-tests:

    runs-on: ubuntu-latest

    steps:
    - uses: actions/[email protected]

The above file provides a basic skeleton for our github actions laravel ci build. It is configured to run the workflow on the latest version of ubuntu operating system when a pull request is opened against master or staging branches.

Our workflow is currently not running any tasks. So let’s start by adding some tasks to this workflow.

1. Setup PHP(Optional)

The ubuntu-latest image comes with the latest version of php installed already. If your application requires php version of 7.3 and lower, Add the below code to steps section in your workflow file to switch to the php version your application is using.

    - name: Setup PHP
      uses: shivammathur/[email protected]
      with:
        php-version: 7.2 # Change to the php version your application is using
        extensions: mbstring, bcmath # Install any required php extensions

2. Create .env file

Every laravel application should have a .env file in it to manage its environment variables. The first task we run on our workflow is copying .env.example file to .env.

Update the steps section in the workflow file with below

    - name: Copy .env.example to .env
      run: php -r "file_exists('.env') || copy('.env.example', '.env');"

3. Install composer dependencies

The next step is installing composer dependencies. By testing the installation of composer dependencies, We are making sure there are no broken packages in the pull request.

    - name: Install composer dependencies
      run: composer install

4. Set our directory permissions correctly

Laravel requires certain directories to be writable by the web server. We can set these directory permissions to 777 as this is just a CI server.

You should never set directory permissions to 777 in your actual server. Setting this might result in unauthorized access to restricted files.

    - name: Set required directory permissions
      run: chmod -R 777 storage bootstrap/cache

5. Generate application encryption key

Since we are creating our .env file freshly, It will not have an encryption key. So let’s create and set an encryption key.

    - name: Generate encryption key
      run: php artisan key:generate

6. Create temporary database

In the next steps, We are going to run database migrations and unit tests. We need a temporary database to run these tasks. We can create a temporary sqlite database by adding the following task.

    - name: Create temporary sqlite database
      run: |
        mkdir -p database
        touch database/database.sqlite

7. Run database migrations

We can run database migrations on the temporary database we created. Doing so we can make sure that the new and existing migration files still work and makes necessary schema changes.

Before we run the migrations, We need to set our DB_CONNECTION and DB_DATABASE environment variables to the newly created temporary sqlite database. We can set these environment variables using the env command as shown below.

    - name: Run laravel database migrations
      env:
        DB_CONNECTION: sqlite
        DB_DATABASE: database/database.sqlite
      run: php artisan migrate --force

8. Install NPM Dependencies

The next step is installing node dependencies. By testing the installation of npm dependencies, We are making sure there are no broken npm packages in the pull request.

    - name: Install NPM dependencies
      run: npm install

9. Minify CSS and JS files

We can run npm run prod command to test and make sure our frontend build command works as expected and minifies our css and js files.

    - name: Minify CSS and JS files
      run: npm run prod

10. Run Unit tests

Finally, Our application is completely ready with all the necessary composer and node packages installed. It has a temporary database, env file, and the frontend assets are compiled and minified.

Now we can dive into running our unit tests to make sure the new code changes did not break existing functionality.

    - name: Run unit tests via PHPUnit
      env:
        DB_CONNECTION: sqlite
        DB_DATABASE: database/database.sqlite
      run: ./vendor/bin/phpunit

Completed workflow file

If you follow all the steps correctly, Your completed workflow file should look like below.

name: Laravel CI

on:
  pull_request:
    branches: [ master, staging ]

jobs:
  laravel-tests:

    runs-on: ubuntu-latest

    steps:
    - uses: actions/[email protected]
    - name: Copy .env.example to .env
      run: php -r "file_exists('.env') || copy('.env.example', '.env');"
    - name: Install composer dependencies
      run: composer install
    - name: Set required directory permissions
      run: chmod -R 777 storage bootstrap/cache
    - name: Generate encryption key
      run: php artisan key:generate
    - name: Create temporary sqlite database
      run: |
        mkdir -p database
        touch database/database.sqlite
    - name: Run laravel database migrations
      env:
        DB_CONNECTION: sqlite
        DB_DATABASE: database/database.sqlite
      run: php artisan migrate --force
    - name: Install NPM dependencies
      run: npm install
    - name: Minify CSS and JS files
      run: npm run prod
    - name: Run unit tests via PHPUnit
      env:
        DB_CONNECTION: sqlite
        DB_DATABASE: database/database.sqlite
      run: ./vendor/bin/phpunit

After you commit the above file to your github repository, Github will run the said tasks when ever a new pull request is opened against master and staging branches.

Failure / Success status of the workflow run will be displayed on the pull request on which it ran.

You can view all the workflow runs triggered in a specific repository by clicking on the actions tab in the navigation menu of a repository page.

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.

programming

via dudi.dev https://ift.tt/3tceeAz

January 2, 2021 at 07:00PM

18 Tips to optimize laravel database queries

18 Tips to optimize laravel database queries

https://ift.tt/3j1kmXQ

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

paginate / simplePaginate
database table has only few rows and does not grow large paginate / simplePaginate
database table has so many rows and grows quickly simplePaginate
it is mandatory to provide the user option to jump to specific pages paginate
it is mandatory to show the user total no of results paginate
not actively using pagination links simplePaginate
UI/UX does not affect from switching numbered pagination links to next / previous pagination links simplePaginate
Using "load more" button or "infinite scrolling" for pagination simplePaginate

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.

programming

via dudi.dev https://ift.tt/3tceeAz

January 4, 2021 at 07:00PM

18 Tips to optimize laravel database queries

18 Tips to optimize laravel database queries

https://ift.tt/3bHGh4H


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

paginate / simplePaginate
database table has only few rows and does not grow large paginate / simplePaginate
database table has so many rows and grows quickly simplePaginate
it is mandatory to provide the user option to jump to specific pages paginate
it is mandatory to show the user total no of results paginate
not actively using pagination links simplePaginate
UI/UX does not affect from switching numbered pagination links to next / previous pagination links simplePaginate
Using “load more” button or “infinite scrolling” for pagination simplePaginate

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.

programming

via Laravel News Links https://ift.tt/2dvygAJ

January 29, 2021 at 03:06PM

What are Laravel Macros and How to Extending Laravel’s Core Classes using Macros with example?

What are Laravel Macros and How to Extending Laravel’s Core Classes using Macros with example?

https://ift.tt/2MjbrVN


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.

programming

via Laravel News Links https://ift.tt/2dvygAJ

January 29, 2021 at 03:06PM

How to Delete Multiple Rows using Checkbox in Laravel?

How to Delete Multiple Rows using Checkbox in Laravel?

https://ift.tt/3taVKAs


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….

programming

via Laravel News Links https://ift.tt/2dvygAJ

January 29, 2021 at 03:06PM

Hoo boy! Watch this mad dad go completely ballistic on his school board and call them “a bunch of cowards hiding behind our children” for refusing to open schools 😮

Hoo boy! Watch this mad dad go completely ballistic on his school board and call them “a bunch of cowards hiding behind our children” for refusing to open schools 😮

https://ift.tt/3iRNjW5

A ticked father unleashed a firestorm of angry logic on the Loudoun County School Board in Virginia on Tuesday, telling them to "open the freaking schools" or to "get off the podium."

fun

via Not the Bee https://notthebee.com

January 27, 2021 at 02:35PM

Use Multiple Databases in Laravel

Use Multiple Databases in Laravel

https://ift.tt/3iU8S8v


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.

programming

via Laravel News Links https://ift.tt/2dvygAJ

January 27, 2021 at 02:54PM

Bazar – a Laravel e-commerce package

Bazar – a Laravel e-commerce package

https://ift.tt/3sXA2A5




Bazar
Thoughtful Laravel e-commerce

GitHub Actions
Coverage Status

Bazar is a powerful “headless” e-commerce system. Built on Laravel and Vue.

Bazar provides a flexible and easily extensible system, respecting the Laravel conventions.

📚 Documentation

  • Installation – Before moving on, please checkout the Laravel documentation about its installation, requirements and configuration.
  • Admin – Bazar provides a simple and extendable admin UI that comes with a lots of built-in functionality. The UI is built on Bootstrap, Vue and Inertia.
  • Cart – Bazar comes with a cart service by default, which manages cart models and their funcionallity.
  • Checkout – The checkout service is no more but a helper class that manages and chains the various steps like updating addresses, creating the order, calculating shipping cost, taxes and discounts.
  • ExtensionsSoon…
  • Gateway – Gateways are responsible to handle the payment or the refund process of an order.
  • Discount – Bazar comes with a flexible discount support by default. You can easily manage discount definitions by using the Bazar\Support\Facades\Discount facade.
  • Media – Bazar comes with a very simple yet flexible and powerful media manager component both on back-end and front-end.
  • Shipping – Shippings are responsible to calculate the cost of a model that implements the Bazar\Contracts\Shippable contract.
  • Tax – Bazar comes with a flexible tax support by default. You can easily manage tax definitions by using the Bazar\Support\Facades\Tax facade.

🤝 Contributing

Thank you for considering contributing to Bazar! The contribution guide can be found in the documentation.

📝 License

Bazar is open-sourced software licensed under the MIT.

programming

via Laravel News Links https://ift.tt/2dvygAJ

January 27, 2021 at 03:24PM

Wacom’s pen tablet for students now works with Chromebooks

Wacom’s pen tablet for students now works with Chromebooks

https://ift.tt/3sQjOIK

For all their strengths as eduction tools, Chromebooks have their limitations. Most don’t come with a stylus, and even if they do, they’re usually not the most accurate. Enter Wacom.   

The company’s One by Wacom tablet now works with Chrome OS devices thanks to the fact it recently earned Google’s Works with Chromebook certification. Not to be confused with the $400 Wacom One tablet, the One by Wacom doesn’t feature a display. Instead, it’s exclusively a compatible surface for the stylus that comes inside the box. While by no means the most capable pen Wacom makes, it’s no slouch either. With 2,048 pressure points, it’s comparable to a previous generation Surface Pen.

One by Wacom
Wacom

To make everything work, you simply connect the pen tablet to your Chromebook through a USB-A port. There’s no need to install any separate drivers or software. What’s more, you don’t even need to charge the stylus as it works without a battery, and if own a Chromebook that already comes with a USI-compatible pen, the One by Wacom stylus won’t interfere with it. With the tablet and pen together weighing in at 259g, the entire device is also light and small enough it can easily fit in a backpack, which makes it easy to see why Wacom thinks it’s a great fit for students.  

As is the case with most accessories, how useful the One by Wacom tablet ends up being will depend on third-party support. Out of the gate, the tablet is fully supported by Clip Studio Paint (as long as you have Chrome OS 8.7 or later installed on your device), as well as education apps like Kami, Pear Deck, Limnu and Explain Everything. It’s also worth noting you can use the device with Mac and Windows computers, so it’s more than just an accessory for Chromebooks. 

The tablet comes in two sizes: small and medium. In the US, it’s only available in the smaller 8.3 by 5.7-inch size, which you can already buy for $59.95.   

Tech

via Engadget http://www.engadget.com

January 26, 2021 at 12:09AM