Level 54
With a query that complex it would probably be more performant to just use the raw sql. But I'm no expert
1 like
Feb 20, 2015
9
Level 7
When not to use eloquent
For exporting data for reports in my web app from a database with about 25 tables, I need to run this query with many joins. The raw mysql query has about 20 left joins. And in the select I'm using many GROUP_CONCAT(DISTINCT ) statements like the one below, to put comma separated lists of items in one cell (to be used in the excel export file):
SELECT
GROUP_CONCAT(DISTINCT categories.`category` ORDER BY categories.`category` SEPARATOR ', ') AS `categories`,
GROUP_CONCAT(DISTINCT countries.`country` ORDER BY countries.`country` SEPARATOR ', ') AS `countries`,
// etc
FROM projects
LEFT JOIN tags
... etc
Trying to get this query working in eloquent, my code is becoming very long and messy. Like:
$rows = array();
foreach($projects as $project){
$row = array();
// Title
$row[] = $project->title;
// ... more
// project many to many categories. Have to loop through relation
$categories = array();
foreach($project->categories as $category) {
$category_name = $category->category;
if(!in_array($category_name, $categories)){
array_push($categories, $category_name);
}
}
$row[] = implode(';', $categories);
// .. more of the same
}
The complete code will probably be 100-200 lines long. The original mysql maybe 30-40 lines, depending on how you layout the sql.
So, should I just stick with raw mysql in this case, or am I doing something wrong with the eloquent methods above and could that be much more simplified?
Level 53
@Mattiman There is no reason for Eloquent in such case. It lets you work with rows as objects, and reports collecting data from multiple tables definitely don't benefit from it in any way.
So, create your raw query and run it just like that. Also, Eloquent is really heavy, and while it doesn't have any impact in most cases, if you load really big amount of models (and I suppose you will for the reports) it may affect the performance.
2 likes
Level 36
does that mean, that for the more compßlex queries, ie involving several tables (joins) etc, it might be better to use raw sql and for the simpler queries Eloquent does a good job?
Level 7
Hi @willvincent and @JarekTkaczyk thanks for your replies. I think I'll follow your advice and just use the raw sql here.
Next challenge is to get everything into an array.
$rows = DB::select(DB::raw($sql));
This returns an array of stdclass objects. After searching I have found a solution mentioned:
$rows = json_decode(json_encode($rows), true);
Would you say that that's the best way?
Level 53
@Mattiman Yup
1 like
Level 7
OK thanks! I'll go with that then :)
Level 47
@frezno it all depends on the query and how your going to consume it. Using Eloquent leaves a lot of database schemas not correctly set up. Meaning many times indexing and foreign keys aren't set, so a raw query (not utilizing Eloquent) might be just as fast as Eloquent. If properly set up and enough resources a raw query might run circles around Eloquent queries for large amounts of joins.
Level 53
@frezno I suggest this: when you need to work with your data in Active Record manner, ie. rows mapped to objects, then use Eloquent. When you need to output some complex data, like in the reports etc. then there is no reason for this - Eloquent is not helping you in this case.
// update user, post and comment - Eloquent makes it so easy:
$user = User::first();
$post = $user->posts()->find($postId);
$post->title = 'different title';
$comment = new Comment(['body' => 'This is awesome!']);
$post->comments()->save($comment);
$user->touch();
// but showing report from a few tables:
$report = SomeModel::join('whatever', ...)->leftJoin('something_else', ...)->...->get();
// now $report hold bunch of data from multiple tables but wrapped in
// Eloquent Collection of SomeModel objects - it doesn't make much sense
That's just a sensible example, I'm sure you get the idea.
2 likes
Level 36
got it - thank you @jekinney and @JarekTkaczyk
Please or to participate in this conversation.