shanechrisbarker.co.uk

Using Eloquent with Laravel

Published On: Wednesday 23rd of January 2019 18:34:57

Eloquent is a great tool contained within Laravel that provides an easy and efficient way to work with the models in your application.

From defining how models are related, all the way to performing cascading queries. Eloquent provides functionality for pretty much any database work you'll need for a web app.

In this little tutorial, we'll look at how we can use Eloquent to relate models to each other. We'll cover some common Eloquent CRUD queries and finish off with a good dose of joins!

If you've used the Yii framework along with it's Active Record stuff, most of this should be pretty similar.

Relational Database Diagram

Our Database Schema

For us to look at the functionality of Eloquent, we'll first need a database! And in true Blue Peter form, here's one I made earlier. It's simple but for what we want to do look at, it'll do.

I have:

  • A customers table (id, name, address)
  • A products table (id, name, description)
  • An orders table (id, customer_id)
  • An orders_products table (order_id, product_id, quantity)

We'll be using the usual enterprise rules whereby many to many relationships are broken down via a weak entity - Hence our orders_products table.

In our tables, we'll have the following data:

Customers Table

id name address
1 John Doe 123 Test Street
2 Mike Smith 99 Some Street

Products Table

id name description
1 hat A navy blue hat
2 jacket A smart jacket
3 shoes A pair of black shoes

Orders Table

id customer_id
1 2
2 1

order_products Table

order_id product_id quantity
1 2 1
1 1 2
2 3 1

Can you make me a model? Even with this face?

You've probably guessed it but we'll also need a model for each of these tables. If you're following along, go ahead and create them.

Eloquent allows us to avoid writing raw SQL into our applications when we need to query the database. Instead, we write Eloquent queries.

Open up a controller in your application and import the models you just made. Add the following code and dump out the results and you should have an Eloquent collection to begin working with:

$customerModel = new Customer();
$results = $customerModel->get();
This is the equivalent of writing the following SQL:
SELECT *
FROM customers
You can access the values in the collection using the -> operator. For example,
$results[0]->name
would return
"John Doe"

We'll build this query up to eventually return the customers along with their orders and their products but first, we'll need to define the relationships so Eloquent can link them together.

Important Notice Thing!


When creating a model for a database table, always name the model in the singular fashion. That is to say you may have a "customers" table as it contains many customers, but Laravel will expect your model to be named "Customer".

This can sometimes cause new Laravel users some confusion when Eloquent throws an exception due to a missing table.

It may also cause on a debate within your team as it did with mine over whether it should be 'people' or 'persons' though. Laravel says it's 'people' and I agree (here's looking at you, Johnathan).

If you absolutely must override this default behaviour, you can add the following into your model:

protected $table = 'your_table_name';

Defining our relationships

In order for us to tell Eloquent that these tables are related to one another, we have to define this in the application. We'll start off by telling Eloquent that a customer can have many orders but an order can only belong to a single customer. This will then allow us to start querying for customers and the orders which belong to them.

Open up the Customer model and add the following code:

class Customer extends Model
{
    public function Orders()
    {
        return $this->hasMany('App\Order', 'customers_id', 'id');
    }
}
This tells Elequoent that a customer has many orders and also which keys to link on. We can now also add another clause to the query we started building earlier. Alter the query to the following:
$results = $customerModel->with('orders')->get();
You'll see that the orders for each of our customers are now returned in our results. The data for any related models will be stored within the
Relations
array within each result.

We can add the inverse of this relationship by opening up our Order model and adding the following code:

class Order extends Model
{
    public function customer()
    {
        return $this->belongsTo('App\Customer', 'customers_id', 'id');
    }
}
As you can see, the code is almost identical apart from this time we are declaring that an Order will belong to a Customer. and would allow us to use:
$orderModel = new Order();
$results = $orderModel->with('customer')->get();
This would return all orders along with their associated Customer.

Building up more complex relationships

Now we've linked the Order model to the Customer model, let's join the Product model to the Order model. This will then mean that we can retrieve the products that were present on the orders. Kinda useful I guess.

This relationship is slightly more complex than our last. This is due to the fact that a product may belong to many orders and an order may have many products - essentially we have a many to many relationship. We've broken this up in our application with the Orders_product model but how do you tell Eloquent about this weak entity? The solution is to use the hasManyThrough() function.

Inside of your Orders model, add the following function:

public function products()
{
    return $this->hasManyThrough
    (
        'App\Product',  // The Model we want to join
        'App\Orders_product',  // The Model we are joining through
        'order_id',  // The Foreign Key on the table we are joining through
        'id', // the primary key on the model we want to join
        'id', // the primary key of the model we are joining
        'product_id' // the key on the model we are joining through that links to the model we are joining
    );
}

That looks kinda complex, right? I'm not gonna lie. I think it is too. In fact me and my team hate defining these relationships when they start to get complicated. Once you do have it all working however, it can make it super easy to grab data related to your results.

If you now dump out the results of the following code, you'll see that we have not only the customer associated with our order, we now have the products associated with them too.

$results = $orderModel->with('products')->with('customer')->get();

Pretty cool eh? Obviously the more relationships a model has, the longer the query takes to complete. By using the

with->()
method, we are essentially eager loading. Don't bring back more data than you need and this shouldn't become a big problem. Use it when you only really need a few bits and you'll slow right down.

