Eloquent makes a lot of requests

Question

I just started playing with Laravel 4 and Eloquent. I have a blog table and many other related tables:

blog <- main info about the blog record blog_lang <- translations for each blog record blog_categories <- name speaks for itself blog_categories_lang <- translations for blog categories titles blog_to_categories <- pivot table between blog and blog_categories blog hasMany blog_lang. blog_categories hasMany blog_categories_lang blog belongsToMany blog_categories 

I want to show the following information in one grid: blog_id , blog_title , username and all categories:

 $data['blogs'] = Blog::with(array( 'translations' => function ($q) { $q->where('lang_id', '=', 1); }, 'user', 'categories', 'categories.translations' => function ($q) { $q->where('lang_id', '=', 1); } ))->get(); 

Performs 5 queries ... aren't there too many? Would it be better to use Fluent and join all of these tables with 1 big query?

Answer 1

Eloquent, making many small indexed queries, is much better than doing one large query for a wide variety of reasons:

• MySQL does not have to load multiple tables into temporary memory for each query and can reuse them between queries
• SQL optimizer will work faster with every query
• It allows you to cache your results without throwing JOIN (and other similar offers) from your data, which simplifies caching

You don't really notice this, but the path used by the SQL optimizer is the same between the following queries:

 SELECT a.*, b.* FROM a INNER JOIN b ON (a.id=b.id) WHERE a.id = 1 SELECT a.*, b.* FROM a, b WHERE a.id = b.id AND a.id = 1 

Both of them will force the SQL optimizer to execute these queries under the hood:

 SELECT a.* WHERE a.id = 1 SELECT b.* WHERE b.id = 1 

And from there, depending on your indexes, the SQL optimizer will perform a match based on either individuals or the full table data. What does Eloquent do? These are the two questions. You don’t get anything for one big request - in fact, you lose the ability to reuse data. In all, prefer small, optimized, reusable, cached queries to bulky operators.