Nuffing.com

https://picperf.io/https://laravelnews.s3.amazonaws.com/featured-images/laravel-pagination.png

Pagination is a common feature in web applications. Almost every Laravel application I’ve ever worked on has had some form of pagination implemented.

But what is pagination and why do we use it? How can we implement pagination in our Laravel applications? And how do we decide which pagination method to use?

In this article, we’re going to answer those very questions and explore how to use pagination in Laravel for both Blade views and API endpoints. By the end of this article, you should feel confident enough to start using pagination in your own projects.

What is Pagination?

Pagination is a technique used to divide a large dataset into smaller chunks (or pages). It allows you to display a subset of the data, rather than all the possible values at once.

For instance, imagine you had a page that outputs the names of all the users in your application. If you had thousands of users, it wouldn’t be practical to display them all on a single page. Instead, you could use pagination to display a subset of the users (say 10 users at a time) on each page, and allow users to navigate between the pages to view more users (the next 10).

By using pagination you can:

• Improve the performance of your application – Since you’re fetching a smaller subset of data at a time, there’s less data for you to fetch from the database, process/transform, and then return.
• Improve the user experience – It’s likely that the user will only ever be interested in a small subset of the data at a time (typically found in the first few pages, especially if filters and search terms are used). By using pagination, you can avoid displaying data that the user is not interested in.
• Improve page loading times – By only fetching a subset of the data at a time, you can reduce the amount of data that needs to be loaded onto the page, which can improve page loading and JavaScript processing times.

Pagination can typically be split into two different types:

• Offset-based pagination – This is the most common type of pagination you’ll likely come across in your web apps, especially in user interfaces (UI). It involves fetching a subset of data from the database based on an "offset" and a "limit". For example, you might fetch 10 records starting from the 20th record to fetch the 3rd page of data.
• Cursor-based pagination – This type of pagination involves fetching a subset of data based on a "cursor". The cursor is typically a unique identifier for a record in the database. For example, you might fetch the next 10 records starting from the record with an ID of 20.

Laravel provides three different methods for paginating Eloquent queries in your applications:

• paginate – Uses offset-based pagination and fetches the total number of records in the dataset.
• simplePaginate – Uses offset-based pagination but doesn’t fetch the total number of records in the dataset.
• cursorPaginate – Uses cursor-based pagination and doesn’t fetch the total number of records in the dataset.

Let’s take a look at each of these methods in more detail.

Using the paginate Method

The paginate method allows you to fetch a subset of data from the database based on an offset and limit (we’ll take a look at these later when we look at the underlying SQL queries).

You can use the paginate method like so:

use App\Models\User;

$users = User::query()->paginate();

Running the above code would result in the $users being an instance of Illuminate\Contracts\Pagination\LengthAwarePaginator, typically an Illuminate\Pagination\LengthAwarePaginator object. This paginator instance contains all the information you need to display the paginated data in your application.

The paginate method can automatically determine the requested page number based on the page query parameter in the URL. For example, if you visited https://my-app.com/users?page=2, the paginate method would fetch the second page of data.

By default, all the pagination methods in Laravel default to fetching 15 records at a time. However, this can be changed to a different value (we’ll take a look at how to do this later).

Using paginate with Blade Views

Let’s take a look at how to use the paginate method when rendering data in a Blade view.

Imagine we have a simple route that fetches the users from the database in a paginated format and passes them to a view:

use App\Models\User;
use Illuminate\Support\Facades\Route;

Route::get('users', function () {
    $users = User::query()->paginate();

    return view('users.index', [
        'users' => $users,
    ]);
});

Our resources/views/users/index.blade.php file might look something like this:

<html>
<head>
    <title>Paginate</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>

<body>
    <div class="max-w-5xl mx-auto py-8">
        <h1 class="text-5xl">Paginate</h1>

        <ul class="py-4">
            @foreach ($users as $user)
                <li class="py-1 border-b"></li>
            @endforeach
        </ul>

        
    </div>
</body>
</html>

The resulting page would look something like this:

Let’s break down what’s happening in the Blade view:

• We’re looping through each user that is present in the $users field (the Illuminate\Pagination\LengthAwarePaginator object) and outputting their name.
• We’re calling the links method on the $users object. This is a really handy method which returns some HTML that displays the pagination links (e.g., "Previous", "Next", and the page numbers). This means you don’t have to worry about creating the pagination links yourself, and Laravel will handle all of that for you.

We can also see that the paginate method is giving us an overview of the pagination data. We can see that we’re viewing the 16th to 30th records, out of a total of 50 records. We can also see that we’re on the second page and that there are a total of 4 pages.

It’s important to note that the links method will return the HTML styled using Tailwind CSS. If you wish to use something other than Tailwind or you want to style the pagination links yourself, you can check out the documentation on customizing pagination views.

Using paginate in API Endpoints

As well as using the paginate method in Blade views, you can also use it in API endpoints. Laravel makes this process easy by automatically converting the paginated data into JSON.

For instance, we could build an /api/users endpoint (by adding the following route to our routes/api.php file) which returns the paginated users in JSON format:

use App\Models\User;
use Illuminate\Support\Facades\Route;

Route::get('paginate', function () {
    return User::query()->paginate();
});

Accessing the /api/users endpoint would return a JSON response similar to the following (please note I’ve limited the data field to just 3 records for the sake of brevity):

{
  "current_page": 1,
  "data": [
    {
      "id": 1,
      "name": "Andy Runolfsson",
      "email": "teresa.wiegand@example.net",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    },
    {
      "id": 2,
      "name": "Rafael Cummings",
      "email": "odessa54@example.org",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    },
    {
      "id": 3,
      "name": "Reynold Lindgren",
      "email": "juwan.johns@example.net",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    }
  ],
  "first_page_url": "http://example.com/users?page=1",
  "from": 1,
  "last_page": 4,
  "last_page_url": "http://example.com/users?page=4",
  "links": [
    {
      "url": null,
      "label": "« Previous",
      "active": false
    },
    {
      "url": "http://example.com/users?page=1",
      "label": "1",
      "active": true
    },
    {
      "url": "http://example.com/users?page=2",
      "label": "2",
      "active": false
    },
    {
      "url": "http://example.com/users?page=3",
      "label": "3",
      "active": false
    },
    {
      "url": "http://example.com/users?page=4",
      "label": "4",
      "active": false
    },
    {
      "url": "http://example.com/users?page=5",
      "label": "5",
      "active": false
    },
    {
      "url": "http://example.com/users?page=2",
      "label": "Next »",
      "active": false
    }
  ],
  "next_page_url": "http://example.com/users?page=2",
  "path": "http://example.com/users",
  "per_page": 15,
  "prev_page_url": null,
  "to": 15,
  "total": 50
}

Let’s break down the JSON response:

