開発スタッフがアプリ関連の最新技術やニュースをご紹介します。

Here is some tips based on my experience to speed up database queries.

Table Indexing

Indexes are important part in database. Indexes are used to quickly retrieve data without having to search every row in a database table is accessed. The users cannot see the indexes, they are just used to speed up searches/queries.

How to use? For example, you have thousands of rows of users and you want to get an email.

Select * from users where email = “me@gmail.com”

Without index, this process will be from top to bottom until it the email is found.

Using with index, in Laravel, the easy way is to create a migration file.

public function up()
{
   Schema::table(‘users’, function(Blueprint $table)
   {
     $table->index(‘email’);
   });
}

Or if you currently on fresh project just add index to column you want to

public function up()
{
    Schema::create(‘users, function(Blueprint $table) {
      …
      $table->index(‘email’);
      …
    });
}

Index is not only for single column. You can use multiple column like this  $table->index([‘email’, ‘ phone_no’]); Let’s us look at a query that could use two different indexes on the table based on the WHERE clause restrictions. Multi column indexes are called compound indexes.

Eloquent Eager Loading

At its core Eager Loading, is telling Eloquent that you want to grab a model with specific relationships that way the framework produces a more performant query to grab all the data you will need. By eager loading, you can take many queries down to just one or two. Wrong implementation cause N+1. Here is where common developer do which cause N+1.

For example:

Class Post extends Model
{
  public function author()
  {
    return $this->belongsTo(Author::class);
  }
}

Lets fetch author about 100 rows from the table and what happen

Mistake 1

$post = Post::all();
foreach($posts as $post) 
{
  $total_user = $post->author;
}

The query runs once to get all the author and for each loop it query another query for author for 100 times. 100 + 1 = N+1 problem. Imagine if you want to fetch thousand to data. If one query cost 100ms * 100 = 10,000ms it takes to complete process.

Solution

It can be solve by using with method in Eager loading. For example

$post = App\Post::with(‘author’)->get();

If you had multiple related associations, you can eager load them with an array:

$post = App\Post::with([‘author’, ‘comments’])->get();
$post =App\Post::with(‘author.profile’)->get();

With this, instead of hitting database N+1, with will single query by joining those table and transform it into collection. So you can freely access the collection object without hitting the database.

By Ami

Tweet

When we are developing web systems using PHP, we might encounter to use magic methods in PHP a lot. Today I would like to talk about some important magic methods provided by PHP.

Actually there are lots of magic methods in PHP. For example,

• __construct()
• __destruct()
• __call($fun, $arg)
• __callStatic($fun, $arg)
• __get($property)
• __set($property, $value)
• __isset($content)
• __unset($content)
• __sleep()
• __wakeup()
• __toString()
• etc..

But we don’t use all of the things unless it’s necessary. I will only write about a few methods which are mostly used in my experience.

Construct

If you define this method in your class, it will be called automatically when an object is instantiated. The purpose of this method is to assign some default values to the properties of the object. This method is also called a constructor.

<?php
class Student {
    private $name;
 
    public function __construct($name) 
    {
        $this->name = $name;
    }
}
 
$student = new Student('yuuma');
?>

Destruct

The __destruct () method is called destructor and is called when the object is destroyed. Generally also called when script stops or exits. The purpose of this method is to provide an opportunity to save the state of the object or any other cleaning you want to do.

<?php
class Student {
    private $name;
 
    public function __construct($name) 
    {
        $this->name = $name;
    }
 
    public function __destruct() 
    {
        echo 'This will be called when the class has been closed';
        // file close, removing session etc.
    }
}
 
$student = new Student('yuuma');
?>

Set

The magic __set () method is called when you try to set data on properties of inaccessible or non-existent objects. The purpose of this method is to set additional object data for which you have not explicitly defined object properties.

<?php
class Student {
    protected $data = array();
 
    public function __set($name, $value) 
    {
        $this->data[$name] = $value;
    }
}
 
$student = new Student();
//  __set() called
$student->name = 'yuuma';

As you can see from the example above, we are trying to set the property of the name, which does not exist. And so the __set () method is called. The first argument to the __set () method is the name of the property that is being accessed, and the second argument is the value we are trying to set.

Get

In the case of the __set () method example in the previous section, we discussed how to set values for non-existent properties. The __get () method is the exact opposite. The magic __get () method is called when you try to read data from properties of inaccessible or non-existent objects. The purpose of this method is to provide values for these properties.

<?php
class Student {
    private $data = array();
 
    public function __set($name, $value) 
    {
        $this->data[$name] = $value;
    }
 
