laravel-eloquent-orm.php

<?php
  
  /*
  |--------------------------------------------------------------------------
  | Return a Collection of Objects
  |--------------------------------------------------------------------------
  */

  // collection of all users
  $sql = "select * from users";
  $users = User::all();
  $users = User::get();

  // name and email of all users
  $sql = "select name, email from users";
  $attributes = ['name', 'email'];
  $users = User::get($attributes);

  // collection of all users filtered by where clause
  $sql = "select * from users where active = 1";
  $users = User::where('active', 1)->get();
  $users = User::where('name', 'like', 'J%')->get();

  // collection of all users filtered by multiple where clauses
  $sql = "select * from users where active = 1 and name like 'G%'";
  $users = User::where('active', 1)
            ->where('name', 'like', 'G%')
            ->get();

  // colleciton of all users ordered by date created
  $sql = "select * from users order by created_at";
  $users = User::orderBy('created_at')->get();
  $users = User::orderBy('created_at', 'desc')->get();

  // a collection of only 5 users
  $sql = "select * from users limit 5";
  $users = User::all()->take(5);
  $users = User::take(5)->get();

  // a collection of only 5 users with a skip
  $sql = "select * from users limit 5 offset 5";
  $users = User::take(5)->skip(5)->get();

  // a collection of the last 5 users created
  $sql = "select * from users order by created_at desc limit 5";
  $users = User::orderBy('created_at', 'desc')->take(5)->get();

  // name and email of the last 3 active users created
  $sql = "select name, email from users where active = 1 order by created_at desc limit 3";
  $users = User::where('active', 1)
            ->orderBy('created_at', 'desc')
            ->take(3)
            ->get(['name', 'email']);

  // collection of all users where active is either 0 or 1
  $sql = "select * from users where active in (0, 1)";
  $users = User::whereIn('active', [0, 1])->get();

  /*
  |--------------------------------------------------------------------------
  | Return a Single Object
  |--------------------------------------------------------------------------
  */

  // single user object by id
  $sql = "select * from users where id = 1 limit 1";
  $user = User::find(1);
  $user = User::where('id', 1)->get()->first();
  $user = User::where('id', 1)->first();

  // single user object by name
  $sql = "select * from users where name = 'Graeson' limit 1";
  $user = User::where('name', 'Graeson')->get()->first();
  $user = User::where('name', 'Graeson')->first();

  // single user object with email and created_at attributes
  $sql = "select email, created_at from users where name = 'Graeson' limit 1";
  $user = User::where('name', 'Graeson')->get(['email', 'created_at'])->first();


  /*
  |--------------------------------------------------------------------------
  | Return a Scalar Value
  |--------------------------------------------------------------------------
  */

  // string value for a single column
  $sql = "select name from users where id = 1 limit 1";
  $name = User::find(1)->pluck('name');
  $name = User::where('id', 1)->first()->pluck('name');
  $name = User::where('id', 1)->get()->first()->pluck('name');

  // scalar value with object count
  $sql = "select count(*) from users";
  $count = User::count();
  $count = User::all()->count();
  $count = User::get()->count();
  $count = User::where('id', '>', 5)->count();
  $count = User::where('id', '>', 5)->get()->count();

  // an aggregated scalar value (min, max, avg, sum);
  $sql = "select max(id) from users where id > 1";
  $highest = User::max('id');
  $highest = User::all()->max('id');
  $highest = User::get()->max('id');
  $highest = User::where('id', '>', 1)->max('id');
  $highest = User::where('id', '>', 1)->get()->max('id');

  // scalar value with max timestamp (no Carbon, bug?)
  $sql = "select max(created_at) from users";
  $latest = User::max('created_at');

  // carbon instance with max timestamp
  $sql = "select max(created_at) from users";
  $latest = User::all()->max('created_at');
  $latest = User::get()->max('created_at');
  $latest = User::where('id', '>=', 1)->get()->max('created_at');

  /*
  |--------------------------------------------------------------------------
  | Increment/Decrement
  |--------------------------------------------------------------------------
  */

  // increments a field by 1 ... static call
  $sql = "update posts set views = views + 1 where id = 101";
  Post::find(101)->increment('views');
  Post::where('title', 'Lyly is Amazeballs!')->increment('views');

  // increment a field by 1 ... instance
  $sql = "update posts set views = views + 1 where id = 101";
  $post = Post::find(101);
  $post->increment('views');

  // decrement a field by 1 ... static call
  $sql = "update posts set views = views - 1 where id = 101";
  Post::find(101)->decrement('views');

  /*
  |--------------------------------------------------------------------------
  | Relationships :: hasMany
  |--------------------------------------------------------------------------
  */

  // a user hasMany posts
  $user = User::find(1);

  // collection of posts for a given user
  $sql = "select * from posts where user_id = 1";
  $posts = $user->posts;
  $posts = $user->posts()->get();

  // collection of posts with specific columns
  $sql = "select title, created_at from posts where user_id = 1";
  $posts = $user->posts()->get(['title', 'created_at']);

  // collection of posts matching criteria
  $sql = "select * from posts where title like 'Q%' and user_id = 1";
  $posts = $user->posts()->where('title', 'like', 'Q%')->get();

  // collection of posts matching criteria and specfic columns
  $sql = "select title, created_at from posts where title like 'Q%' and user_id = 1";
  $posts = $user->posts()->get(['title', 'created_at']);
  $posts = $user->posts()->where('title', 'like', 'Q%')->get(['title', 'created_at']);

  // collection of posts ordered by date created
  $sql = "select * from posts where user_id = 1 order by created_at desc";
  $posts = $user->posts()->orderBy('created_at', 'desc')->get();

  // paginated collection of posts by user
  $sql = "select * from posts where user_id = 1 limit 5 offset 0";
  $posts = $user->posts()->paginate(5);

  // count of all posts for a given user
  $sql = "select count(*) from posts where user_id = 1";
  $count = $user->posts()->count();
  $count = $user->posts()->get()->count();

  // count of all posts for a given user matching criteria
  $sql = "select count(*) from posts where title like 'Q%' and user_id = 1";
  $count = $user->posts()->where('title', 'like', 'Q%')->count();
  $count = $user->posts()->where('title', 'like', 'Q%')->get()->count();

  /*
  |--------------------------------------------------------------------------
  | Relationships :: belongsTo
  |--------------------------------------------------------------------------
  */

  // a post belongs to a user
  $post = Post::findOrFail(101);

  // single user object
  $sql = "select * from user where id = ?";
  $user = $post->user;

  // scalar value for a single column
  $sql = "select name from user where id = ?";
  $name = $post->user->name;
  $name = $post->user->pluck('name');
  $name = $post->user()->get()->first()->name;

  /*
  |--------------------------------------------------------------------------
  | Relationships :: belongsToMany
  |--------------------------------------------------------------------------
  */

  // a post has many likes (hopefully)
  $post = Post::find(3);

  // number of times post has been liked
  $likes = $post->likes->count();

  // a collection of users that like the post
  $users = $post->likes;

  // references to $post->likes resolve to a SQL inner join
  $sql = "select users.*, likes.post_id, likes.user_id from users
            inner join likes on users.id = likes.user_id
            where likes.post_id = ?";


  // a user likes many posts
  $user = User::find(3);

  // number of times a user has liked a post
  $likes = $user->likes->count();

  // a collection of posts liked by a user
  $posts = $user->likes;

  // references to $user->likes also resolve to a SQL inner join
  $sql = "select posts.*, likes.user_id, likes.post_id from posts
          inner join likes on posts.id = likes.post_id
          where likes.user_id = ?";