• current_page – The current page we’re on. In this case, we’re on the first page.
• data – The actual data itself that’s being returned. In this case, it contains the first 15 users (shortened to 3 for brevity).
• first_page_url – The URL to the first page of data.
• from – The starting record number of the data being returned. In this case, it’s the first record. If we were on the second page, this would be 16.
• last_page – The total number of pages of data. In this case, there are 4 pages.
• last_page_url – The URL to the last page of data.
• links – An array of links to the different pages of data. This includes the "Previous" and "Next" links, as well as the page numbers.
• next_page_url – The URL to the next page of data.
• path – The base URL of the endpoint.
• per_page – The number of records being returned per page. In this case, it’s 15.
• prev_page_url – The URL to the previous page of data. In this case, it’s null because we’re on the first page. If we were on the second page, this would be the URL to the first page.
• to – The ending record number of the data being returned. In this case, it’s the 15th record. If we were on the second page, this would be 30.
• total – The total number of records in the dataset. In this case, there are 50 records.

The Underlying SQL Queries

Using the paginate method in Laravel results in two SQL queries being run:

• The first query fetches the total number of records in the dataset. This is used to determine information such as the total number of pages and the total number of records.
• The second query fetches the subset of data based on the offset and limit values. For example, it might be fetching the users for us to process and return.

So if we wanted to fetch the first page of users (with 15 users per page), the following SQL queries would be run:

select count(*) as aggregate from `users`

and

select * from `users` limit 15 offset 0

In the second query, we can see that the limit value is set to 15. This is the number of records that are returned per page.

The offset value is calculated as follows:

Offset = Page size * (Page - 1)

So if we wanted to fetch the third page of users, the offset value would be calculated as:

Offset = 15 * (3 - 1)

Therefore, the offset value would be 30 and we would fetch the 31st to 45th records. The queries for the third page would look like so:

select count(*) as aggregate from `users`

and

select * from `users` limit 15 offset 30

Using the simplePaginate Method

The simplePaginate method is very similar to the paginate method but with one key difference. The simplePaginate method doesn’t fetch the total number of records in the dataset.

As we’ve just seen, when we use the paginate method, we also get information about the total number of records and pages available in the dataset. We can then use this information for displaying things like the total number of pages in the UI or API response.

But if you do not intend to display these details to the user (or developer consuming the API), then we can avoid an unneeded database query (that counts the total number of records) by using the simplePaginate method.

The simplePaginate method can be used in the same way as the paginate method:

use App\Models\User;

$users = User::query()->simplePaginate();

Running the above code would result in the $users being an instance of Illuminate\Contracts\Pagination\Paginator, typically an Illuminate\Pagination\Paginator object.

Unlike the Illuminate\Pagination\LengthAwarePaginator object returned by the paginate method, the Illuminate\Pagination\Paginator object doesn’t contain information about the total number of records in the dataset and has no idea how many pages or total records there are. It just knows about the current page of data and whether there are more records to fetch.

Using simplePaginate with Blade Views

Let’s take a look at how you can use the simplePaginate method with a Blade view. We’ll assume we have the same route as before, but this time we’re using the simplePaginate method:

use App\Models\User;
use Illuminate\Support\Facades\Route;

Route::get('users', function () {
    $users = User::query()->simplePaginate();

    return view('users.index', [
        'users' => $users,
    ]);
});

We’ll build our Blade view in the same way as before:

<html>
<head>
    <title>Simple Paginate</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>

<body>
    <div class="max-w-5xl mx-auto py-8">
        <h1 class="text-5xl">Simple Paginate</h1>

        <ul class="py-4">
            @foreach ($users as $user)
                <li class="py-1 border-b"></li>
            @endforeach
        </ul>

        
    </div>
</body>
</html>

The resulting page would look something like this:

As we can see in this example, the output of $users->links() is different to the output we saw when using the paginate method. Since the simplePaginate method doesn’t fetch the total number of records, it has no context of the total number of pages or records, only whether there’s a next page or not. Therefore, we only see the "Previous" and "Next" links in the pagination links.

Using simplePaginate in API Endpoints

You can also use the simplePaginate method in API endpoints. Laravel will automatically convert the paginated data into JSON for you.

Let’s build an /api/users endpoint that returns the paginated users in JSON format:

use App\Models\User;
use Illuminate\Support\Facades\Route;

Route::get('users', function () {
    return User::query()->simplePaginate();
});

When we hit this route, we’ll get a JSON response similar to the following (I’ve limited the data field to just 3 records for brevity):

{
  "current_page": 1,
  "data": [
    {
      "id": 1,
      "name": "Andy Runolfsson",
      "email": "teresa.wiegand@example.net",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    },
    {
      "id": 2,
      "name": "Rafael Cummings",
      "email": "odessa54@example.org",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    },
    {
      "id": 3,
      "name": "Reynold Lindgren",
      "email": "juwan.johns@example.net",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    }
  ],
  "first_page_url": "http://example.com/users?page=1",
  "from": 1,
  "next_page_url": "http://example.com/users?page=2",
  "path": "http://example.com/users",
  "per_page": 15,
  "prev_page_url": null,
  "to": 15
}

As we can see, the JSON response is very similar to the response we got when using the paginate method. The key difference is that we don’t have the last_page, last_page_url, links, or total fields in the response.

The Underlying SQL Queries

Let’s take a look at the underlying SQL queries that are run when using the simplePaginate method.

The simplePaginate method still relies on the limit and offset values to fetch the subset of data from the database. However, it doesn’t run the query to fetch the total number of records in the dataset.

The offset value is still calculated in the same way as before:

Offset = Page size * (Page - 1)

However, the limit value is calculated slightly differently than the paginate method. It’s calculated as:

Limit = Page size + 1

This is because the simplePaginate method needs to fetch one more record than the perPage value to determine if there are more records to fetch. For example, let’s say we’re fetching 15 records per page. The limit value would be 16. So if 16 records were to be returned, we’d know there is at least one more page of data available to fetch. If any less than 16 records were returned, we’d know that we’re on the last page of data.

So if we wanted to fetch the first page of users (with 15 users per page), the following SQL queries would be run:

select * from `users` limit 16 offset 0

The query for the second page would look like so:

select * from `users` limit 16 offset 15

Using the cursorPaginate Method

So far we’ve looked at the paginate and simplePaginate methods which both use offset-based pagination. We’re now going to take a look at the cursorPaginate method which uses cursor-based pagination.

As a heads-up, cursor-based pagination might seem a little confusing the first time you come across it. So don’t worry if you don’t quite get it straight away. Hopefully, by the end of this article, you’ll have a better understanding of how it works. I’ll also leave an awesome video at the end of this article that explains cursor-based pagination in more detail.

With offset-based pagination, we use the limit and offset values to fetch a subset of data from the database. So we can say "skip the first 10 records and fetch the next 10 records". This is simple to understand and easy to implement. Whereas with cursor pagination, we use a cursor (typically a unique identifier for a specific record in the database) as a starting point to fetch the previous/next set of records.

For example, let’s say we make a query to fetch the first 15 users. We’ll assume the ID of the 15th user is 20. When we want to fetch the next 15 users, we’ll use the ID of the 15th user (20) as the cursor. We’ll say "fetch the next 15 users with an ID greater than 20".

You may sometimes see cursors referred to as "tokens", "keys", "next", "previous", and so on. They’re essentially a reference to a specific record in the database. We’ll look at the structure of the cursors later in this section when we take a look at the underlying SQL queries.

Laravel allows us to easily use cursor-based pagination with the cursorPaginate method:

use App\Models\User;

$users = User::query()->cursorPaginate();