Eloquent isn't only here for defining our relationships though. It has an equivalent for pretty much anything you'd need to do in SQL. In the next section, we'll look at a few commonly used Eloquent queries.

Common Eloquent queries

As promised in the enticing introduction to this article, we'll have a look at some common Eloquent queries. We wont go too deep but most of these commands are the things that the majority of web apps will do during a days work.

If you've used any other PHP framework before then again, this should be quite familiar to you. I came from using the query builder within the Zend Framework and also through Doctrine in Silex to using Eloquent and it certainly felt like seeing a distant cousin or that slightly weird Aunt we all have.

Retrieving data

Getting specific columns - You can easily get just the columns you need:

// SELECT name, address FROM customers
$customer = $customerModel->select('name', 'address')->get();

Getting columns with aliases - Aliasing a column is also easy:

// SELECT name as customer_name FROM customers
$customer = $customerModel->select('name AS customer_name)->get();

Ordering Columns - If you need to specify the order of your results:

// SELECT id, name
// FROM customers
// ORDER BY id DESC
$customer = $customerModel->select('id', 'name')->orderBy('id', 'DESC')->get();

Conditions - Obviously we can specify conditions on what we would like returning:

// SELECT *
// FROM customers
// WHERE id > 1
$customer = $customerModel->where('id', '>', 1)->get();

For operations other than equality checks, you can add a paramater to that method. For example, to return all results where the id is greater than 1, you could use:

// SELECT *
    // FROM customers
    // WHERE name = 'John Doe'
    $customer = $customerModel->where('name', 'John Doe')->get();

Multiple Where clauses - where this AND that

/*
SELECT *
FROM customers
WHERE name = 'John Doe'
AND id = 1
*/
$customer = $customerModel->where('name', 'John Doe')->where('id', 1)->get();

Multiple Where clauses - where this OR that

/*
SELECT *
FROM customers
WHERE name = 'John Doe'
OR id = 2
*/
$customer = $customerModel->where('name', 'John Doe')->orWhere('id', 2)->get();

Where In clauses - where this IN that

/*
SELECT *
FROM customers
WHERE id
IN (1, 2, 3)
*/
$customer = $customerModel->whereIn('id', [1, 2, 3])->get();

Query smarter - If you know you only need the first, use first() instead of get()!

/*
SELECT *
FROM customers
WHERE name = 'John Doe'
LIMIT 1
*/
$customer = $customerModel->where('name', 'John Doe')->first();

Carrying out Create, Updates and Deletes

Creating a record - make THIS from THAT

/*
INSERT INTO customers (name, address)
VALUES ('Mike Sparks', '103a Chippy Row')
*/
$customer = $customerModel->create(['name' => 'Mike Sparks', 'address' => '103a Chippy Row']);

Performing an update - change THIS to THAT

/*
UPDATE customers
SET name = 'John Smith'
WHERE name = 'John Doe'
*/
$customer = $customerModel->where('name', 'John Doe')->update(['name' => 'John Smith']);

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'updated_at' in 'field list'


Laravel and Elequoent have a certain way of doing things when running at their default settings. When carrying out create and updates for example, it is expected that you will have a column named 'created_at' and a column named 'updated_at' with a type of DATE. You can disable this behaviour by adding

public $timestamps = false;
to your model.

I tend to use this functionality where it makes sense as it can be very useful. It's also very easy to use soft deletes via the addition of a 'deleted_at' column, but I'll cover that another time.

Performing a deletion - remove THIS if THAT

/*
DELETE
FROM customers
WHERE name = 'John Doe'
*/
$customer = $customerModel->where('name', 'John Doe')->delete();;

Important Notice Thing! - Mass Assigmnents with Elequoent


The following commands all have the ability to change multiple attributes to your models. Laravel and Elequoent provide a good way of deciding exactly what should and sholdn't be changeable.

To allow models to be mass updated you'll need to specify the columns which can be changed. You do this from within the model you are attempting to update. Adding the following code to our Customer model would allow our 'name' and 'address' columns to be mass updated:

protected $fillable = ['name', 'address'];

If this exists, get it. If not, make it! - I like this function. Searches for the given criteria and if it doesn't exist, creates it!

$customer = $customerModel->firstOrCreate(['name' => 'Penny Pound', 'address' => '76 Upside Downs']);

If this exists, update this to that. Otherwise make it. - Similar to the previous function but updates if it exists, creates if it doesn't!

$customer = $customerModel->createOrUpdate(['name' => 'Penny Pound', 'address' => '76 Downside Ups']);

Joining tables with Elequoent

Even with defining your relationships correctly, sometimes a good old JOIN is what's required. You can still achieve this with Elequoent.

/*
SELECT orders.id as order_id, products.id as product_id, products.description
FROM products
LEFT JOIN orders_product
ON orders.id = orders_product.order_id
LEFT JOIN products
ON products.id = orders_product.product_id
*/
$customer = $orderModel->select
    (
        'orders.id as order_id',
        'products.id as product_id',
        'products.description'
    )
    ->leftJoin('orders_product', 'orders.id', '=', 'orders_product.order_id')
    ->leftJoin('products', 'products.id', '=', 'orders_product.product_id')
    ->get();

You can carry out inner and right joins in the same fashion.

And with that, we're done. Hopefully you learned something and I didn't send you to sleep. Best of look with any future relationships you may build with a model ;)