Laravel5.1模型-增删改查

Published on 2017 - 04 - 01

Seeding the Database

We’re going to spend a lot of time experimenting with retrieving, updating and deleting records, I would like to eliminate much of the tedium otherwise required to create realistic test data. Fortunately, we can do so by seeding the database with test data. As an added bonus, you can use Laravel’s database seeding capabilities for several other purposes, including conveniently populating helper tables. For instance if you wanted users to provide their city and state, then you’d probably require the user to choose the state from a populated select box of valid values (Ohio, Pennsylvania, Indiana, etc.). These values might be stored in a table named states. It would be quite time consuming to manually insert each state name and other related information such as the ISO abbreviation (OH, PA, IN, etc). Instead, you can use Laravel’s seeding feature to easily insert even large amounts of data into your database.

You’ll seed a database by executing the following artisan command:

$ php artisan db:seed 

If you execute this command now, nothing will happen. Actually, something did happen, it’s just not obvious what. The db:seed command executes the file database/seeds/DatabaseSeeder.php, which looks like this:

 <?php

 use Illuminate\Database\Seeder;
 use Illuminate\Database\Eloquent\Model;

 class DatabaseSeeder extends Seeder {

   /**
    * Run the database seeds.
    *
    * @return void
    */
   public function run()
   {

     Model::unguard();

     // $this->call('UserTableSeeder');

     Model::reguard();

   }

}

You’ll use DatabaseSeeder.php to pre-populate, or seed, your database. The run() method is where all of the magic happens. Inside this method you’ll reference other seeder files, and when db:seed executes, the instructions found in those seeder files will be executed as well. The Laravel developers provide a commented-out example of how you’ll reference this files:

 // $this->call('UserTableSeeder');

The UserTableSeeder.php file doesn’t actually exist, but if it did it would be found in the database/seeds/ directory. Let’s create a seeder for populating a few Todolist records. Create a new file named TodolistTableSeeder, placing it in database/seeds/. Add the following contents to it:

 <?php    

 use Illuminate\Database\Seeder;
 use Illuminate\Database\Eloquent\Model;
 use todoparrot\Todolist;

 class TodolistTableSeeder extends Seeder {

   public function run()
   {

     Todolist::create([
       'name' => 'San Juan Vacation',
       'description' => 'Things to do before we leave for Puerto Rico!'
     ]);

     Todolist::create([
       'name' => 'Home Winterization',
       'description' => 'Winter is coming.'
     ]);

      Todolist::create([
       'name' => 'Rental Maintenance',
       'description' => 'Cleanup and improvements for new tenants'
     ]);

   }

}

Save this file and then replace the DatabaseSeeder.php line $this->call('UserTableSeeder'); with the following lines:

DB::table('todolists')->delete();
$this->call('TodolistTableSeeder');

After saving the changes you’ll need to rebuild the classmap:

$ composer dump-autoload
Generating autoload files

Finally, run the seeder anew:

$ php artisan db:seed
Seeded: TodolistTableSeeder

Check your project database and you should see several new records in the todolists table!

Creating Large Amounts of Sample Data

The above approach works fine when you’d like to create a small set of sample data, but what if you wanted to simulate a real-world data set involving hundreds or thousands of lists? Surely it wouldn’t be a wise use of time to manually create each record as carried out in the previous example. Fortunately you can use the fantastic Faker library (which is now included in Laravel 5.1+ projects by default) to easily create large amounts of sample data. Let’s modify TodolistTableSeeder.php to generate 50 lists using Faker.

If you have a look at your project’s composer.json file, you’ll see the Faker package is included in require-dev as presented below:

"require-dev": {
  "fzaninotto/faker": "~1.4",
  ...
},

We’ll use Faker to create fifty lists. Modify the TodolistTableSeeder’s run() method to look like this:

 class TodolistTableSeeder extends Seeder {

   public function run()        
   {   

     $faker = \Faker\Factory::create();

     Todolist::truncate();      

     foreach(range(1,50) as $index)  
     {

         Todolist::create([     
           'name' => $faker->sentence(2),  
           'description' => $faker->sentence(4),
         ]);                    

     }

   }

} 

In this example a new instance of the Faker class is created. Next the Todolist’s underlying table (todolists) is truncated, meaning all of the existing records are removed. Strictly speaking you might not have to do this depending upon how your database tables are configured, however presuming you are using seeding as a development aid then you’ll probably want to just repeatedly rebuild the same data set. Next, a foreach statement is used to loop over the Todolist::create fifty times, with each iteration resulting in Faker creating two random Lorem Ipsum-style sentences consisting of two and four words, respectively.

After saving the changes, run php artisan db:seed again. After the command completes, enter your database and you should see fifty records that look similar to the records found below:

mysql> select name, description from todolists limit 4;
+----------------------+-----------------------------------------------+
| name                 | description                                   |
+----------------------+-----------------------------------------------+
| Sed voluptates.      | Accusamus sit et excepturi voluptas.          |
| Debitis dignissimos. | Cum quia ut.                                  |
| Earum et dolore.     | Quis necessitatibus magnam deserunt error id. |
| Nesciunt sequi.      | Porro ratione non non.                        |
+----------------------+-----------------------------------------------+

Random sentence generation is only a small part of what Faker can do. You can also use Faker to generate random numbers, lorem ipsum paragraphs, male and female names, U.S. addresses, U.S. phone numbers, company names, e-mail addresses, URLs, credit card information, colors, and more! Be sure to check out the Faker documentation for examples of these other generators. We’ll also return to Faker throughout the remainder of the book to generate various data sets, so stay tuned!

Finding Data

Most Laravel queries are very straightforward in that they’ll simply involve retrieving a record based on its primary key or some other filter, while others require more sophisticated approaches involving multiple parameters, complex sorting, and table joins. Fortunately Laravel offers an incredibly rich set of methods for querying data in a variety of fashions. In this section I’ll show you the many ways in which data can be retrieved from your application’s database.

Retrieving All Records

Perhaps the easiest query involves retrieving all of a table’s records using the all method. To recap, the following example will retrieve all of the lists:

$lists = Todolist::all();

The $lists variable is an instance of Illuminate\Database\Eloquent\Collection, which is among other things iterable. This means you can loop over the records using standard PHP syntax. Fire up tinker and try it for yourself:

>> namespace todoparrot;
>>> $lists = Todolist::all();
=> <Illuminate\Database\Eloquent\Collection...
>>> foreach ($lists as $list) {
... printf("%s\n", $list->name);
... }
San Juan Vacation
Home Winterization
Rental Maintenance

Alternatively, because the results are returned as an Eloquent collection, you have access to a variety of useful methods, including each. In the following example I’ll use each in conjunction with a closure to arguably more eloquently iterate over the results:

>>> $lists = todoparrot\Todolist::all();
>>> $lists->each(function($list) {
... echo $list->name;
... });

You’ll likely rarely want to use the all() method unless the target model is associated with a trivial (less than one thousand) number of records. However if you would like to provide a solution for viewing a large number of records, consider paginating the results. I’ll introduce Laravel’s pagination feature in the later section, “Paginating Results”.

Retrieving Records by Primary Key

When viewing a list detail page or user profile, or updating a particular record, you’ll want to unmistakably retrieve the desired record, done by querying for the record using its primary key. To do so you’ll use the find method, passing along the primary key:

[1] > $list = todoparrot\Todolist::find(3);
[2] echo $list
{ "id":3, 
  "name":"Rental Maintenance", 
  "description":"Cleanup and improvements for new tenants",
  "created_at":"2015-06-02 17:38:55",
  "updated_at":"2015-06-02 17:38:55"
}

Implementing the RESTful Show Action

Now that you know how to use the find method, let’s implement the Lists controller’s show action. The action currently looks like this:

