Selecting carefully with Laravel joins

Laravel comes packaged with an ORM called Eloquent, which is one of the better PHP tools for dealing with your database layer using objects. It provides all of the features you’d expect from a modern relational mapper, and cleanly uses some PHP magic to make the process pleasant.

But it has its quirks, and today i’m setting my sights on a not-uncommon operation – the join.

The tables

Let’s say we have two tables: users and posts. users has an id and a name. posts has an id, name, content and user_id (a foreign key to the users table). Each table has the following data

users 
id | name 
1  | jp 
2  | cosmo

posts 
id | name         | content      | user_id 
18 | jp's post    | hello there! | 1 
22 | cosmo's post | hi there!    | 2

Here are their definitions in Eloquent

class User extends Eloquent {
  protected $fillable = ['name'];
  public function posts()
  {
    return $this->hasMany('Post');
  }
}

class Post extends Eloquent {
  protected $fillable = ['name', 'content', 'user_id'];
}

With this definition, we can retrieve the first user easily, and the results are what we’d expect

$user = User::first();
print $user->id;   //prints 1
print $user->name; //prints "jp"

Where it gets tricky

Retrieving a User object directly is nice, but many times when dealing with the database you’ll be issuing joins to retrieve your data. A join is fairly simple in Eloquent

$user = 
  User
    ::join('posts', 'posts.user_id', '=', 'users.id')
    ->where('posts.name', 'LIKE', '%jp%')
    ->first(); 

The join takes a table name, and then the pieces of the join clause. In this case we’re joining on the relationship between the posts.user_id and the users.id. A little bit clunky, but usable.

What isn’t usable, are the results.

print $user->id;   //prints 18
print $user->name; //prints "jp's post"

The object looks the same, but we’re getting what seems to be invalid results. When we ran it before, we printed an id of 1 and a name of “jp” – now we’re seeing an id of 18, which isn’t even available in the users table at all. And the name “jp’s post” is the from the posts table. What’s going wrong?

Under the hood

By default when Eloquent issues a query, it selects all columns

User::all(); //SELECT * FROM users

When you perform a join, it continues issuing the same select call, additionally adding the join and where clauses. So our join from earlier results in the following query

SELECT * 
FROM users 
INNER JOIN posts ON users.id = posts.user_id 
WHERE posts.name LIKE '%jp%' 

The result is a row with two id and name columns. Raw sample output from a database tool would look something like this

id | name | id | name      | content      | user_id
1  | jp   | 18 | jp's post | hello there! | 1

This means that when Eloquent parses the results into the User object, it’s contending with two versions of the same column name. Most likely it’s using a hash internally, and the second key overrides the first. The result is a User object with the id and name of the related Post.

Fixing the issue

The solution turns out to be very simple. You have to be specific with your select clause.


$user = 
  User
    ::join('posts', 'posts.user_id', '=', 'users.id')
    ->where('posts.name', 'LIKE', '%jp%')
    ->select('users.*')
    ->first(); 

Now our select is specifically selecting columns from the users table only, and our results are consistent with our expectations again

print $user->id;   //prints 1
print $user->name; //prints "jp"

How Rails does it

One reason this tripped me up in the first place, is coming to Laravel from Ruby on Rails. In Ruby on Rails, this problem is taken care of for you.

User.joins(:posts).where(name: '%jp%').first
SELECT users.* 
FROM users 
INNER JOIN posts ON users.id = posts.user_id
WHERE name LIKE '%jp%'

I assumed a similar behavior in Laravel without ever checking the results. Personally I think the Rails behavior is the more sensible default, since it keeps your class mapping directly to your table (unless you explicitly tell it otherwise using #select).

Why not use #with?

For people used to using eloquent, it may be suggested to instead use the #with method. Using #with does technically fix the select issue, but the use case it solves and queries it generates aren’t the same.

#with allows you to work around the N + 1 problem and also can give some form of filtering. To achieve a similar affect to the join clause using #with

User::with(['posts' => function ($query)
{
  $query->where('name', 'LIKE', '%jp%');
}])->first();

Using #with you might expect a similar result to the join, but the queries generated work differently.

SELECT * FROM users
SELECT * FROM posts WHERE posts.user_id IN (1) AND name LIKE '%jp%'

#with generates two queries, so if you’re trying to perform an operation on the data with one call, it’s not the way to go. It first fully retrieves the users table information, then uses that to construct a query on the posts table. It’s less efficient and may not always be accurate.

Also – it’s up for debate, but I find that adding a where clause to a #with method is more complicated to read.

Conclusion

The way joins work in Eloquent has bitten me a few times, and it teaches you to be careful about making assumptions when moving around between frameworks and abstraction layers. Laravel feels right at home when you’re used to using other systems like Rails, but truly understanding the framework you’re using and implications its tools have is important to making sure mistakes like this don’t slip through.

Prototyping quickly with the PHP built-in server

Sometimes you want to get a server up and running as quickly as humanly possible. Whether it’s trying out a js library where the demos only work with a server, developing a static front-end (stand-alone or for integration with an API) or getting a PHP app up and running without setting up apache or nginx – being able to quickly start a local server is a nice convenience. Here’s one of my goto approaches for getting up and running quickly.

PHP Built-in Server

Since the release and general use/availability of PHP 5.4, there’s been a built-in PHP server available from the PHP CLI. To run it, from the terminal/command line all you need to type is

and boom, you’re done! You can now access anything in that directory or subdirectories as you would from a normal web server. Naturally, getting to this point has two main pre-requisites – your PHP version must be 5.4+ (php -v), and PHP must be on your path.

Command Explained

Breaking it down, this is taking advantage of the (very not-production ready) built-in server that PHP 5.4+ offers. If you were to type

in the terminal, one of the instructions provided would be

This specifies the structure of starting the built-in PHP web server. In reference to our example: the -S specifies that you’re trying to start the built-in server, localhost is the address and 8000 is the port. We’re not using the -t docroot option here, but when specified you could use it to create a routing script (for more information on that, see the PHP docs here).

Accessing the server remotely

If you want to access the web server from a remote machine (useful for trying it on your phone, or letting other people on your network access it using your IP), you can specify the address as 0.0.0.0 instead of localhost. For example

More Examples

For more one-line server starters, there’s a helpful Github gist here. It doesn’t provide any specifics or explanations of the examples, but it gives examples in multiple languages including node.js, ruby, and python.

Knowledge Roundup: Week of 12/14

This is just a dumping ground for articles/books/tools that stuck out to me over the last week.

Most of the articles I read come from some excellent newsletter sources: Javascript Weekly, HTML5 Weekly, Ruby Weekly, Node Weekly, Database Weekly, and the Code Project Newsletter. Books are from a variety of sources, including Achieve Your Goals podcast, Entrepreneur on Fire and the Ameer Rosic Show (among many others and random internet discoveries).

Articles

http://arstechnica.com/security/2014/12/critical-git-bug-allows-malicious-code-execution-on-client-machines/

The official git clients for Mac and Windows are vulnerable to an attack. Time to update. If you’re using homebrew, this article will help – http://artarmstrong.com/blog/2014/12/18/install-and-update-to-git-2-2-1-on-mac-osx-10-10-yosemite-using-homebrew/

Read More