<?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 = ?";
Comments
Post a Comment