Laravel Laravel Database Stuff Cheatsheet - everything you need to know

Laravel Laravel Database Stuff Cheatsheet

How to increment a value in the database

You can just use the ::increment methods to 'plus one' a value. You can also send how many to increment in the 2nd param.

  1. \App\User::findOrFail($user_id)->increment("profile_view_count");
  2. // or to increment by 5
  3. \App\User::findOrFail($user_id)->increment("profile_score_count",5);

You can also of course use the ::decrement method too.

If you use this as part of the query builder you will face some problems chaining this. It will return an int, not the query builder object. You have to do it like this:

  1. $query = \App\User::where("id",1);
  2.  
  3. $query->decrement("profile_view_count",5);
  4. $query->increment("profile_rating",1);
  5.  
  6.  
  7. // this will result in an error:
  8.  \App\User::where("id",1)->decrement("profile_view_count",5)->increment("profile_rating",1);
  9.  
  10.  //although you could do it with just one:
  11.  $num = \App\User::where("id",1)->decrement("profile_view_count",5);
View More Details (and 7 discussions about this topic) Here...

How to change the order that migrations happen?

Rename the migration file, as it is sorted alphabetically

For example if you change database/migrations/2014_10_12_000000_create_users_table.php to something like database/migrations/2018_10_11_000000_create_users_table.php then it will be the most recent one processed when doing php artisan migrate.

View More Details (and 11 discussions about this topic) Here...

How to fix the MySQL related "Specified key was too long error" error in Laravel

This is a common problem (especially if you use a local development environment such as XAMPP).

The error will look something like this:

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

 [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Luckily the fix is simple. Add the following to the boot() method of your app/Providers/AppServiceProvider.php file:

  1. Schema::defaultStringLength(191);
View More Details (and 11 discussions about this topic) Here...

How to select what columns to return when calling ::all()

You can provide an array of column names to returns, such as:

  1. $posts = Posts::all(["post_name","id"]);
View More Details (and 4 discussions about this topic) Here...

What kind of database field type should you (normally) use for foreign keys?

For most cases you will want to use unsignedInteger for foreign keys. The actual answer is that the foreign key should be the same type as the primary key on the main table, which is normally an unsignedInteger.

Even if it isn't an actual foreign key you should be using this for anything that relates to another unsignedInteger. It is marginally quicker than just using integer.

  1. $table->unsignedInteger('user_id');
  2. $table->foreign('user_id')->references('id')->on('users');
View More Details (and 12 discussions about this topic) Here...

How to show (or log) all SQL queries executed by Laravel

There are a few ways to show the SQL queries that are generated and executed by Laravel

toSql() - You can replace ->get() (or ->paginate()) with ->toSql() to return a string of the SQL query that was generated

  1. $posts = Posts::where("type",$type)->get();
  2. // or as SQL statement
  3. $postsSql =  Posts::where("type",$type)->toSql();

But maybe you want to see all of the queries that Laravel executes for each request?

Query Logging - you can enable the query log, then somewhere else loop through each logged query:

  1. // put this somewhere...
  2. DB::connection()->enableQueryLog();
  3. // run some queries
  4. Posts::where("title","test")->get();
  5. User::find(1);
  6.  
  7. // then loop through this:
  8. $queries = DB::getQueryLog();

Or add a event listener for each query - Put this in your AppServiceProvider (or another file, such as your routes file or even in a controller)

  1. Event::listen('illuminate.query', function($query)
  2. {
  3.     dump($query);
  4. });

Just remember to take these bits of code out before pushing the changes to production!

View More Details (and 6 discussions about this topic) Here...

How to group a SQL statement in brackets/parenthesis

It is very easy to chain where() methods together in Laravel like this:

  1. $posts = Posts::where("category",1)->where("published",true)->where("active",1)->get()

But this will create a long list of AND WHERE statements (You can also use orWhere for OR WHERE).

But what if you need to group your where statements in brackets (parenthesis)?

If you want something like this (with one part of the WHERE grouped by brackets (parenthesis):

    SELECT *
    FROM `orders`
    WHERE
        category = 1

        AND (
            featured = 1
            OR
            discounted IS NOT NULL
         )
        

Then you will have to do something called parameter grouping.

Wrap those grouped parts of the where statement in something like this:

  1.     ...
  2.    ->where(function ($query) {
  3.             // put your grouped ->where() method calls here
  4.     }) ...

Real world example:

  1. DB::table('users')
  2.             ->where('name', '=', 'John')
  3.  
  4.             ->where(function ($query) {
  5.             // Everything within this closure will be grouped together
  6.                 $query->where('votes', '>', 100)
  7.                       ->orWhere('title', '=', 'Admin');
  8.             })
  9.  
  10.             ->get();

This would output:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

View More Details (and 6 discussions about this topic) Here...