#whendi

The previous test for the list of employees did not include user data. This was naive as a more normal use case would involve listing data from the related user records when listing employees. So now I rewrote the test to include user data.

/** @test */ function it_can_list_employees() {    [$business, $users] = $this->createBusinessWithEmployees();    DB::enableQueryLog();    $listOfEmployees = $business->listEmployees();    $this->assertEquals($users->count(), $listOfEmployees->count());    $employee1 = Employee::where('user_id', $users[0]->id)        ->where('business_id', $business->id)        ->first();    $this->assertTrue($listOfEmployees->contains($employee1));    $listOfUsers = $listOfEmployees->map(function($employee, $key) {        return $employee->user;    });    $this->assertTrue($listOfUsers->contains($users[0]));    dd(DB::getQueryLog()); }

This adds $listOfUsers to the test, using the map function to pull out the user from each employee in the $listOfEmployees collection. Then it asserts that one of the users created from the factory appears in the collection.

The test passes as expected because the user belongsTo relation is already in place. Looking at the query log, there was a separate query for each call to $employee->user in the map function, an example of the N+1 problem.

public function listEmployees() {    return $this->employees()->with('user')->get(); }

There are many reasons to look at the generated SQL. Maybe you’re concerned about the N+1 problem. Maybe you want to be sure you’re joining the right tables. Maybe the results just aren’t what you expect. Happily, it’s easy enough to peek under the hood and get a report on what actual SQL statements were created when you formed a query using Eloquent.

Here’s an example of putting a couple extra calls in a test to examine the generated SQL.

/** @test */ function it_can_list_employees() {    [$business, $users] = $this->createBusinessWithEmployees();    DB::enableQueryLog();    $listOfEmployees = $business->listEmployees();    $this->assertEquals($users->count(), $listOfEmployees->count());    $employee = Employee::where('user_id', $users[0]->id)        ->where('business_id', $business->id)        ->first();    $this->assertTrue($listOfEmployees->contains($employee));    dd(DB::getQueryLog()); }

There are two commands. “DB::enableQueryLog()” turns on logging of all queries to the database. This goes in right before the queries you want to examine. The second one is “DB::getQueryLog()” which returns an array of the queries and their passed parameters. Here, I put it in a dd (dump and die). Alternately you may want to pass it to a logger system or a file. Typically you don’t put this kind of logging in production code, but for tests it’s excellent.

.........array:2 [ 0 => array:3 [ "query" => "select * from "employees" where "employees"."business_id" = ? and "employees"."business_id" is not null" "bindings" => array:1 [ 0 => 3 ] "time" => 0.06 ] 1 => array:3 [ "query" => "select * from "employees" where "user_id" = ? and "business_id" = ? limit 1" "bindings" => array:2 [ 0 => 8 1 => 3 ] "time" => 0.03 ] ]

In the output, you can see each result is an element of an array. Each result is another array, this one with three elements, “query”, “bindings”, and “time”. The query is the generated SQL. The bindings are the passed parameters that go in where the question mark symbols are. Eloquent passes values by bindings instead of constructing SQL in order to help prevent SQL injection attacks. Finally, the time is how long the query took to process, in seconds.

I have a method to list a business’s employees, and another to add an employee. I still need the show, update, and delete methods. The first two methods were made on the Business model, as they are taken from the point of view of the business.

The showEmployee() method needs to display data about the Employee record and also the related User record. But for that matter, the listEmployees record will need information from the User records as well. Imagine if an owner wants to display a list of their employees. Clearly, that will need to include their first and last names.

Currently, listEmployees returns a collection of Employee records. From there I could also fetch data for each related User based on the Employee’s user_id. There’s a problem with this: displaying the list of employees takes 1 query to fetch the employees and N queries to get users, for a total of N+1 queries.

The N+1 problem is very common when using an ORM (object-relational mapper) because it hides the real SQL logic from you. So a developer might think they are fetching all of their data in one query, not realizing their error.