Running the above code would result in the $users field being an instance of Illuminate\Contracts\Pagination\CursorPaginator, typically an Illuminate\Pagination\CursorPaginator object. This paginator instance contains all the information you need to display the paginated data in your application.

Similar to the simplePaginate method, the cursorPaginate method doesn’t fetch the total number of records in the dataset. It only knows about the current page of data and whether there are more records to fetch, so we’re not immediately aware of the total number of pages or records.

Using cursorPaginate with Blade Views

Let’s take a look at how to use the cursorPaginate method when rendering data in a Blade view. Similar to our previous examples, we’ll assume we have a simple route that fetches the users from the database in a paginated format and passes them to a view:

use App\Models\User;
use Illuminate\Support\Facades\Route;

Route::get('users', function () {
    $users = User::query()->cursorPaginate();

    return view('users.index', [
        'users' => $users,
    ]);
});

The Blade view might look something like this:

<html>
<head>
    <title>Cursor Paginate</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>

<body>
    <div class="max-w-5xl mx-auto py-8">
        <h1 class="text-5xl">Cursor Paginate</h1>

        <ul class="py-4">
            @foreach ($users as $user)
                <li class="py-1 border-b"></li>
            @endforeach
        </ul>

        
    </div>
</body>
</html>

This would output a page similar to the following:

As we can see, since the cursorPaginate method doesn’t fetch the total number of records in the dataset, the output of $users->links() is similar to the output we saw when using the simplePaginate method. We only see the "Previous" and "Next" links in the pagination links.

Using cursorPaginate in API Endpoints

Laravel also allows you to use the cursorPaginate method in API endpoints and will automatically convert the paginated data into JSON for us.

Let’s build an /api/users endpoint that returns the paginated users in JSON format:

use App\Models\User;
use Illuminate\Support\Facades\Route;

Route::get('users', function () {
    return User::query()->cursorPaginate();
});

When we hit this route, we’ll get a JSON response similar to the following (I’ve limited the data field to just 3 records for brevity):

{
  "data": [
    {
      "id": 1,
      "name": "Andy Runolfsson",
      "email": "teresa.wiegand@example.net",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    },
    {
      "id": 2,
      "name": "Rafael Cummings",
      "email": "odessa54@example.org",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    },
    {
      "id": 3,
      "name": "Reynold Lindgren",
      "email": "juwan.johns@example.net",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    }
  ],
  "path": "http://example.com/users",
  "per_page": 15,
  "next_cursor": "eyJ1c2Vycy5pZCI6MTUsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0",
  "next_page_url": "http://example.com/users?cursor=eyJ1c2Vycy5pZCI6MTUsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0",
  "prev_cursor": null,
  "prev_page_url": null
}

As we can see, the JSON response is similar to the previous responses we’ve seen but with some small differences. Since we aren’t fetching the total number of records, we don’t have the last_page, last_page_url, links, or total fields in the response. You may also have noticed we don’t have the from and to fields either.

Instead, we have the next_cursor and prev_cursor fields which contain the cursor for the next and previous pages of data. Since we’re on the first page, the prev_cursor and prev_page_url fields are both null. However, the next_cursor and next_page_url fields are set.

The next_cursor field is a base-64 encoded string that contains the cursor for the next page of data. If we decode the next_cursor field, we’d get something like this (beautified for readability):

{
  "users.id": 15,
  "_pointsToNextItems": true
}

The cursor contains two separate pieces of information:

• users.id – The ID of the last record fetched in the dataset.
• _pointsToNextItems – A boolean value that tells us whether the cursor points to the next or previous set of items. If the value is true it means the cursor should be used to fetch the next set of records with an ID greater than the users.id value. If the value is false, it means the cursor should be used to fetch the previous set of records with an ID less than the users.id value.

Let’s take a look at what the second page of data might look like (again, shortened to 3 records for brevity):

{
  "data": [
    {
      "id": 16,
      "name": "Durward Nikolaus",
      "email": "xkuhic@example.com",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    },
    {
      "id": 17,
      "name": "Dr. Glenda Cruickshank IV",
      "email": "kristoffer.schiller@example.org",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    },
    {
      "id": 18,
      "name": "Prof. Dolores Predovic",
      "email": "frankie.schultz@example.net",
      "email_verified_at": "2024-10-15T23:19:28.000000Z",
      "created_at": "2024-10-15T23:19:29.000000Z",
      "updated_at": "2024-10-15T23:19:29.000000Z"
    }
  ],
  "path": "http://example.com/users",
  "per_page": 15,
  "next_cursor": "eyJ1c2Vycy5pZCI6MzAsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0",
  "next_page_url": "http://example.com/users?cursor=eyJ1c2Vycy5pZCI6MzAsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0",
  "prev_cursor": "eyJ1c2Vycy5pZCI6MTYsIl9wb2ludHNUb05leHRJdGVtcyI6ZmFsc2V9",
  "prev_page_url": "http://example.com/users?cursor=eyJ1c2Vycy5pZCI6MTYsIl9wb2ludHNUb05leHRJdGVtcyI6ZmFsc2V9"
}

We can see that the prev_cursor and prev_page_url fields are now set, and the next_cursor and next_page_url fields have been updated with the cursor for the next page of data.

The Underlying SQL Queries

To get a better understanding of how the cursor pagination works under the hood, let’s take a look at the underlying SQL queries that are run when using the cursorPaginate method.

On the first page of data (containing 15 records), the following SQL query would be run:

select * from `users` order by `users`.`id` asc limit 16

We can see that we’re fetching the first 16 records from the users table and ordering them by the id column in ascending order. Similar to the simplePaginate method, we’re fetching 16 rows because we want to determine if there are more records to fetch.

Let’s imagine we then navigate to the next page of items with the following cursor:

eyJ1c2Vycy5pZCI6MTUsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0

When this cursor is decoded, we get the following JSON object:

{
  "users.id": 15,
  "_pointsToNextItems": true
}

Laravel will then run the following SQL query to fetch the next set of records:

select * from `users` where (`users`.`id` > 15) order by `users`.`id` asc limit 16

As we can see, we’re fetching the next 16 records from the users table that have an id larger than 15 (since 15 was the last ID on the previous page).

Now let’s assume that the ID of the first user on page 2 is 16. When we navigate back to the first page of data from the second page, the following cursor would be used:

eyJ1c2Vycy5pZCI6MTYsIl9wb2ludHNUb05leHRJdGVtcyI6ZmFsc2V9

When this is decoded, we get the following JSON object:

{
  "users.id": 16,
  "_pointsToNextItems": false
}

When we’re moving to the next page of results, the last record fetched is used as the cursor. When we move back to the previous page of results, the first record fetched is used as the cursor. For this reason, we can see the users.id value is set to 16 in the cursor. We can also see that the _pointsToNextItems value is set to false because we’re moving back to the previous set of items.

As a result, the following SQL query would be run to fetch the previous set of records:

select * from `users` where (`users`.`id` < 16) order by `users`.`id` desc limit 16

As we can see, the where constraint is now checking for records with an id less than 16 (since 16 was the first ID on page 2) and the results are ordered in descending order.

Using API Resources with Pagination

