I am new to Laravel and I am wondering if I should use Eloquent Query Builder or just raw SQL. When I am pulling data from MySql with Eloquent I get a lot of unnessesary stuff.
My approach with Eloquent Query Builder:
$query = Document::where('owner', $user_id);
$docs = $query->take(2)->get();
return $docs;
and the result I get:
object(Illuminate\Database\Eloquent\Collection)[191]
protected 'items' =>
array (size=2)
0 =>
object(Document)[189]
protected 'table' => string 'documents' (length=9)
protected 'connection' => null
protected 'primaryKey' => string 'id' (length=2)
protected 'perPage' => int 15
public 'incrementing' => boolean true
public 'timestamps' => boolean true
protected 'attributes' =>
array (size=12)
'id' => int 2
'title' => string 'test' (length=4)
'description' => null
'status' => int 0
'files' => null
'uid' => string 'gregdf' (length=6)
'path' => string '[]' (length=2)
'owner' => int 1
'workspace' => int 1
'flow' => int 1
'created_at' => string '2014-12-10 10:10:10' (length=19)
'updated_at' => string '0000-00-00 00:00:00' (length=19)
protected 'original' =>
array (size=12)
'id' => int 2
'title' => string 'test' (length=4)
'description' => null
'status' => int 0
'files' => null
'uid' => string 'gregdf' (length=6)
'path' => string '[]' (length=2)
'owner' => int 1
'workspace' => int 1
'flow' => int 1
'created_at' => string '2014-12-10 10:10:10' (length=19)
'updated_at' => string '0000-00-00 00:00:00' (length=19)
protected 'relations' =>
array (size=0)
empty
protected 'hidden' =>
array (size=0)
empty
protected 'visible' =>
array (size=0)
empty
protected 'appends' =>
array (size=0)
empty
protected 'fillable' =>
array (size=0)
empty
protected 'guarded' =>
array (size=1)
0 => string '*' (length=1)
protected 'dates' =>
array (size=0)
empty
protected 'touches' =>
array (size=0)
empty
protected 'observables' =>
array (size=0)
empty
protected 'with' =>
array (size=0)
empty
protected 'morphClass' => null
public 'exists' => boolean true
1 =>
object(Document)[187]
protected 'table' => string 'documents' (length=9)
protected 'connection' => null
protected 'primaryKey' => string 'id' (length=2)
protected 'perPage' => int 15
public 'incrementing' => boolean true
public 'timestamps' => boolean true
protected 'attributes' =>
array (size=12)
'id' => int 3
'title' => string 'test' (length=4)
'description' => null
'status' => int 0
'files' => null
'uid' => string 'gregdf' (length=6)
'path' => null
'owner' => int 1
'workspace' => int 1
'flow' => int 1
'created_at' => string '0000-00-00 00:00:00' (length=19)
'updated_at' => string '0000-00-00 00:00:00' (length=19)
protected 'original' =>
array (size=12)
'id' => int 3
'title' => string 'test' (length=4)
'description' => null
'status' => int 0
'files' => null
'uid' => string 'gregdf' (length=6)
'path' => null
'owner' => int 1
'workspace' => int 1
'flow' => int 1
'created_at' => string '0000-00-00 00:00:00' (length=19)
'updated_at' => string '0000-00-00 00:00:00' (length=19)
protected 'relations' =>
array (size=0)
empty
protected 'hidden' =>
array (size=0)
empty
protected 'visible' =>
array (size=0)
empty
protected 'appends' =>
array (size=0)
empty
protected 'fillable' =>
array (size=0)
empty
protected 'guarded' =>
array (size=1)
0 => string '*' (length=1)
protected 'dates' =>
array (size=0)
empty
protected 'touches' =>
array (size=0)
empty
protected 'observables' =>
array (size=0)
empty
protected 'with' =>
array (size=0)
empty
protected 'morphClass' => null
public 'exists' => boolean true
My approach with SQL:
$sqlArgs = array(
'workspace' => $args['workspace'],
'usr' => $user_id
);
$query = 'select d.title, d.description, d.created_at
from documents as d
inner join userDocuments as ud on ud.document = d.id
where ud.user = :usr and d.workspace = :workspace limit 0, 2 ';
$docs = DB::select($query, $sqlArgs);
return $docs;
and the result (this is what I really need):
array (size=2)
0 =>
object(stdClass)[185]
public 'title' => string 'test' (length=4)
public 'description' => null
public 'created_at' => string '2014-12-10 10:10:10' (length=19)
1 =>
object(stdClass)[189]
public 'title' => string 'test' (length=4)
public 'description' => null
public 'created_at' => string '0000-00-00 00:00:00' (length=19)
So which approach is better? Or maybe I can limit the amount of data in Eloquent approach somehow?
UPDATE: Both above examples are querying for different sets of records, but what is important is how many data is returnet for each record.
You can specify what fields you are not interested in.
class Car extends Eloquent {
protected $hidden = array('created_at');
}
You need to use Select for what you wanted column like the following
$query = Document::where('owner', '=', $user_id)->lists('title', 'description', 'created_at');
It's will got only title, description and created_at columns. For limiting eloquent query, you can use take with select like the following
$query = Document::where('owner', '=', $user_id)->lists('title', 'description', 'created_at')->take(5)->get();
It is more of a by design matter. Eloquent is an ORM (Read thoroughly about ORMs), that is it represents all records through objects and all records and their related records can be manipulated in a more object oriented way. You get to query the database and/or make its schema in PHP and not touch SQL at all. This way a plethora of abstractions can be placed on the DB.
To provide food for thought, one can through eloquent, manipulate the same DB differently in multiple deployments, set distinctly different profiles for a single table which may have their own primary keys and/or relationships.
Hand tailored SQL will always be faster than using any ORM. It really depends on what you judge as the better option.
Though the toArray() method is cleaner, I tend to avoid it. You can extract the field values of a single model by $fieldValues = $model->attributes.