    public function __get($name) 
    {
        If (isset($this->data[$name])) {
            return $this->data[$name];
        }
    }
}
 
$student = new Student();
 
//  __set() called
$student->name = 'yuuma';   
 
//  __get() called
echo $student->name;                      
?>

I would like to write more but I think the article is a little bit long for now. So I will write a part 2 in next week relating with this week article. So see you next week.

By Yuuma

Tweet

I am using a laravel library called `simple-qr-code` in my previous project. It is pretty easy to use and configurable and I would like to share with you today.

Firstly download the package via composer

composer require simplesoftwareio/simple-qrcode "~4"

That is all for the configuration. And you can now generate in controller like this.

use SimpleSoftwareIO\QrCode\Facades\QrCode;

QrCode::generate('Make me into a QrCode!');

This will make a QrCode that says “Make me into a QrCode!”

If you want to generate directly on the view, it is fine too.

{!! QrCode::size(300)->generate('generate Qr code'); !!}

If we want to generate a URL after reading the Qr code, we just have to pass a url string in generate function like this.

{!! QrCode::size(300)->generate('www.google.com'); !!}

You can vary the Qr code designs as well. You can see more detail here. Make sure to use when you have to deal with qr code as it is pretty easy to use.

By Yuuma

Tweet

Today, I would like to highlight a laravel package that will hep you to speed up your laravel website.

It is very important for every website to quick load that means your website should load in few seconds like 4 or 5. We are always fetching issues about page speed like how to increase website speed in laravel, how to reduce loading time of website in laravel, is it possible speed up php execution time of my website, how to optimize php laravel script to increase speed, how to speed up web browsing in laravel. So basically you have several question regarding to improve your website performance.

I found “laravel-page-speed” composer package for 35%+ optimization of laravel website. laravel-page-speed package will minify HTML output and it can be optimization your web page. laravel-page-speed package will delete unused attributes in HTML tags, delete unused quotes in HTML tags, delete unused prefixes from URLs, delete unused whitespace in HTML, delete HTML comments. Also there are several feature. They also minify css and minify js files.

Just grab the package via composer.

 composer require renatomarinho/laravel-page-speed

Publish the configuration file

php artisan vendor:publish --provider="RenatoMarinho\LaravelPageSpeed\ServiceProvider"

And then register it in Kernel file

//app/Http/Kernel.php

protected $middleware = [
    ...
    \RenatoMarinho\LaravelPageSpeed\Middleware\InlineCss::class,
    \RenatoMarinho\LaravelPageSpeed\Middleware\ElideAttributes::class,
    \RenatoMarinho\LaravelPageSpeed\Middleware\InsertDNSPrefetch::class,
    \RenatoMarinho\LaravelPageSpeed\Middleware\RemoveComments::class,
    //\RenatoMarinho\LaravelPageSpeed\Middleware\TrimUrls::class, 
    //\RenatoMarinho\LaravelPageSpeed\Middleware\RemoveQuotes::class,
    \RenatoMarinho\LaravelPageSpeed\Middleware\CollapseWhitespace::class, // Note: This middleware invokes "RemoveComments::class" before it runs.
    \RenatoMarinho\LaravelPageSpeed\Middleware\DeferJavascript::class,
]

And you are good to go.

There are several useful filtering methods that will help you to speed up your website like `RemoveComments, CollapseWhitespace, RemoveQuotes` etc.

You can see more detail in their documentation.

By Yuuma

Tweet

I would like to talk about the date filtering process I am using in laravel today.

If we want to select records that is created today. We can normally use raw queries or without raw queries like this.

//Raw Query
Model::where(DB::raw("DATE(created_at) = '".date('Y-m-d')."'"))->get();

//Not Raw Query
Model::where('created_at', '>=', date('Y-m-d').' 00:00:00'))->get();

In fact, we can do more neat and eloquent way in laravel like this.

Model::whereDate('created_at', '=', date('Y-m-d'))->get();

We can also use Carbon function instead of date(), result will still the same.

Model::whereDate('created_at', '=', Carbon::today()->toDateString())->get();

If you want to filter just not with full date, it’s totally fine. You can filter with day, month and year like this.

//Day
Model::whereDay('created_at', '=', date('d'))->get();
//Month
Model::whereMonth('created_at', '=', date('m'))->get();
//Year
Model::whereYear('created_at', '=', date('Y'))->get();

There is also another method called `whereBetween` if you want to filter by date range.

Model::whereBetween('created_at', [FROM_DATE, TO_DATE])->get();

By Yuuma

Tweet