Eloquent makes a lot of queries

Question

I just started playing with Laravel 4 and Eloquent. I have a blog table and lots of other related tables to it:

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 info 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();

This executes 5 queries... aren't they a little too many? Will it be better to just use Fluent and join all these tables with 1 bigger query?

Answer 1

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

• MySQL will not have to load multiple tables in temporary memory for each query, and may re-use them between queries
• The SQL optimizer will run more swiftly through each query
• It allows you to cache your results without having to throw out the JOINs (and other similar clauses) from your data, which makes caching easy

You're not actually noticing it, but the path taken 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 cause the SQL optimizer to perform these queries under-the-hood:

SELECT a.* WHERE a.id = 1

SELECT b.* WHERE b.id = 1

And from there, depending on your indices, the SQL optimizer will perform matching based on either the indices or the full table data. What is Eloquent doing? Exactly those two queries. You're not gaining anything by one big query - in fact, you are losing on data reusability. In everything, prefer small, optimized, re-usable, cacheable queries to bulky statements.