Chapter 8. Querying the Database with the Eloquent ORM

In the previous chapters, you learned how build the basic components of an application. In this chapter the Eloquent ORM, another one of the best features that makes Laravel so popular, will be introduced.

In this chapter, we'll cover the following topics:

  • Basic query statements
  • One-to-one, one-to-many, and many-to-many relations
  • Polymorphic relations
  • Eager loading

An ORM, or object relational mapping, explained in the simplest sense, turns a table into a class, its columns into attributes, and its rows into instances of that class. It creates an abstraction layer between the developer and the database and allows for easier programming, since it uses the familiar object-oriented paradigm.

We shall assume that we have a posts table with the following structure:

id

contents

author_id

 

To illustrate this example, the following would be the representation of a posts table:

<?php
namespace MyBlog;

class Post {
}

To add in the id, contents, and author_id attributes, we will add the following code to the class:

class Post {
    private $id;
    private $contents;
    private $author_id;

    public function getId()
    {
        return $this->id;
    }

    public function setId($id)
    {
        $this->id = $id;
    }

    public function getContents()
    {
        return $this->contents;
    }

    public function setContents($contents)
    {
        $this->contents = $contents;
    }

    public function getAuthorId()
    {
        return $this->author_id;
    }

    public function setAuthorId($author_id)
    {
        $this->author_id = $author_id;
    }

}

This gives us an overview of how a table may be represented by a class: the Post class represents an entity that has a collection of posts.

If the active record pattern was followed, then Eloquent can automatically manage all of the class names, key names, and their related relations. The power of Eloquent lies in its ability to give the programmer the ability to use object-oriented methods to manage the relations between the classes.

Basic operations

We will now discuss some of the basic operations. There are virtually hundreds of ways to use Eloquent, and certainly every developer will use Eloquent in the best way for their project. The following techniques are the basic building blocks upon which more complex queries may be developed.

Finding one

One of the most basic operations is to perform the following query:

select from rooms where id=1;

This is accomplished by using the find() method.

The Room facade is called with the find method, which accepts the ID as an argument:

MyCompanyAccommodationRoom::find($id);

Since Eloquent is based on the fluent query builder, any fluent method may be mixed and matched. Some of the fluent methods are chainable and others execute the query.

The find() method actually executes the query, so it always needs to be at the end of the expression.

If the ID of the model is not found, then nothing is returned. To force a ModelNotFoundException, which can then be trapped to perform some other operation such as logging, add OrFail as follows:

MyCompanyAccommodationRoom::findOrFail($id);

The where method

To query an attribute (column) other than ID, use the following command:

select from accommodations where name='Lovely Hotel';

Use the where method followed by the get() method:

MyCompanyAccommodation::where('name','Lovely Hotel')->get();

The like comparator may be used as follows:

MyCompanyAccommodation::where('name','like','%Lovely%')->get();

Chaining functions

Multiple where methods can be chained as follows:

MyCompanyAccommodation::where('name','Lovely Hotel')- >where('city','like','%Pittsburgh%')->get();

The preceding command produces the following query:

select * from accommodations where name ='Lovely Hotel' and description like '%Pittsburgh%'

Notice that if the where comparator is = (equals), then the second parameter (the comparator) is not needed, and the second part of the comparison is passed into the function. Also, note that an and operation is added between the two where methods. To achieve an or operation, the following change has to be made to the code:

MyCompanyAccommodation::where('name','Lovely Hotel')- >orWhere('description','like','%Pittsburgh%')->get();

Notice that or is added to the where creating orWhere().

Finding all

To find all of the rooms, the all() method is used in place of find. Notice that this method actually executes the query:

MyCompanyAccommodationRoom::all();

To limit the number of rooms, the take method is used in place of find. Since take is chainable, get is needed to execute the query:

MyCompanyAccommodationRoom::take(10)->get();

To achieve pagination, the following query may be used:

MyCompanyAccommodationRoom::paginate();

By default, the preceding query will return a JSON object as follows:

{"total":15,        "per_page":15,
"current_page":1,      "last_page":1,
"next_page_url":null,   "prev_page_url":null,
"from":1,        "to":15,
"data":[
{"id":9,"name":"LovelyHotel","description":"Lovely Hotel Greater Pittsburgh","location_id":1,"created_at":null,"updated_at": "2015-03-13 22:00:23","deleted_at":null,"franchise_id":1},{"id":12, "name":"Grand Hotel","description":"Grand Hotel Greater Cleveland","location_id":2,"created_at":"2015-02- 0820:09:35","updated_at":"2015-02- 0820:09:35","deleted_at":null,"franchise_id":1}
...

Attributes such as total, per_page, current_page, and last_page are used to give the developer an easy way to implement paging, while the array of data is returned inside of an array called data.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset