CODEWITHSUNDEEP
phplaravellaravel-5
user45623
user45623

Reputation: 621

Emulating eager loading with query

Let's say we have the tables dog and breed with corresponding models:

class Dog extends Model {
    protected $table = 'dogs';
    protected $fillable = [
        'name', 'breed_id'
    ];
    protected $hidden = [ 'breed_id' ];

    public function breed() {
        return $this->belongsTo(Breed::class, 'breed_id');
    }
}

and 

class Breed extends Model {
    protected $table = 'breeds';
    protected $fillable = [
        'name'
    ];
}

We can eager load and access the breed like this: 

$dogs = Dog::with('breed')->all();

If we have an API function return this collection of dogs, by default it will format them as JSON, and each dog object will have a 'breed' object:

{"dogs":[
    {
      "id": 1,
      "name": "Rover",
      "breed": {
                 "id": 1,
                 "name": "Golden Retriever"
               }
    }
    //etc

Now let's say we need to load dogs using a raw query (this is a simple example, please pretend there's a good reason we're using a raw query). We can grab the breed name using a JOIN:

$query = "SELECT dogs.*, breeds.name AS breed_name
          FROM dogs
          JOIN breeds ON dogs.breed_id = breeds.id";
$dogs = DB::select(DB::raw($query));

However, if we return this as JSON, it will have a different structure:

{"dogs":[
    {
      "id": 1,
      "name": "Rover",
      "breed_id": 1,
      "breed_name": "Golden Retriever"
    }

Is there any way to give our raw query result the same formatting as our Eloquent model result without having to do this?

foreach ($dogs as $dog) {
    $dog['breed'] = array(
        'id' = $dog['breed_id'], 
        'name' => $dog['breed_name']
    );
    unset($dog['breed_id']);
    unset($dog['breed_name']);
}
return $dogs;

I am asking because I don't want the client application to have to parse the result in two different ways depending on whether the particular API function is using Eloquent with eager loading, or using raw queries.

Upvotes: 4

Views: 505

Answers (1)

Scopey
Scopey

Reputation: 6319

Even with databases with very good JSON support, returning exactly what you want is not really that feasible.

AFAIK MySQL doesn't support arrays, but in Postgres you can select the breed information in an array, but even then you can't key the elements of the array.

You can select everything as JSON: 

-- Postgres Example
SELECT row_to_json(row(dogs.*, ARRAY[breeds.name, breeds.id])) FROM ...

But again, keying the values is way more effort than it would be to just implement the desired functionality in code rather than SQL.

Upvotes: 2

Related Questions