Database queries are amongst the slowest parts of an application. Sometimes they are even slower that API calls out to an Internet-based service. This is because they frequently have to perform physical I/O operations on storage, which is many times slower than accessing RAM. This problem can be hidden through technology for a while, by using things like solid-state drives and databases with large caches. Eventually, though, the performance penalty will be felt. It’s unavoidable.

The addEmployee() method is handy, but probably should only be used by the business owner. This calls for a method for detecting that the user is the business owner. I opted to put an isOwner() method Business.

First I created a test for isOwner. It seemed easy enough to combine the positive and negative case into one test.

/** @test */ function it_can_tell_if_a_user_is_an_owner() {   $user = factory(User::class, 2)->create();   $business = factory(Business::class)->create(['owner_id' => $user[0]->id]);   $this->assertFalse($business->isOwner($user[1]));   $this->assertTrue($business->isOwner($user[0])); }

Here I created two users from the factory and set one of them to be the owner of a business. So isOwner() should return false for one and true for the other.

The implementation is trivial.

public function isOwner(User $user) {    return $this->owner_id == $user->id; }

So I decided to expand the test to cover the case where there is nothing passed to isOwner(). In that case, I expected it to compare against the logged-in user.

/** @test */ function it_can_tell_if_a_user_is_an_owner() {   $user = factory(User::class, 2)->create();   $business = factory(Business::class)->create(['owner_id' => $user[0]->id]);   $this->assertFalse($business->isOwner($user[1]));   $this->assertTrue($business->isOwner($user[0]));   $this->actingAs($user[1]);   $this->assertFalse($business->isOwner());   $this->actingAs($user[0]);   $this->assertTrue($business->isOwner()); }

To tell PHPUnit that I want to be acting as another user, I used “$this->actingAs($user);”. This is really handy for spoofing various roles for the purpose of testing.

One question at this point is whether I should still keep the version where I pass $user as an argument to isOwner(). I decided to keep it because it is possible that in some contexts I may have scripts that check that permission without being logged in as the owner.

Again, the implementation is not hard.

public function isOwner($user = null) {    if(null == $user)    {        return $this->owner_id == Auth::id();    }    return $this->owner_id == $user->id; }

This also required a “use Auth;” at the top. This works, but now I have two different comparisons happening here, and I feel it would be cleaner to have just one. So I refactored it a little, backed up by the confidence that comes with having tests.

public function isOwner($user = null) {    $user_id = (null == $user) ? Auth::id() : $user->id;    return $this->owner_id == $user_id; }

When I return to the controller and screens, there will be a need to list a business’s employees, add an employee, and remove an employee. Today I’m looking at the list. I started with a test.

/** @test */ function it_can_list_employees() {    $business = factory(Business::class)->create();    $users = factory(User::class, 3)->create();    foreach($users as $user)    {        $business->addEmployee($user);    }    $listOfEmployees = $business->listEmployees();    $this->assertEquals(3, $listOfEmployees->count());    $employee = Employee::where('user_id', $users[0]->id)        ->where('business_id', $business->id)        ->first();    $this->assertTrue($listOfEmployees->contains($employee)); }

It’s a bit complicated and I’ll refactor it later. But first I want it to pass.

public function listEmployees() {    return $this->employees()->get(); }

Simple enough, it gets the employees and makes a collection from the relation. This can be changed later to include modifiers such as active employees or employees of a certain type.

Now the bit where I create a business with three users and add them as employees is found in several tests. So extracted that out to its own method. I had it return $business and $users so they could be used as part of the tests.

protected function createBusinessWithEmployees() {    $business = factory(Business::class)->create();    $users = factory(User::class, 3)->create();    foreach($users as $user)    {        $business->addEmployee($user);    }    return [$business, $users]; }

Then I updated the tests to call this method instead of running the factories themselves.

/** @test */ function it_can_list_employees() {    [$business, $users] = $this->createBusinessWithEmployees();    $listOfEmployees = $business->listEmployees();    $this->assertEquals($users->count(), $listOfEmployees->count());    $employee = Employee::where('user_id', $users[0]->id)        ->where('business_id', $business->id)        ->first();    $this->assertTrue($listOfEmployees->contains($employee)); }