So far, in our API examples, we’ve just returned the paginated data directly from the controller. However, in a real-world application, you’ll likely want to process the data before returning it to the user. This could be anything from adding or removing fields, converting data types, or even transforming the data into a different format altogether. For this reason, you’ll likely want to use API Resources since they provide a way for you to consistently transform your data before returning it.

Laravel allows you to use API resources alongside pagination. Let’s look at an example of how to do this.

Imagine we have created an App\Http\Resources\UserResource API resource class that transforms the user data before returning it. It might look something like this:

declare(strict_types=1);

namespace App\Http\Resources;

use Illuminate\Http\Request;
use Illuminate\Http\Resources\Json\JsonResource;

final class UserResource extends JsonResource
{
    public function toArray(Request $request): array
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'email' => $this->email,
        ];
    }
}

In the toArray method, we’re defining that whenever we process a user via this resource, we only want to return the id, name, and email fields.

Now let’s build a simple /api/users API endpoint in our routes/api.php file that returns the paginated users using the App\Http\Resources\UserResource:

use App\Models\User;
use App\Http\Resources\UserResource;
use Illuminate\Support\Facades\Route;

Route::get('users', function () {
    $users = User::query()->paginate();

    return UserResource::collection(resource: $users);
});

In the code above, we’re fetching a single page of users (let’s assume it’s the first page containing 15 users) from the database. We’re then passing the $users field (which will be an instance of Illuminate\Pagination\LengthAwarePaginator) to the UserResource::collection method. This method will transform the paginated data using the App\Http\Resources\UserResource before returning it to the user.

When we hit the /api/users endpoint, we’ll get a JSON response similar to the following (I’ve limited the data field to just 3 records for brevity):

{
  "data": [
    {
      "id": 1,
      "name": "Andy Runolfsson",
      "email": "teresa.wiegand@example.net"
    },
    {
      "id": 2,
      "name": "Rafael Cummings",
      "email": "odessa54@example.org"
    },
    {
      "id": 3,
      "name": "Reynold Lindgren",
      "email": "juwan.johns@example.net"
    }
  ],
  "links": {
    "first": "http://example.com/users?page=1",
    "last": "http://example.com/users?page=4",
    "prev": null,
    "next": "http://example.com/users?page=2"
  },
  "meta": {
    "current_page": 1,
    "from": 1,
    "last_page": 4,
    "links": [
      {
        "url": null,
        "label": "« Previous",
        "active": false
      },
      {
        "url": "http://example.com/users?page=1",
        "label": "1",
        "active": true
      },
      {
        "url": "http://example.com/users?page=2",
        "label": "2",
        "active": false
      },
      {
        "url": "http://example.com/users?page=3",
        "label": "3",
        "active": false
      },
      {
        "url": "http://example.com/users?page=4",
        "label": "4",
        "active": false
      },
      {
        "url": "http://example.com/users?page=2",
        "label": "Next »",
        "active": false
      }
    ],
    "path": "http://example.com/users",
    "per_page": 15,
    "to": 15,
    "total": 50
  }
}

As we can see in the JSON above, Laravel detects that we’re working with a paginated dataset and returns the paginated data in a similar format as before. However, this time the users in the data field only contain the id, name, and email fields which we specified in our API resource class. Other fields (current_page, from, last_page, links, path, per_page, to, and total) are still returned as they’re part of the paginated data, but they’ve been placed inside a meta field. There’s also a links field that contains the first, last, prev, and next links to the different pages of data.

Changing the Per Page Value

When building views with paginated data, you might want to allow the user to change the number of records displayed per page. This might be via a dropdown or number input field.

Laravel makes it easy to change the number of records displayed per page by passing a perPage parameter to the simplePaginate, paginate, and cursorPaginate methods. This parameter allows you to specify the number of records you want to display per page.

Let’s take a look at a simple example of how to read a per_page query parameter and use this to change the number of records fetched per page:

use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;

Route::get('users', function (Request $request) {
    $perPage = $request->integer('per_page', default: 10);

    return User::query()->paginate(perPage: $perPage);
});

In the example above, we’re grabbing the per_page query parameter’s value. If the value isn’t provided, we’ll default to 10. We then pass that value to the perPage parameter of the paginate method.

We could then access these different URLs:

• https://my-app.com/users – Display the first page of users with 10 records per page.
• https://my-app.com/users?per_page=5 – Display the first page of users with 5 records per page.
• https://my-app.com/users?per_page=5&page=2 – Display the second page of users with 5 records per page.
• And so on…

How to Decide Which Pagination Method to Use

Now that we’ve looked at the different types of pagination and how to use them in Laravel, we’ll discuss how to decide which of these approaches to use in your application.

Do You Need the Page Number or the Total Number of Records?

If you’re building a UI or API endpoint that requires the total number of records or pages to be displayed, then the paginate method is probably a sensible choice.

If you don’t require either of these, then the simplePaginate or cursorPaginate will be more efficient as they don’t perform unnecessary queries to count the total number of records.

Do You Need to Jump to a Specific Page?

If you need to be able to jump to a specific page of data, then offset-based pagination is more suitable. Since cursor pagination is stateful, it relies on the previous page to know where to go next. So it’s not as easy to jump to a specific page.

Whereas when using offset pagination, you can typically just pass the page number in the request (maybe as a query parameter) and jump to that page without having any context of the previous page.

How Large is the Dataset?

Due to the way that databases handle offset values, offset-based pagination becomes less efficient as the page number increases. This is because when you’re using an offset, the database still has to scan through all the records up to the offset value. They’re just discarded and not returned in the query results.

Here’s a great article that explains this in more detail: https://use-the-index-luke.com/no-offset.

So as the total amount of data in the database grows and the page number increases, offset-based pagination can become less efficient. In these cases, cursor-based pagination is more performant, especially if the cursor field is indexed, since the previous records aren’t read. For this reason, if you’re going to be using pagination against a large dataset, you might want to opt for cursor pagination over offset pagination.

Is the Dataset Likely to Change Often?

Offset-based pagination can suffer from issues if the underlying dataset changes between requests.

Let’s take a look at an example.

Let’s say we have the following 10 users in our database:

• User 1
• User 2
• User 3
• User 4
• User 5
• User 6
• User 7
• User 8
• User 9
• User 10

We make a request to fetch the first page (containing 5 users) and get the following users:

• User 1
• User 2
• User 3
• User 4
• User 5

When we navigate to page 2, we’d expect to get users 6 to 10. However, let’s imagine that before we load page 2 (while we’re still viewing page 1), User 1 is deleted from the database. Since the page size is 5, the query to fetch the next page would look like this:

select * from `users` limit 5 offset 5

This means we’re skipping the first 5 records and fetching the next 5.

This would result in page 2 containing the following users:

• User 7
• User 8
• User 9
• User 10

As we can see, User 6 is missing from the list. This is because User 6 is now the 5th record in the table, so they’re actually on the first page.

Cursor-based pagination doesn’t have this issue, because we’re not skipping records, we’re just fetching the next set of records based on a cursor. Let’s imagine we’d used cursor-based pagination in the example above. The cursor for page 2 would be the ID of User 5 (which we’ll assume is 5) since it was the last record on the first page. So our query for page 2 may look like this:

select * from `users` where (`users`.`id` > 5) order by `users`.`id` asc limit 6

Running the above query would return users 6 to 10 as expected.