public function show($id)
{
}

Because the show action is intended to display a specific instance of a particular resource, the action is automatically configured to pass along the resource’s ID via the $id variable. We’ll use the find method to retrieve the desired record, and then pass the Todolist object into the view. The updated show action looks like this:

use todoparrot\Todolist;
...

public function show($id)
{
  $list = Todolist::find($id);
  return view('lists.show')->with('list', $list);
}

Next we’ll create the corresponding view. Create a file named show.blade.php, placing it in the directory resources/views/lists. Add the following contents to it:

 @extends('layouts.master')

 @section('content')

 <h1>{{ $list->name }}</h1>

 <p>
 Created on: {{ $list->created_at }} 
 Last modified: {{ $list->updated_at }}<br />
</p>

<p>
{{ $list->description }}
</p>

@endsection

After saving the changes, navigate to the Lists controller’s show URI (append an appropriate ID to /lists/) and you should see output that looks something like this (HTML tags included for clarity):

<h1>Rental Maintenance</h1>
<p>
Created on: 2015-06-02 17:38:55 Last modified: 2015-06-02 17:38:55<br />
Cleanup and improvements for new tenants
</p>

Brilliant! We’re now able to view more information about a specific list. But what happens if the user attempts to access a record that doesn’t exist, such as /lists/23245? The find method would return a null value, meaning any attempts to retrieve a property of a non-object within the view would result in a view exception. Logically you’ll want to avoid this sort of mishap, and can do so using the findOrFail method instead:

$list = Todolist::findOrFail($id);

If the desired record is not found, an exception of type ModelNotFoundException will be thrown. You can listen for these exceptions by registering an error handler in app/Exceptions/Handler.php, and redirecting users encountering a ModelNotFoundException exception to a custom 404 view. Begin by creating a file named 404.blade.php in resources/views/errors/404.blade.php. Add a simple message to it for the moment, something like File not found will suffice. Obviously you’ll want to further customize this 404 page before deploying to production.

Next, open up app/Exceptions/Handler.php and add the following method:

 protected function renderModelNotFoundException(ModelNotFoundException $e)
 {

   if (view()->exists('errors.404'))
   {
     return response()->view('errors.404', [], 404);
   }
   else
   {
    return (new SymfonyDisplayer(config('app.debug')))
      ->createResponse($e);
  }
}

This method borrows heavily from the native renderHttpException method found in the following file:

vendor/laravel/framework/src/Illuminate/Foundation/Exceptions/Handler.php

It will render the previously created 404.blade.php view if the view exists, or otherwise display a more terse exception-related message. Next, modify the app/Exceptions/Handler.php’s render method to look like this:

use Illuminate\Database\Eloquent\ModelNotFoundException;

...

 public function render($request, Exception $e)
 {

  if ($e instanceof ModelNotFoundException)
   {
     return $this->renderModelNotFoundException($e);
   }
   else
   {
     return parent::render($request, $e);
   }

}

The modified code extends what’s already being done to render HTTP exceptions, adding additional functionality to handle exceptions of type ModelNotFound (ModelNotFoundException).

Finally, test it out by accessing some record you know to not exist, such as /lists/asdf. The asdf key will be passed to findOrFail, presumably not be found, and send the user to the 404 page.

Selecting Specific Columns

For performance reasons you should to construct queries that retrieve the minimal data required to complete the desired task. For instance if you’re constructing a list view that only displays the list name and description, there is no reason to retrieve the id, created_at, and updated_at columns. You can restrict which columns are selected using the select method, as demonstrated here:

$ php artisan tinker
>>> $lists = todoparrot\Todolist::select('name', 'description')->first();
>>> echo $lists->name;
Sed voluptates.
>>> echo $lists;
{"name":"Sed voluptates.","description":"Accusamus sit et excepturi."}

Counting Records

To count the number of records associated with a given model, use the count method:

>> echo todoparrot\Todolist::count();
50

You can also use count to determine how many records have been selected:

$lists = Todolist::all();
...
{{ $lists->count() }} records selected.

Ordering Records

You can order records using the orderBy method. You’ll use this method in conjunction with get. The following example will retrieve all Todolist records, ordered by name:

$lists = Todolist::orderBy('name')->get();

You’ll use the get() method to retrieve records using methods other than all or find.

Laravel will by default sort results in ascending order. You can change this default behavior by passing the desired order (ASC or DESC) as a second argument to orderBy:

$lists = Todolist::orderBy('name', 'DESC')->get();

You can order results using multiple column by calling orderBy multiple times:

$lists = Todolist::orderBy('created_at', 'DESC')
  ->orderBy('name', 'ASC')
  ->get();

This is equivalent to executing the following SQL statement:

SELECT * FROM todolists ORDER BY created_at DESC, name ASC;

Using Conditional Clauses

While the find method is useful for retrieving a specific record, you’ll often want to find records using other attributes. You can do so using the where method. Suppose the Todolist model included a Boolean complete attribute, intended to denote whether the user considered the list completed. You could use where to retrieve a set of completed lists:

$lists = Todolist::where('complete', '=', 1)->get();

Notice how the attribute, comparison operator, and value are passed into where as three separate arguments. This is done as a safeguard against attacks such as SQL injection. If the comparison operator is =, you can forgo providing the equal operator altogether:

$lists = Todolist::where('complete', 1)->get();

However, if you’re using an operator such as > or <, you are logically required to expressly supply the operator. You can alternatively use the whereRaw method (without sacrificing security) to accomplish the same result:

$lists = Todolist::whereRaw('complete = ?', 1)->get();

Grouping Records

Grouping records according to a shared attribute provides opportunities to view data in interesting ways, particularly when grouping is performed in conjunction with an aggregate SQL function such as count() or sum(). Laravel offers a method called groupBy that facilitates this sort of query. Suppose you wanted to retrieve the years associated with all lists’ creation dates and the count of lists associated with each year. You could construct the query in MySQL like so:

mysql> select year(created_at) as `year`, count(name) as `count` 
     -> from todolists
     -> group by `year` order by `count` desc;
 +--------------+
 | year | count |
 +------+-------+
 | 2015 |   398 |
 | 2014 |   247 |
 | 2013 |   112 |
 | 2012 |   92  |
| 2011 |   14  |
+---------------+
5 rows in set (0.00 sec)

This query can be reproduced in Laravel like so:

use DB;

...

$lists = Todolist::select(DB::raw('year(created_at) as year'), 
         DB::raw('count(name) as `count`'))
         ->groupBy('year')
         ->orderBy('`count` desc')->get();

Another new concept was introduced with this example: DB::raw. Eloquent currently does not support aggregate functions however you can use Laravel’s Query Builder interface in conjunction with Eloquent as a convenient workaround. The DB::raw method injects raw SQL into the query, thereby allowing you to use aggregate functions within select. I’ll talk more about Query Builder’s capabilities in the later section, “Introducing Query Builder”.

You can then iterate over the year and count attributes as you would any other:

 @if ($lists->count() > 0)
   <ul>
     @foreach ($lists as $list) 
       <li>{{ $list->count }} lists created in {{ $list->year }}</li>
     @endforeach
   </ul>
 @else
   <p>
     No lists found!
   </p>  
 @endif

You’ll often want to group records in conjunction with a filter. For instance, what if you only wanted to retrieve a grouped count of arcade games released by year in the years after 2010? You could use groupBy in conjunction with where:

$lists = Todolist::select(
          DB::raw('year(created_at) as year'), 
          DB::raw('count(name) as count'))
          ->groupBy('year')
          ->where('year', '>', '2010')->get();

This works because you’re filtering on the non-aggregated field. What if you wanted to instead retrieve the same information, but only those years in which more than 50 lists were created? At first blush it would seem you could use group in conjunction with where to filter the results, however as it turns out you can’t use where to filter anything calculated by an aggregate function, because where applies the defined condition before any results are calculated, meaning it doesn’t know anything about the count alias at the time it attempts to perform the filter. Instead, when you desire to filter on the aggregated result, you’ll use having. Let’s revise the previous broken example to use having instead of where:

$lists = Todolist::select(
          DB::raw('year(created_at) as year'), 
          DB::raw('count(name) as count'))
          ->groupBy('year')
          ->having('year', '>', '2010')->get();

Limiting Returned Records

Sometimes you’ll want to just retrieve a small subset of records, for instance the five most recently added lists. You can do so using the oddly-named take method:

$lists = Todolist::take(5)->orderBy('created_at', 'desc')->get();

If you wanted to retrieve a subset of records beginning at a certain offset you can combine take with skip. The following example will retrieve five records beginning with the sixth record:

$lists = Todolist::take(5)->skip(5)->orderBy('created_at', 'desc')->get();

If you’re familiar with SQL the above command is equivalent to executing the following statement:

SELECT * from todolists ORDER BY created_at DESC LIMIT 5 OFFSET 5;

Retrieving the First or Last Record

It’s often useful to retrieve just the first or last record found in a collection. For instance you might want to offer users a visual aide highlighting the most recently created list:

$list = Todolist::orderBy('created_at', 'desc')->first();

To retrieve a collection’s last record, you can also use first() and reverse the order. For instance to retrieve the oldest list, you’ll use the same snippet as above but instead order the results in ascending fashion:

$list = Todolist::orderBy('created_at', 'asc')->first();

Retrieving a Random Record

There are plenty of reasons you might wish to retrieve a random record from your project database. Perhaps a future version of TODOParrot would highlight an incomplete list in the hopes of spurring the user into action. You might at first glance conclude the following approach is the most straightforward:

$list = Todolist::all()->random(1);

Eloquent collections support the random method, which retrieves one or more random records from a collection. However unless the model’s underlying table size is vanishingly small, you should not use this approach, because it requires all records to first be retrieved from the database! Instead, you can use the DB::raw method first used in the section “Grouping Records” to pass the SQL RAND() function into orderBy, as demonstrated here:

$list = Todolist::orderBy(DB::raw('RAND()'))->first();

This is equivalent to executing the following SQL:

select * from `todolists` order by RAND() asc limit 1

Determining Existence

If your sole goal is to determine whether a particular record exists, without needing to actually load the record if it does, use the exists method. For instance to determine whether a list category named Home exists use the following statement:

$exists = Todolist::where('name' , '=', 'San Juan Vacation')->exists();

Using exists instead of attempting to locate a record and then examining the object or counting results is preferred for performance reasons, because exists produces a query that just counts records rather than retrieving them:

select count(*) as aggregate from `todolists` 
where `name` = 'San Juan Vacation'

Paginating Results

If users only planned on maintaining a few lists then retrieving and displaying the lists using the all method is going to do the job nicely. However the TODOParrot team is intent on becoming the world’s most popular TODO list management company, hopefully culminating in users creating and maintaining dozens if not hundreds of lists for all of life’s activities. To help users quickly and easily find a desired list you’ll probably want to paginate them across multiple pages.

Database pagination is accomplished using a series of queries involving limit and offset clauses. For instance, to retrieve lists in batches of 10 sorted by name you would execute the following queries (MySQL, PostgreSQL and SQLite):

SELECT id, name FROM todolists ORDER BY name ASC LIMIT 10 OFFSET 0 
SELECT id, name FROM todolists ORDER BY name ASC LIMIT 10 OFFSET 10

Incidentally, MySQL, PostgreSQL and SQLite all use a 0-based index, meaning executing the first query is the same as executing:

SELECT id, name FROM locations ORDER BY name ASC LIMIT 10

