We discussed later how to retrieve rows as objects and perform basic CRUD methods on them. Well,there are other good tasks that eloquent can perform. Let’s discuss some of them and the rest will be discussed in subsequent parts.
Let’s create a sample table .We need first to create migration file with the following command.
$php artisan migrate:make create_users
Created Migration: 2014_03_03_154640_create_users
Generating optimized class loaderThen create table structure. You can build any structure.
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateUsers extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
//add table schema
Schema::create('users', function($table){
$table->increments('id');
$table->string('username',60)->unique();
$table->string('email',100)->unique();
$table->string('biog',200);
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
//drop table
Schema::dropIfExists('users');
}
}Well, Run our migration file to create users table.
$php artisan migrate
Migrated: 2014_03_03_154640_create_usersNow our table is ready, We need to fill it with some dummy data so let’s explore database seeding.
Database Seeding
Laravel extends a simple class to seed your database tables. Navigate to app/database/seeds/.You will find DatabaseSeeder.php file. This file contain DatabaseSeeder class. All you have to do is to call UsersTableSeeder class from DatabaseSeeder.The UsersTableSeeder class used to insert dummy data. Here’s how to seed users table.
class DatabaseSeeder extends Seeder {
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
Eloquent::unguard();
//call uses table seeder class
$this->call('UsersTableSeeder');
//this message shown in your terminal after running db:seed command
$this->command->info("Users table seeded :)");
}
}
class UsersTableSeeder extends Seeder {
public function run()
{
//delete users table records
DB::table('users')->delete();
//insert some dummy records
DB::table('users')->insert(array(
array('username'=>'john','email'=>'[email protected]','biog'=>'PHP Ninga'),
array('username'=>'mark','email'=>'[email protected]','biog'=>'JS Ninga'),
array('username'=>'Karl','email'=>'[email protected]','biog'=>'Jquery Ninga'),
array('username'=>'marl','email'=>'[email protected]','biog'=>'Not Ninga'),
array('username'=>'mary','email'=>'[email protected]','biog'=>'HTML Ninga'),
array('username'=>'sels','email'=>'[email protected]','biog'=>'CSS Ninga'),
array('username'=>'taylor','email'=>'[email protected]','biog'=>'Ruby Ninga'),
));
}
}Little confused !don’t worry,If you aren’t familiar with laravel query builder,I will discuss it soon. Now we are ready to fill users table with these dummy data. Just run db:seed command like that.
$php artisan db:seed
Seeded: UsersTableSeeder
Users table seeded : )Awesome! Check users table, It should be look like this.
Now we have table with seven records.Don’t lose this happy moment and check fetch methods.
Fetch Methods
All
The all() method used to return all rows. Here’s an example.
Route::get('users', function(){
//get all users
return User::all();
});Navigate to http://localhost/<laravel dir>/public/users. You should see this.
[{"id":1,"username":"john","email":"[email protected]","biog":"PHP Ninga","created_at":"0000-00-00 00:00:00","updated_at":"0000-00-00 00:00:00"},
{"id":2,"username":"mark","email":"[email protected]","biog":"JS Ninga","created_at":"0000-00-00 00:00:00","updated_at":"0000-00-00 00:00:00"},
{"id":3,"username":"Karl","email":"[email protected]","biog":"Jquery Ninga","created_at":"0000-00-00 00:00:00","updated_at":"0000-00-00 00:00:00"},
{"id":4,"username":"marl","email":"[email protected]","biog":"Not Ninga","created_at":"0000-00-00 00:00:00","updated_at":"0000-00-00 00:00:00"},
{"id":5,"username":"mary","email":"[email protected]","biog":"HTML Ninga","created_at":"0000-00-00 00:00:00","updated_at":"0000-00-00 00:00:00"},
{"id":6,"username":"sels","email":"[email protected]","biog":"CSS Ninga","created_at":"0000-00-00 00:00:00","updated_at":"0000-00-00 00:00:00"},
{"id":7,"username":"taylor","email":"[email protected]","biog":"Ruby Ninga","created_at":"0000-00-00 00:00:00","updated_at":"0000-00-00 00:00:00"}]Find
The find() method used to retrieve a single or multiple rows. Here’s an example.
Route::get('users', function(){
//get user with id 1
return User::find(1);
//get users with ids 1 and 2
return User::find(array(1,3));
});I think you can guess results. Visit http://localhost/<laravel dir>/public/users if you don’t.
First
The first() method used to retrieve the first record of records set. Here’s an example.
Route::get('users', function(){
//get user with id 1
return User::first();
//get user with id 1
return User::all()->first();
//get user with id 3
return User::find(array(3,4,5))->first();
//set all users to variable
$all_users = User::all();
//get user with id 1
return $all_users->first();
});Update
The update() method used to update row values. This method can’t be used without a constraint. Now let’s modify username of the record with id=1 to Adele (my favourite singer but others are welcomed).
Route::get('users', function(){
//change username to Adele
User::where('id','=',1)->update(array('username'=>'Adele'));
//return record after update
return User::find('1');
//output will be {"id":1,"username":"Adele",...
});Delete
This method used to delete rows. If you don’t like Adele, fine! delete her.
Route::get('users', function(){
//get total number of records (return 7)
echo User::all()->count();
//delete record with id=1
User::where('id','=',1)->delete();
//get total number of records (return 6)
echo User::all()->count();
});Count
We used this method in last example.It is used to get total number of rows like count() PHP function.
Get
The get() method used to return result of a constraint. Here’s an example.
Route::get('users', function(){
//get record with id = 5
return User::where('id','=','5')
//return record catched
->get();
});toSql
This method used at the end of a query chain to return the SQL line used. Here’s an example.
Route::get('users', function(){
//get SQL which selects record with username = mark
return User::where('username','=','mark')
->toSql();
//outputs :
//select * from `users` where `username` = ?
//get SQL which selects record with username = mark or karl
return User::where('username','=','mark')
->orWhere('username','=', 'karl')
->toSql();
//outputs :
//select * from `users` where `username` = ? or `username` = ?
});Lists
The lists() return array of values of specific column passed as first parameter. Here’s an example.
Route::get('users', function(){
//get array of all usernames
return User::lists('username');
});