This should hopefully highlight how offset-based pagination can become problematic when the underlying data is changed, added to, or removed while it’s being read. It becomes less predictable and can lead to unexpected results.

Are You Building an API?

It’s important to remember that you’re not fixed to using a single type of pagination in your application. In some places, offset pagination might be more suitable (maybe for UI purposes) and in others, cursor pagination might be more efficient (such as when working with a large dataset). So you can mix and match pagination methods in your application depending on the use case.

However, if you’re building an API, I’d highly recommend that you’re consistent and use a single pagination approach for all your endpoints. This will make it easier for developers to understand how to use your API and avoid any confusion.

You don’t want them to have to remember which endpoints use offset-pagination and which use cursor-pagination.

Of course, this isn’t a hard and fast rule. If you really need to use a different pagination method in one particular endpoint, then go ahead. But just make sure to make it clear in the documentation to make it easier for developers to understand.

Prefer a Video Instead?

If you’re more of a visual learner, you might want to check out this awesome video by Aaron Francis that explains the difference between offset and cursor-based pagination in more detail:

<iframe width="560" height="315" src="https://www.youtube.com/embed/zwDIN04lIpc" title="Pagination in MySQL – offset vs. cursor" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

Conclusion

In this article, we’ve taken a look at the different types of pagination in Laravel and how to use them. We’ve also looked at their underlying SQL queries and how to decide which pagination method to use in your application.

Hopefully, you should now feel more confident in using pagination in your Laravel applications.

The post A Guide to Pagination in Laravel appeared first on Laravel News.

Join the Laravel Newsletter to get all the latest
Laravel articles like this directly in your inbox.

Laravel News

Our bodies divest themselves of 60 billion cells every day through a natural process called "apoptosis". So Stanford medicine researchers are developing a new approach to cancer therapy that could "trick cancer cells into disposing of themselves," according to announcement from Stanford’s medical school:
Their method accomplishes this by artificially bringing together two proteins in such a way that the new compound switches on a set of cell death genes… One of these proteins, BCL6, when mutated, drives the blood cancer known as diffuse large cell B-cell lymphoma… [It] sits on DNA near apoptosis-promoting genes and keeps them switched off, helping the cancer cells retain their signature immortality. The researchers developed a molecule that tethers BCL6 to a protein known as CDK9, which acts as an enzyme that catalyzes gene activation, in this case, switching on the set of apoptosis genes that BCL6 normally keeps off. "The idea is, Can you turn a cancer dependency into a cancer-killing signal?" asked Nathanael Gray, PhD, co-senior author with Crabtree, the Krishnan-Shah Family Professor and a chemical and systems biology professor. "You take something that the cancer is addicted to for its survival and you flip the script and make that be the very thing that kills it…." When the team tested the molecule in diffuse large cell B-cell lymphoma cells in the lab, they found that it indeed killed the cancer cells with high potency. They also tested the molecule in healthy mice and found no obvious toxic side effects, even though the molecule killed off a specific category of of the animals’ healthy B cells, a kind of immune cell, which also depend on BCL6. They’re now testing the compound in mice with diffuse large B-cell lymphoma to gauge its ability to kill cancer in a living animal. Because the technique relies on the cells’ natural supply of BCL6 and CDK9 proteins, it seems to be very specific for the lymphoma cells — the BCL6 protein is found only in this kind of lymphoma cell and in one specific kind of B cell. The researchers tested the molecule in 859 different kinds of cancer cells in the lab; the chimeric compound killed only diffuse large cell B-cell lymphoma cells. Scientists have been trying to shut down cancer-driving proteins, one of the researchers says, but instead, "we’re trying to use them to turn signaling on that, we hope, will prove beneficial for treatment." The two researchers have co-founded the biotech startup Shenandoah Therapeutics, which "aims to further test this molecule and a similar, previously developed molecule," according to the article, "in hopes of gathering enough pre-clinical data to support launching clinical trials of the compounds. "They also plan to build similar molecules that could target other cancer-driving proteins…"

Read more of this story at Slashdot.

Slashdot

https://media.notthebee.com/articles/6723bb0ebb9af6723bb0ebb9b0.jpg

Happy Halloween, y’all. JD Vance just did 3+ hours with Rogan and we’ve got the whole thing for you here.

Not the Bee

https://blog.jetbrains.com/wp-content/uploads/2024/10/compact.png

Maybe you’ve heard complicated-sounding phrases such as ‘“Students t-test”, “regression models”, “support vector machines”, and so on. You might think there’s so much you need to learn before you can explore and understand your data, but I am going to show you two tools to help you go faster. These are summary statistics and graphs.

Summary statistics and graphs/plots are used by new and experienced data scientists alike, making them the perfect building blocks for exploring data.

We will be working with this dataset available from Kaggle if you’d like to follow along. I chose this dataset because it has several interesting properties, such as multiple continuous and categorical variables, missing data, and a variety of distributions and skews. I’ll explain each variable I work with and why I chose each one to show you the tools you can apply to your chosen data set.

In our previous blog posts, we looked at where to get data from and bring that data into PyCharm. You can look at steps 1 and 2 from our blog post entitled 7 ways to use Jupyter notebooks in PyCharm to create a new Jupyter notebook and import your data as a CSV file if you need a reminder. You can use the dataset I linked above or pick your own for this walkthrough.

We’re going to be using the pandas library in this blog post, so to ensure we’re all on the same page, your code should look something like the following block in a Jupyter notebook –  you’ll need to change the spreadsheet name and location to yours, though. Make sure you’ve imported matplotlib, too, as we will be using that library to explore our data.

import pandas as pd
import matplotlib as plt


df = pd.read_csv('../data/AmesHousing.csv')
df

When you run that cell, PyCharm will show you your DataFrame, and we can get started.

Try PyCharm Professional for free

Summary statistics

When we looked at where to get data from, we discussed continuous and categorical variables. We can use Jupyter notebooks inside PyCharm to generate different summary statistics for these, and, as you might have already guessed, the summary statistics differ depending on whether the variables are continuous or categorical.

Continuous variables summary statistics

First, let’s see how we can view our summary statistics. Click on the small bar graph icon on the right-hand side of your DataFrame and select Compact:

Let me give you a little tip here if you’re unsure which variables are continuous and which are categorical, PyCharm shows different summary statistics for each one. The ones with the mini graphs (blue in this screenshot) are continuous, and those without are categorical.

This data set has several continuous variables, such as Order, PID, MS SubClass, and more, but we will focus on Lot Frontage first. That is the amount of space at the front of the property.

The summary statistics already give us some clues:

There’s a lot of data here, so let’s break it down and explore it to understand it better. Immediately, we can see that we have missing data for this variable; that’s something we want to note, as it might mean we have some issues with the dataset, although we won’t go into that in this blog post!

First, you can see the little histogram in blue in my screenshot, which tells us that we have a positive skew in our data because the data tails off to the right. We can further confirm this with the data because the mean is slightly larger than the median. That’s not entirely surprising, given we’d expect the majority of lot frontages to be of a similar size, but perhaps there are a small number of luxury properties with much bigger lot frontages that are skewing our data. Given this skew, we would be well advised not to use the standard deviation as a measure of dispersion because that is calculated by using all data points, so it’s affected by outliers, which we know we have on one side of our distribution.

