Article

Sometimes non-coders think programmers work magic. We know we don’t. But sometimes we think other programmers do.

Not real magic, of course. Programmer magic. Code that appears very simple in use, but performs complex tasks behind the scenes.

At Foster Made, we often use the Laravel PHP framework when building web applications, and Laravel does a lot of magic. Its magic is probably best demonstrated in how it accesses data from a database. Instead of having to manually write SQL to retrieve information, Laravel¹ does it for you. So the developer simply tells Laravel what he or she is looking for, and it writes the SQL, queries the database, and processes the results. Magic.

The only problem is sometimes we developers become so used to this magic handling requests elegantly, that we assume it is doing more work than it really is. Recently, we encountered a bug that resulted from an over reliance on magic.

For the sake of simplicity and interest, below is an illustration of an imaginary Laravel application to explain the issue and solution.

Imagine, if you would, that you are the headmaster of certain school of Witchcraft and Wizardry. You want the names and birthdays of all students 17 years old, so you can get them a birthday gift in their final year. Additionally, you would want to know which of these students is enrolled in the Potions class because you wish to gift them a rare potion ingredient.

Surprisingly, the school has integrated ideas from the non-magical world and built a system using Laravel to help manage some of the school’s operations. In it is kept information on all of the professors, courses, students, houses, ghosts, etc. You use the system to retrieve the list of students you need and, oddly, the system suggests that none of them are taking Potions—a thing you know to be untrue.

The database, as you understand, has a table titled PERSONS with generic information on all the students, faculty, and resident ghosts that includes a unique number to identify each. It also has a table titled COURSES, with information on each course, also including a unique number to identify them. When a student enrolls in a course, their identifier is added to another table titled ENROLLMENTS that lists their name alongside the identifier of the course² and a little more information like their grade and the semester they enrolled in; for every course a student registers in, another record gets added.

Taking a look at the code, you see the request looks like this:

$persons = Person::where("age", 17) ->where('is_student', true) ->with([ 'enrollments' => function ($query) { $query->join('courses', 'courses.id', '=', 'enrollments.course_id') ->where('courses.name' = 'Potions'); } ]);

And the SQL Laravel then initially builds from that looks like this:

SELECT * FROM `persons` WHERE `persons`.`age` = 17 AND is_student;

And once Laravel retrieves the identifiers of all these students from that SQL query, it automatically looks for the students enrolled in Potions using (the (?) being the list of student identifiers):

SELECT * FROM `enrollments`JOIN `courses` ON (`courses`.id = `enrollments`.`course_id`)WHERE `enrollments`.`person_id` in (?)AND `courses`.`name` = 'Potions';

You, the multitalented headmaster, are able to run these queries on the database directly, and discover they are returning that data you expected. Logically, the issue must be happening somewhere in the Laravel magic.

After a little digging, you discover that the issue is occurring when Laravel attempts to associate the students from the PERSONS query back to the results from the ENROLLMENTS query. Laravel does this by matching up each person on the PERSONS result to that person's identifier on the ENROLLMENTS result. The ENROLLMENTS table stores this identifier in a column titled ‘person_id’. However, the COURSES table also has a column titled ‘person_id’ which is a reference to the course’s professor. This is where the problem begins.

As it is written, the SQL query will retrieve both of these values from the database. When Laravel gets the information back, it keeps only one of the two ‘person_id’ values—specifically, the last it processes. Because the professor’s person identifier always comes last, Laravel keeps that instead of the students'. When Laravel then tries to match each student to a record of enrollment, it can’t because it can’t find the student’s identifier in the list of enrollments. This makes it appear as though no student is taking Potions.

It was assumed in the original Laravel query that the ‘magic’ behind Laravel would know that it needed to keep the ‘person_id‘ from the ENROLLMENTS table. The solution, you quickly see, is for Laravel to only retrieve the first foreign key (or nothing from the COURSES table):

$persons = Person::where("age", 17) ->where('is_student', true) ->with([ 'enrollments' => function ($query) { $query->join('courses', 'courses.id', '=', 'enrollments.course_id') ->select('enrollments.*') ->where('courses.name' = 'Potions'); } ]);

Situations similar to this are familiar to developers, occasionally with inelegant workarounds. The moral of the story above isn’t that we can’t trust frameworks and other third-party code to act as we suspect them to—that’s why we use them. But no code is infallible and no third-party code is tailored to one particular application, so these situations will always arise. Delving into what makes this magic work to understand its limitations and caveats may be time-consuming, but the knowledge enables us to build better applications for our clients.

1 Laravel is using Eloquent ORM as the system to handling the interaction with the database. Laravel is being used as an umbrella term to reduce jargon in the article. 2 This, of course, would be done with ids in the real world.

Technical Summary: When using Laravel’s Eloquent to join two tables in the constraints of an eager loaded query, be sure to use the select method in the query builder to get the proper foreign key on the eager loaded table if the table it is joining with has a column with the same name (and possibly different value). Eloquent does not magically do this for you. It will overwrite any duplicate column values the query retrieves. After retrieving the data, when it attempts to associate the eager loaded models with the relation that is loading them, it will use the column value from the joined table as the foreign key. This assigns the relations incorrectly, with likely false results.