Therefore when creating a pagination solution you would need to keep track of the current offset and limit values, the latter of which might be variable if you gave users the opportunity to adjust the number of items presented per page. Further, you would also need to create a user interface for allowing the user to navigate from one page to the next. Fortunately, pagination is a key feature of many web applications, meaning turnkey solutions are often incorporated into frameworks, Laravel included!

To paginate results, you’ll use the paginate method:

$lists = Todolist::orderBy('created_at', 'desc')->paginate(10);

In this example we’re overriding the default number of records retrieved (15), lowering the number retrieved to 10. Once retrieved, you can iterate over the collection using @foreach just as you would were pagination not being used. You’ll however want to make a slight modification to the view, adding the pagination ribbon:

{!! $lists->render() !!}

This will create a stylized list of links to each available page, similar to the screenshot presented below.

Inserting New Records

Laravel offers a few different approaches to creating new records. The first involves the save method. To save a record using save, you’ll first create a new instance of the desired model, update its attributes, and then execute the save method:

$list = new Todolist;
$list->name = 'San Juan Vacation';
$list->description = 'Pre-vacation planning';
$list->save();

Presuming your underlying table incorporates the default id, created_at and updated_at fields, Laravel will automatically update the values of these fields for you.

You can alternatively use the create method, simultaneously setting and saving the model attributes:

$list = Todolist::create(
        array('name' => 'San Juan Vacation', 
              'description' => 'Pre-vacation planning')
       );

Creating a Record if It Doesn’t Exist

It’s also possible to create a new record only if a record with a matching attribute isn’t found:

$list = Todolist::firstOrCreate(array('name' => 'San Juan Vacation'));

Keep in mind however that firstOrCreate will fail should you neglect to provide values for any fields not associated with default values. Of course, this is a catch-22, because identifying these attributes and values within firstOrCreate means the filter will additionally use those attributes in an attempt to find a matching record, which is likely not the behavior you desire. Instead, you’ll probably want to use firstOrNew, because it will just create a new model instance if a record matching the provided attribute isn’t found:

$list = Todolist::firstOrNew(array('name' => 'San Juan Vacation'));
$list->description('Too much to do before vacation!');
$list->save();

However, if your intent is to update a record if it exists or create a new record if not match is found, you might consider using updateOrCreate. It does allow you to specify an attribute argument separately from the values you’d like to create or update depending upon whether a record is found:

$list = Todolist::updateOrCreate(
  array('name' => 'San Juan Vacation'), 
  array('description' => 'Too much to do before vacation!')
);

The first array defines the attributes used to determine whether a matching record exists, and the second array identifies the attributes and values which will be inserted or updated based on the outcome. If the former, then the attributes found in the first array will be inserted into the new record along with the attributes found in the second array.

Updating Existing Records

Users will logically want to update existing lists, perhaps tweaking the list name or description. To do so, you’ll typically retrieve the desired record using its primary key, update the attributes as necessary, and use the save method to save the changes:

$list = Todolist::find(14);
$list->name = 'San Juan Holiday';
$list->save();

Deleting Records

To delete a record you’ll use the delete method:

$list = Todolist::find(12);
$list->delete();

You can optionally consolidate these two commands using the destroy method:

Todolist::destroy(12);

Implementing the RESTful Destroy Method

The Lists controller’s destroy method is the easiest to implement, because typically a companion view isn’t required. You’ll just delete the desired record and redirect the user to a designated location. Below is the modified Lists controller’s destroy method, which accepts the ID of the record designated for deletion and then redirects the user to the Lists controller’s index action:

public function destroy($id)
{
  Todolist::destroy($id);
  return \Redirect::route('lists.index');
}

While this bit of logic is easy enough, it doesn’t shed any insight into how the user actually executes this action, particularly because as the table found in the earlier section “Creating a RESTFul Controller” indicates, this route is only accessible via the DELETE method. You’re probably familiar with the GET and POST methods, however unless you have prior experience implementing RESTful applications then DELETE is probably entirely unfamiliar.