Next, we can calculate our interquartile range as the difference between our 25th percentile of 58.0 and our 75th percentile of 80.0, giving us an interquartile range of 22.0. Alongside the interquartile range, it’s helpful to consider the median, the middle value in our data, and unlike the mean, it is not based on every data point. The median is more appropriate for Lot Frontage than the mean because it’s not affected by the outliers we know we have.

Since we’re talking about the median and interquartile range, it is worth saying that box plots are a great way to represent these values visually. We can ask JetBrains AI Assistant to create one for us with a prompt such as this:

Create code using matplotlib for a box plot for  ‘Lot Frontage’. Assume we have all necessary imports and the data exists.

Here’s the code that was generated:

plt.figure(figsize=(10, 6))
plt.boxplot(df['Lot Frontage'].dropna(), vert=False)
plt.title('Box Plot of Lot Frontage')
plt.xlabel('Lot Frontage')
plt.show()

When I click Accept and run, we get our box plot:

The median is the line inside the box, which, as you can see, is slightly to the left, confirming the presence of the positive or right-hand skew. The box plot also makes it very easy to see a noticeable number of outliers to the right of the box, known as “the tail”. That’s the small number of likely luxury properties that we suspect we have.

It’s important to note that coupling the mean and standard deviation or the median and IQR gives you two pieces of information for that data: a central tendency and the variance. For determining the central tendency, the mean is more prone to being affected by outliers, so it is best when there is no skew in your data, whereas the median is more robust in that regard. Likewise, for the variation, the standard deviation can be affected by outliers in your data. In contrast, the interquartile range will always tell you the distribution of the middle 50% of your data. Your goals determine which measurements you want to use. 

Categorical variables summary statistics

When it comes to categorical variables in your data, you can use the summary statistics in PyCharm to find patterns. At this point, we need to be clear that we’re talking about descriptive rather than inferential statistics. That means we can see patterns, but we don’t know if they are significant.

Some examples of categorical data in this data set include MS Zoning, Lot Shape, and House Style. You can gain lots of insights just by looking through your data set. For example, looking at the categorical variable Neighborhood, the majority are stated as Other in the summary statistics with 75.8%. This tells you that there might well be a lot of categories in Neighborhood, which is something to bear in mind when we move on to graphs. 

As another example, the categorical variable House Style states that about 50% of the houses are one-story, while 30% are two-story, leaving 20% that fall into some other category that you might want to explore in more detail. You can ask JetBrains AI for help here with a prompt like:

Write pandas code that tells me all the categories for ‘House Style’ in my DataFrame ‘df’, which already exists. Assume we have all the necessary imports and that the data exists.

Here’s the resulting code:

unique_house_styles = df['House Style'].unique()


print("Unique categories for 'House Style':")
print(unique_house_styles)

When we run that we can see that the remaining 20% is split between various codes that we might want to research more to understand what they mean:

Unique categories for ‘House Style’:

['1Story' '2Story' '1.5Fin' 'SFoyer' 'SLvl' '2.5Unf' '1.5Unf' '2.5Fin']

Have a look through the data set at your categorical variables and see what insights you can gain!

Before we move on to graphs, I want to touch on one more piece of functionality inside PyCharm that you can use to access your summary statistics called Explain DataFrame. You can access it by clicking on the purple AI icon on the top-right of the DataFrame and then choosing AI Actions | Explain DataFrame.

JetBrains AI lists out your summary statistics but may also add some code snippets that are helpful for you to get your data journey started, such as how to drop missing values, filter rows based on a condition, select specific columns, as well as group and aggregate data. 

Graphs

Graphs or plots are a way of quickly getting patterns to pop out at you that might not be obvious when you’re looking at the numbers in the summary statistics. We’re going to look at some of the plots you can get PyCharm to generate to help you explore your data.

First, let’s revisit our continuous variable, Lot Frontage. We already learned that we have a positive or right-hand skew from the mini histogram in the summary statistics, but we want to know more! 

In your DataFrame in PyCharm, click the Chart View icon on the left-hand side:

Now click the cog on the right-hand side of the chart that says Show series settings and select the Histogram plot icon on the far right-hand side. Click x to clear the values in the X axis and Y axis and then select Lot Frontage with group and sort for the X axis and Lot Frontage with count for the Y axis:

PyCharm generates the same histogram as you see in the summary settings, but we didn’t have to write a single line of code. We can also explore the histogram and mouse over data points to learn more. 

Let’s take it to the next level while we’re here. Perhaps we want to see if the condition of the property, as captured by the Overall Cond variable, predicts the sale price.

Change your X axis SalePrice group and sort and your Y axis to SalePrice count and then add the group Overall Cond:

Looking at this chart, we can hypothesize that the overall condition of the property is indeed a predictor of the sale price, as the distribution and skew are remarkably similar. One small note is that grouping histograms like this works best when you have a smaller number of categories. If you change Groups to Neighborhood, which we know has many more categories, it becomes much harder to view! 

Moving on, let’s stick with PyCharm’s plotting capabilities and explore bar graphs. These are a companion to frequency charts such as histograms, but can also be used for categorical data. Perhaps you are interested in Neighbourhood (a categorical variable) in relation to SalesPrice.

Click the Bar [chart] icon on the left-hand side of your series setting, then select Neighbourhood as Categories and SalesPrice with the median as the Values:

This helps us understand the neighborhoods with the most expensive and cheapest housing. I chose the median for the SalesPrice as it’s less susceptible to outliers in the data. For example, I can see that housing in Mitchel is likely to be substantially cheaper than in NoRidge. 

Line plots are another useful plot for your toolkit. You can use these to demonstrate trends between continuous variables over a period of time. For example, select the Line [graph] icon and then choose Year Built as the X axis and SalePrice with the mean as the Y axis:

This suggests a small positive correlation between the year the house was built and the price of the house, especially after 1950. If you’re feeling adventurous, remove the mean from SalePrice and see how your graph changes when it has to plot every single price! 

The last plot I’d like to draw your attention to is scatter plots. These are a great way to see a relationship between two continuous variables and any correlation between them. A correlation shows the strength of a relationship between two variables. To dig deeper, check out this beginner-friendly overview from Real Python.

For example, if we set our X axis to SalePrice and our Y axis to Gr LivArea, we can see that there is a positive correlation between the two variables, and we can also easily spot some outliers in our data, including a couple of houses with a lower sale price but a huge living area!

Summary

Here’s a reminder of what we’ve covered today. You can access your summary statistics in PyCharm either through Explain DataFrame with JetBrains AI or by clicking on the small graph icon on the right-hand side of a DataFrame called Column statistics and then selecting Compact. You can also use Detailed to get even more information than we’ve covered in this blog post. 

You can get PyCharm to create graphs to explore your data and create hypotheses for further investigation. Some more commonly used ones are histograms, bar charts, line graphs, and scatter plots.

Finally, you can use JetBrains AI Assistant to generate code with natural language prompts in the AI tool window. This is a quick way to learn more about your data and start thinking about the insights on offer.

Download PyCharm Professional to try it out for yourself! Get an extended trial today and experience the difference PyCharm Professional can make in your data science endeavors. Use the promotion code “PyCharmNotebooks” at checkout to activate your free 60-day subscription to PyCharm Professional. The free subscription is available for individual users only.