Now that addEmployee() is available, I moved on to the test for hasEmployee(). This replaced much of the earlier setup with simpler code.

/** @test */ function a_null_is_not_an_employee_in_a_business_with_employees() {    $nullUser = null;    $business = factory(Business::class)->create();    $users = factory(User::class, 3)->create();    foreach($users as $user)    {        $business->addEmployee($user);    }    $this->assertFalse($business->hasEmployee($nullUser)); }

And the code to satisfy that is simple enough.

public function hasEmployee($user = null) {    if (null == $user)        return false; }

Now, what if I use a real user?

/** @test */ function a_user_is_not_an_employee() {    $nonemployeeUser = factory(User::class)->create();    $business = factory(Business::class)->create();    $users = factory(User::class, 3)->create();    foreach($users as $user)    {        $business->addEmployee($user);    }    $this->assertFalse($business->hasEmployee($nonemployeeUser)); }

This led to the following code.

public function hasEmployee(User $user = null) {    if (null == $user)        return false;    $result = Employee::where("user_id", $user->id)        ->where("business_id", $this->id)        ->exists();    return $result; }

Finally, I needed a test for a user that was also an employee.

/** @test */ function a_user_is_an_employee() {    $business = factory(Business::class)->create();    $users = factory(User::class, 3)->create();    foreach($users as $user)    {        $business->addEmployee($user);    }    $this->assertTrue($business->hasEmployee($users[0])); }

And this test passed with the existing code.

So while it was a bit slow getting the testing factories set up and getting familiar with how to set up some tests, once these things were in place the testing and coding was greatly accelerated. Plus, the coding took place with a lot more confidence. Testing this way was also much faster than going through a browser, creating users, and testing features by hand.

A business can have several users as employees; a user can be employed by multiple companies. Sounds like a many-to-many relationship, calling for the pivot table business_users, right?

But wait. A business can have several users as clients; a user can be a client of multiple companies. Again, a many-to-many relationship, calling for the pivot table business_users... uh oh.

Well, maybe we can rescue this with a business_user type field, designating whether the user is an “employee” or a “client”. That might work... but then what if a user is not just an employee, he’s also a client? The business user type can be made part of the composite key. Other things can be done to handle other cases.

I feel this is the wrong direction to go in. An Employee and a Client are separate concepts, have different responsibilities, and have different attributes. It makes a lot more sense for these to be separate models. And if they’re going to be separate models, then they should be separate tables. Therefore, I decided to opt against using pivot tables.

For my next test, I need a business to be able to add an employee.

/** @test */ function it_can_add_an_employee() {   $business = factory(Business::class)->create();   $user = factory(User::class)->create();   $business->addEmployee($user);   $foundEmployee = Employee::where('business_id', $business->id)       ->where('user_id', $user->id)       ->exists();   $this->assertTrue($foundEmployee); }

I found this test to be a little unsettling. The arrangement and action are fine. A couple of basic factories are used, and the new addEmployee() method seems plain enough. It’s the assertion that bothers me. How do I know the user was properly added as an employee? This query examines the “employees” table and looks for a match. The query is nearly identical to the query that I was going to use in the hasEmployee() method. So is the test driving the code, or is the code driving the test? It’s a bit of a loop. I could replace the query with a call to hasEmployee(), but then I should have a separate test for just hasEmployee(), but that test would need to use addEmployee() to create the record in the “employees” table.

To break the loop I ignore hasEmployee() for now and let the query stand.

The first attempt to make the test pass is very straightforward, simply creating a new Employee() record and filling in the fields.

public function addEmployee(User $user) {    $employee = new Employee();    $employee->user_id = $user->id;    $employee->business_id = $this->id;    $employee->save(); }

From here, I refactor it to save the employee record through the hasMany relation.

public function addEmployee(User $user) {    $employee = new Employee();    $employee->user_id = $user->id;    $this->employees()->save($employee); }