Eloquent and SQL Views
I'm currently on a mission to learn ember.js. This is my first attempt at frontend development and ember seems to be the new hotness so I figured I'd give it a shot. I'm building the backend api with Laravel. Out of the box, Laravel is pretty well-suited for building an api. However, after learning what json responses ember expects, I realized there was some work to be done.
The dilemma
I was reading through the ember rest adapter guide and came across the model relationship expectations. Ember expects something like the following.
{
"company": {
"id": 1,
"name": "Acme Company",
"person_ids": [1, 2, 3]
}
}Notice how the relationship ids are contained in an array. Ember does support sideloading models like below.
{
"company": {
"id": 1,
"name": "Acme Company",
"person_ids": [1, 2, 3],
"people": [{
"id": 1,
"name": "David Adams"
},
{
"id": 2,
"name": "John Doe"
},
{
"id": 3,
"name": "Bob Smith"
}]
}
}
The problem with this is that if your app is of any size, this isn't an option. Too much data would be loaded.
Laravel is awesome in that you can side/eager load models but there isn't an option for eager loading data outside of entire models. For example, the person_ids in our company models.
My first attempt
The very last section in the eloquent docs informs us that we can define custom accessors that don't have a corresponding column in the database. I thought I would use this feature to accomplish my goal.
class Company extends \Eloquent
{
protected $table = 'companies';
protected $appends = ['person_ids'];
protected $person_ids;
public function people()
{
return $this->hasMany('Person');
}
public function getPersonIdsAttribute()
{
if (is_null($this->person_ids)) {
$ids = DB::table('people')->where('company_id', '=', $this->id)->lists('id');
$this->person_ids = array_map('intval', $ids);
}
return $this->person_ids;
}
}This accomplishes my goal....but there is one big problem. This solution has the N + 1 query problem. I'm adding a lot of database overhead with this approach. Every model retrieved will require one more query to get all of its data. This isn't optimal at all.
The solution
Rather than require a separate query made by php, it would be better to have sql handle that for us behind the scenes. It would be great to be able to use a sql view to read the data from instead of directly off of the table.
SELECT * FROM your_view_name.
I created a view with the following migration.
class CreateCompaniesView extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::statement("CREATE VIEW companiesView AS
SELECT *,
(
SELECT GROUP_CONCAT(DISTINCT id SEPARATOR ',')
FROM people AS p
WHERE p.company_id = c.id
) AS person_ids
FROM companies AS c");
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::statement("DROP VIEW companiesView");
}
}
This allows me to issue a query like SELECT * FROM companiesView and get the following resultset:
| id | created_at | updated_at | name | person_ids |
|---|---|---|---|---|
| 1 | 2013-11-10 06:29:48 | 2013-11-10 06:29:48 | Acme Company | 3,4 |
| 2 | 2013-11-10 06:29:48 | 2013-11-10 06:29:48 | FooBar Industries | 2,8,12 |
Now we need to figure out how to incorporate this view into our eloquent model.
Eloquent and views
We can't simply change the $table property on our model to the newly created view. Views cannot be written to, only read from. We need to provide the writable $table along with a $readFrom property where we can specify a view.
My first thought was to hook into the saving and saved eloquent model events to swap out the view for the real table when saving. I learned that the saving event isn't fired soon enough though. Eloquent has already built part of the query at that point.
Rather than using events, I decided to wrap eloquent with my own BaseModel class. This allows me to override the save() method and juggle the table/view before and after the actual save.
public function save(array $options = [])
{
$this->toWriteMode();
try {
$saved = parent::save($options);
} catch (\Exception $e) {
$this->toReadMode();
throw $e;
}
$this->toReadMode();
return $saved;
}
The toWriteMode() and toReadMode() methods simply swap the $table to the appropriate table or view.
Read only attributes
The next problem is figuring out how to handle these read only attributes, ie. person_ids. These values need to be attributes on the models but not saved to the database.
We need a way to cache these read only values, remove them from being attributes on the model, save the model, and then add them back after the model is saved.
//part of the BaseModel class
protected $readOnly = [];
protected $readOnlyCache = [];
public function save(array $options = [])
{
$this->toWriteMode();
$this->cacheReadOnly();
try {
$saved = parent::save($options);
} catch (\Exception $e) {
$this->toReadMode();
throw $e;
}
$this->toReadMode();
$this->restoreReadOnly();
return $saved;
}
protected function cacheReadOnly()
{
$this->readOnlyCache = [];
foreach ($this->readOnly as $key) {
$value = $this->getAttributeValue($key);
$this->readOnlyCache[$key] = $value;
$this->__unset($key);
}
}
protected function restoreReadOnly()
{
foreach ($this->readOnlyCache as $key => $value) {
$this->setAttribute($key, $value);
}
}
Sweet. Now in our model classes, we just need to provide an array of $readOnly attributes.
Putting it all together
With the creation of this base model class we can define a company model like below.
class Company extends BaseModel
{
protected $table = 'companies';
protected $fillable = ['name'];
protected $guarded = ['id'];
public function people()
{
return $this->hasMany('Person');
}
} There's absolutely nothing new in this Company class. It works just as you would expect. However, now we can extend this class to create an EagerCompany class.
class EagerCompany extends Company
{
protected $readFrom = 'companiesView';
protected $readOnly = ['person_ids'];
public function getPersonIdsAttribute($ids)
{
return $this->intArrayAttribute($ids);
}
} This class will read its data from the view we created earlier and we can save and retrieve it as normal. It will fetch our read only attributes and they will be handled appropriately when saving.
That new intArrayAttribute() method just converts the comma delimited id string returned from the view into an array of integers.
We have some options now. We can use the Company internally but if we need those extra read only attributes, say in an api response, we can use the EagerCompany class.
I found this to work quite well for getting a list of ids for relationships. I also plan to use this same idea for returning counts of relationships.
{
"company": {
"id": 1,
"name": "Acme Company",
"person_ids": [1, 2, 3],
"people_count": 3
}
}The base model
I've only shown you bits and pieces of this base model up until now. Here's the whole enchilada.
/**
* Base model for allowing reads from sql views
*
* Class BaseModel
* @package ProgrammingAreHard\Models
*/
abstract class BaseModel extends \Eloquent
{
/**
* Writable table
*
* @var string
*/
protected $writeTable;
/**
* Read table/view
*
* @var string
*/
protected $readFrom;
/**
* Read only attributes (not to be saved)
*
* @var array
*/
protected $readOnly = [];
/**
* Cache for read only attribute values
*
* @var array
*/
protected $readOnlyCache = [];
/**
* Instantiate and set the table
*
* @param array $attributes
*/
public function __construct(array $attributes = [])
{
parent::__construct($attributes);
$this->writeTable = $this->getTable();
$this->toReadMode();
}
/**
* Juggle the table and read only attributes
*
* @param array $options
* @return bool
* @throws \Exception
*/
public function save(array $options = [])
{
$this->toWriteMode();
$this->cacheReadOnly();
try {
$saved = parent::save($options);
} catch (\Exception $e) {
$this->toReadMode();
throw $e;
}
$this->toReadMode();
$this->restoreReadOnly();
return $saved;
}
/**
* Cache and remove read only attributes
*
* @return void
*/
protected function cacheReadOnly()
{
$this->readOnlyCache = [];
foreach ($this->readOnly as $key) {
$value = $this->getAttributeValue($key);
$this->readOnlyCache[$key] = $value;
$this->__unset($key);
}
}
/**
* Restore the cached read only attributes
*
* @return void
*/
protected function restoreReadOnly()
{
foreach ($this->readOnlyCache as $key => $value) {
$this->setAttribute($key, $value);
}
}
/**
* Get the writable table
*
* @return string
*/
public function getWriteTable()
{
return $this->writeTable;
}
/**
* Swap to the writable table
*
* @return void
*/
protected function toWriteMode()
{
$this->setTable($this->getWriteTable());
}
/**
* Get the readable table/view
*
* @return string
* @throws \Exception
*/
public function getReadFrom()
{
if (is_null($this->readFrom)) {
$this->readFrom = $this->getWriteTable();
}
return $this->readFrom;
}
/**
* Swap to the readable table/view
*
* @return void
*/
protected function toReadMode()
{
$this->setTable($this->getReadFrom());
}
/**
* Convert the value to an int array if needed
*
* @param string|array $value
* @return array
*/
protected function intArrayAttribute($value)
{
if (is_string($value) and strlen($value) > 0) {
$value = explode(',', $value);
$value = array_map('intval', $value);
}
return is_array($value) ? $value : [];
}
}The controller
Here's a very simplified version of my controller.
class CompaniesController extends \BaseController
{
/**
* Display a listing of companies.
*
* @return Response
*/
public function index()
{
$res = new stdClass;
$res->companies = EagerCompany::all()->toArray();
return Response::json($res);
}
/**
* Display a single company.
*
* @param int $id
* @return Response
*/
public function show($id)
{
$res = new stdClass;
$res->company = EagerCompany::find($id)->toArray();
return Response::json($res);
}
}I feel like this is a pretty good way to handle eager loading of non-model data. Maybe there's a better way to do this. I'd love to hear any ideas if you have some!