Try PyCharm Professional for free

Using both summary statistics and graphs in PyCharm, we can learn a lot about our data, giving us a solid foundation for our next step – cleaning our data, which we will talk about in the next blog post in this series.

Planet Python

https://media.notthebee.com/articles/671c5657a9901671c5657a9902.jpg

A little treat for ya this evening.

Not the Bee

https://theawesomer.com/photos/2024/10/starscream_ova_transformers_fan_film_t.jpg

“Your task is simply to… survive!” The two-faced Decepticon Starscream is assigned to a solo mission. Thinking that it’s another attempt by Megatron to foil the Autobots, he quickly reverts to his high and mighty rhetoric. But will he be brought back down to earth and eat his words? 87render and their friends got together to create this great homage to Transformers.

The Awesomer

https://www.dailycomputerscience.com/storage/1727805007._logo_.png

<?php

function runPythonScript(string $scriptPath): string {
    $output = shell_exec("python3 " . escapeshellarg($scriptPath));
    return $output ?: 'Error running Python script.';
}

echo runPythonScript('/path/to/your_script.py');

# app.py
from flask import Flask, jsonify

app = Flask(__name__)

@app.route('/process-data', methods=['GET'])
def process_data():
    result = {'message': 'Data processed successfully', 'status': 'OK'}
    return jsonify(result)

if __name__ == '__main__':
    app.run(port=5000)

<?php

function callPythonApi(string $url): array {
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    $response = curl_exec($ch);
    curl_close($ch);

    return json_decode($response, true) ?? [];
}

$apiResponse = callPythonApi('http://localhost:5000/process-data');

print_r($apiResponse);

Laravel News Links

https://blog.mclaughlinsoftware.com/wp-content/uploads/2024/10/LighteningBolt.png

It’s very difficult explaining to students new to relational databases how SQL works. There are many parts that seem intuitive and then there are others that confuse and confound.

For beginners, the idea that a SQL statement is simply a text string that you must dispatch to a SQL statement processing engine is new. That’s because they use an Integrated Development Environment (IDE) that hides, or abstracts the complexity, of how SQL executes.

I start my core SQL class by demonstrating how to run a text literal query without a FROM clause in MySQL Workbench, like this:

SELECT 'Hello World!' AS "Output";

After writing the query, I highlight everything except the semicolon and click the lightening bolt that dispatches the static string to the SQL statement engine. They see this result:

Then, I launch a mysql Monitor session and write the query with a semicolon to dispatch the SQL static string to the SQL statement engine:

SELECT 'Hello World!' AS "Output";

and, with a \g to dispatch the SQL static string to the SQL statement engine:

SELECT 'Hello World!' AS "Output"\g

Both queries return the same output, as shown below:

+--------------+
| output       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)

Rewriting the query with a \G to dispatch the SQL static string to the SQL statement engine:

SELECT 'Hello World!' AS "Output"\G

Both queries return the following output:

*************************** 1. row ***************************
output: Hello World!
1 row in set (0.00 sec)

The next step requires removing the MySQL Workbench and MySQL Monitor from the demonstration. Without either of those tools, a Python program can demonstrate how to run a static SQL string.

The query is now a string literal into a query.sql file. The Python program reads the query.sql file, dispatches the embedded query, and displays the query results.

This is the query.sql file is:

SELECT 'Hello World!' AS "output";

This is the query.py file is:

#!/usr/bin/python

# Import libraries.
import sys
import mysql.connector
from mysql.connector import errorcode

# ============================================================
#  Use a try-catch block to read and parse a query from a
#  a file found in the same local directory as the Python
#  program.
# ============================================================
try:
  file = open('query.sql','r')
  query = file.read().replace('\n',' ').replace(';','')
  file.close()

except IOError:
  print("Could not read file:", fileName)
 
# ============================================================
#  Attempt connection in a try-catch block.
# ============================================================
# --------------------------------------------------------
#  Open connection, bind variable in query and format
#  query output before closing the cursor.
# --------------------------------------------------------
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')

  # Create cursor.
  cursor = cnx.cursor()

  # Execute cursor, and coerce string to tuple.
  cursor.execute(query)

  # Display the rows returned by the query.
  for row in cursor:
    print(row[0])

  # Close cursor.
  cursor.close()

# --------------------------------------------------------
#  Handle MySQL exception 
# --------------------------------------------------------
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message

# --------------------------------------------------------
#  Close connection after try-catch completes.
# --------------------------------------------------------
# Close the connection when the try block completes.
else:
  cnx.close()

In Linux or Unix from the relative directory where both the query.sql and query.py files are located:

./query.py

It returns:

Hello World!

These examples demonstrate that a query without variable substitution is only a static string. In all the cases, the static SQL strings are dispatched to the SQL engine by a terminator like a semicolon or through an ODBC library call that executes the static SQL string.

Planet MySQL

https://media.notthebee.com/articles/67129488a494967129488a494b.jpg

We’ve got FEMA paying billons to house illegal aliens and we’ve got our Customs and Border Patrol agents in Florida cleaning up hurricane damage.

Not the Bee

https://cdn0.thetruthaboutguns.com/wp-content/uploads/2024/09/AR15-for-Home-Defense.png

Imagine waking up in the middle of the night to the sound of a break-in—your heart racing, adrenaline pumping and precious seconds ticking away. 

In such a high-stress situation, you need a home defense tool that offers more than just peace of mind; you need one that can provide a quick and decisive response. The AR-15 delivers that edge, combining speed, precision and stopping power when you need it most. 

So, let’s discuss why the AR-15 could be your ultimate home defense solution and how to set it up effectively.

The AR-15’s Key Features for Home Defense

The AR-15 has earned a reputation as one of the most versatile and effective firearms available today. Here are the key features that make it a top choice for home defense:

Customizability and Modularity

One of the standout characteristics of the AR-15 is its unparalleled customizability. Whether you’re looking to add a flashlight, a red dot sight, or a foregrip, the AR-15’s modular design allows for easy attachment of accessories that enhance your defensive capabilities. This flexibility means you can tailor the rifle to fit your needs and preferences, creating a perfectly suited setup for home defense.

Accuracy and Low Recoil

The AR-15’s design is optimized for accuracy, even under high-stress situations. Its low recoil, especially when chambered in 5.56 NATO or .223 Remington, makes it easy to shoot accurately. This is particularly important in home defense scenarios, where precise shot placement can be critical to neutralizing a threat without endangering others in the home.

Capacity and Reload Speed

With standard magazines holding 30 rounds, the AR-15 provides ample capacity for defensive situations. This reduces the need for frequent reloading, which can be a critical advantage when facing multiple intruders or when you must focus on the threat. Additionally, reloading an AR-15 is relatively quick and easy, with minimal training to master the process.

Ease of Handling

The AR-15’s ergonomic design makes it easy to handle, even in high-pressure situations. Its lightweight frame and adjustable stock make it adaptable to shooters of all sizes and strengths, ensuring that any responsible adult in your household can effectively use it in a defensive scenario.

Intimidation Factor