Soft Deleting Records

In many cases you won’t ever actually want to truly remove records from your database, but instead annotate them in such a way that users perceive them to be deleted. This is known as a soft delete. Laravel natively supports soft deletion, requiring just a few configuration changes to ensure a model’s records aren’t actually deleted when delete or destroy are executed. As an example let’s modify the Todolist model to support soft deletion. Begin by creating a new migration that adds a column named deleted_at to the todolists table:

$ php artisan make:migration add_soft_delete_to_todolists --table=todolists 
Created Migration: 2015_06_07_203253_add_soft_delete_to_todolists
Generating optimized class loader

Next open up the newly created migration (found in the database/migrations directory), and modify the up and down methods to look like the following:

 public function up()
 {
   Schema::table('todolists', function(Blueprint $table)
   {
     $table->softDeletes();
   });
 }

 public function down()
 {
   Schema::table('todolists', function(Blueprint $table)
   {
     $table->dropColumn('deleted_at');
   });
 }

Save the changes and run the migration:

$ php artisan migrate

After the migration has completed you’ll next want to open up the target model (again I’m using Todolist as an example although this feature isn’t actually integrated into TODOParrot) and use the SoftDeleting trait:

<?php namespace todoparrot;

 use Illuminate\Database\Eloquent\Model;
 use Illuminate\Database\Eloquent\SoftDeletes;

 class Todolist extends Model {

     use SoftDeletes;

     protected $dates = ['deleted_at'];

 }

Although not strictly necessary, adding the deleted_at attribute to the $dates array as demonstrated above will cause any returned deleted_at values to be of type Carbon as first discussed in the earlier section “Defining Methods”.

After saving these changes, the next time you delete a record associated with this model, the deleted_at column will be set to the current timestamp. Any record having a set deleted_at timestamp will not be included in any retrieved results, thereby seemingly having been deleted. Of course, there are plenty of practical reasons why you might want to at some point include soft deleted records in your results (for instance giving users the ability to recover a previously deleted record). You can do so using the withTrashed method:

$lists = Todolist::withTrashed()->get();

Introducing Query Builder

Chances are you’re going to be able to successfully carry out 99% of the database operations you desire using Eloquent, however you’ll occasionally want to exercise a bit of additional control over your queries. Enter Query Builder, Laravel’s alternative approach to querying your project database.

You can retrieve all of the records found in the todolists table using Query Builder like this:

$lists = DB::table('todolists')->get();

This returns an array of objects of type stdClass, meaning you can iterate over the returned objects like this:

foreach ($lists as $list) {
  echo $list->name;
}

If you’re looking for a specific record and want to search for it by ID, you can use find:

$list = DB::table('todolists')->find(52);

If you’re only interested in retrieving the name column, there’s no sense retrieving the descriptions and other columns. You can use select to limit the results accordingly:

$lists = DB::table('todolists')->select('name')->get();

Finally, there are instances where it makes more sense to directly execute raw SQL. You can do this using several different approaches. To select data, you can use DB:select:

$lists = DB::select('SELECT * from todolists');

This returns an array of objects as was the case with the introductory example in this section. If you wanted to insert, update, or delete data using raw SQL, you can use the DB::insert, DB::update, and DB::delete methods, respectively:

DB::insert('insert into todolists (name, description) values (?, ?)', 
  array('San Juan Vacation', 'Things to do before vacation');

DB::update('update todolists set completed = 1 where id = ?', array(52));

DB::delete('delete from todolists where completed = 1');

If you wanted to run SQL that isn’t intended to interact with the data directly, perhaps something of an administrative nature, you can use DB::statement:

$lists = DB::statement('drop table todolists');

As mentioned, this isn’t intended to be anything more than a brief introduction to Query Builder. See the documentation for a much more comprehensive summary of what’s available.

Reference