While not a primary reason to choose a firearm, the intimidation factor of an AR-15 cannot be overlooked. The mere presence of a rifle can often be enough to deter potential intruders, possibly preventing the need to fire a single shot. This psychological deterrent adds another layer of defense to your home security strategy.

Comparing the AR-15 to Other Home Defense Firearms

Several firearm options are available for home defense, each with its own pros and cons. Let’s compare the AR-15 with the most commonly recommended alternatives: shotguns and handguns.

Shotguns

Shotguns are often hailed as the quintessential home defense firearm, but they have advantages and disadvantages compared to the AR-15.

Pros

• Stopping Power: A 12-gauge shotgun can deliver devastating stopping power at close range, making it highly effective against intruders.
• Spread Pattern: The spread pattern of buckshot can increase hit probability, especially in high-stress situations.

Cons

• Recoil: Shotguns, especially 12-gauge models, have significant recoil, which can be difficult to manage for smaller or less experienced shooters.
• Capacity and Reloading: Most shotguns have a limited capacity, typically between 4 and 8 rounds, and reloading them is considerably slower and more cumbersome than reloading the AR-15.
• Size and Maneuverability: Most shotguns are longer than the standard AR-15, making them harder to maneuver in tight spaces like hallways or small rooms.

Handguns

Handguns are a popular choice for home defense due to their compact size and ease of use, but they also have limitations.

Pros

• Maneuverability: Handguns allow you to move faster if you need to move to secure family members.
• Ease of Storage and Accessibility: Handguns are compact and easy to stage and deploy around your home for quick access.

Cons

• Accuracy: Handguns require more skill to shoot accurately, especially under duress. They require more training and practice to control the recoil at speed and under stress.
• Stopping Power and Capacity: Handguns can be effective, but they generally offer lower stopping power and capacity than the AR-15. This can be a disadvantage if multiple shots are required to stop a threat.

AR-15: The Middle Ground

The AR-15 serves as a balanced option between the power and spread of a shotgun and the maneuverability and capacity of a handgun.

Pros

• Higher Capacity: With standard 30-round magazines, the AR-15 provides more than enough ammunition for most home defense scenarios.
• Controlled Recoil: The low recoil allows for quick follow-up shots, which is crucial in a defensive situation.
• Accuracy and Adaptability: The rifle’s inherent accuracy and the ability to attach optics and other accessories enhance its defensive capabilities.

Cons

• Overpenetration: One concern with using an AR-15 for home defense is the potential for overpenetration, where a bullet could pass through walls and pose a risk to others. This can be mitigated with proper ammunition selection, such as frangible or hollow-point rounds designed for reduced penetration.

While shotguns and handguns have their merits, the AR-15 is a versatile and effective choice that balances both strengths. Its customizability, capacity, and ease of use make it a formidable tool for protecting your home.

Addressing Common Concerns and Misconceptions

Despite its popularity, the AR-15 as a home defense firearm often draws skepticism and concern. In this section, we’ll tackle some of the most common misconceptions and clarify why the AR-15 can be an effective choice for home defense.

Myth: “The AR-15 is Too Powerful for Home Defense”

Many people believe that the AR-15, due to its rifle caliber, is too powerful for home defense scenarios, posing a risk of overpenetration that could harm family members or neighbors. While it’s true that certain types of ammunition can penetrate multiple walls, this concern can be mitigated by selecting the right ammunition. Frangible or hollow-point rounds are designed to minimize overpenetration, breaking apart upon impact with hard surfaces while providing effective stopping power against threats.

Myth: “The AR-15 is Hard to Control”

Some assume that the AR-15 is difficult to handle, especially for smaller or less experienced shooters. However, the opposite is true. The AR-15’s low recoil, ergonomic design, and lightweight frame make it easier to control and shoot accurately than many handguns and shotguns. The adjustable stock also allows the rifle to be fitted to a wide range of shooters, ensuring comfort and control regardless of stature or strength.

Myth: “It’s a Military-Grade Weapon Unsuitable for Civilian Use”

This misconception stems from the AR-15’s resemblance to the military’s M16 and M4 rifles. However, the civilian AR-15 is semi-automatic, meaning it fires one round per trigger pull, unlike the fully automatic capabilities of military rifles. Its features make it no different from many other semi-automatic rifles used for hunting, sport shooting, and, yes, home defense.

Myth: “It’s Overkill for Home Defense”

Critics often argue that the AR-15’s firepower is excessive for home defense. Home invasions can be unpredictable, and it’s not uncommon for intruders to be armed. The AR-15’s high capacity and ability to deliver precise shots rapidly can be a critical advantage in such situations. The goal is to neutralize threats efficiently, and the AR-15 provides the capability to do so.

Optimizing Your AR-15 for Home Defense

Optimizing your AR-15 for home defense involves equipping it with key accessories. A red dot sight enhances target acquisition, while backup iron sights ensure reliability if the optic fails. A weapon-mounted light is essential for identifying threats in low-light conditions, and an adjustable two-point sling offers stability and hands-free operation. Choosing the proper ammunition, like frangible or hollow-point rounds, minimizes the risk of overpenetration. Reliable 30-round magazines are recommended, with additional spares accessible for quick reloading in critical situations.

Now, let’s get into my go-to AR-15 setup for home defense.

My Go-To AR-15 Setup For Home Defense

My go-to defensive AR-15 is the Maxim Defense MD-15L. It’s paired with the EOTech 512 holographic sight. This rifle offers a solid foundation with its lightweight build, quality materials and optimal barrel length for maneuverability. The EOTech 512 provides quick target acquisition and a parallax-free reticle, essential for high-stress situations. I’ve added a Streamlight PolyTac weapon light for low-light conditions and a Frank Proctor Sling for retention. Combined with 77-grain BTHP ammunition, this setup is practical and effective for home defense.

Optic: EOTech 512

The EOTech 512 holographic sight offers rapid target acquisition with its wide field of view and parallax-free reticle. Its robust design is ideal for home defense, allowing for accurate shots in high-stress situations. Powered by standard AA batteries, it ensures reliability and ease of use.

Sling: Frank Proctor Sling

This lightweight, minimalist sling provides versatility and quick adjustment. It allows for easy transitions between shooting positions and enhances weapon retention, making it suitable for dynamic home defense scenarios.

Weapon Light: Streamlight PolyTac

The Streamlight PolyTac offers a powerful beam with a durable polymer body. It provides essential illumination for target identification and threat assessment in low-light conditions, a critical feature for any home defense setup.

Ammunition: 77-Grain BTHP

Using 77-grain boat-tail hollow-point (BTHP) ammunition balances stopping power and reduces overpenetration. This bullet design maximizes terminal ballistics, making it effective for home defense by ensuring energy transfer upon impact.

This combination creates a balanced and reliable setup that prioritizes maneuverability, accuracy, and adaptability in various home defense scenarios.

Conclusion

A home invasion is a fast and violent event that can unfold in seconds, leaving little time to react. In these critical moments, the AR-15 gives you the edge needed to respond fast and with extreme violence. 

Its accuracy, capacity, and adaptability allow you to take control of the situation and protect your loved ones. Don’t wait until it’s too late—take the time NOW to build your ideal home defense setup. Equip yourself, train diligently, and be prepared to respond with the skill and confidence necessary to survive another day.

Where To Buy

The Truth About Guns