laravel渴望加载以最小化查询 [英] laravel eager loading to minimize queries
问题描述
我正在将laravel与DDD模式一起使用,这是我的代码来为网站制作一些分析报告...
I am using laravel with DDD Pattern and here is my code to make some analytics report for website...
我来自存储库的代码...
//Abstract Entity
public function getFirstBy($key, $value, array $with = array())
{
return $this->repository->getFirstBy($key, $value, $with);
}
//Link Entity
$link = parent::getFirstBy('hash', $data['hash'], ['visits']);
//prepare report
$report = [];
//get dates difference
$date1 = new \DateTime($data['from']);
$date2 = new \DateTime($data['to']);
$date_diff = $date2->diff($date1)->format("%a");
$time_visits = $link->visits()->whereRaw('DATE(created_at) BETWEEN ? AND ?', array($date1, $date2))->groupBy('created_at')->orderBy('created_at', 'ASC')->get();
//Total Visits
$report['total_visits'] = $time_visits->count();
//country visits
$country_visits = $link->visits()->whereRaw('DATE(created_at) BETWEEN ? AND ?', array($date1, $date2))->orderBy('country_iso', 'ASC')->groupBy('country_iso')->get();
//referrers
$referer_visits = $link->visits()->whereRaw('DATE(created_at) BETWEEN ? AND ?', array($date1, $date2))->where('source', '!=', '')->orderBy('source', 'ASC')->groupBy('source')->get();
//browsers
$browser_visits = $link->visits()->whereRaw('DATE(created_at) BETWEEN ? AND ?', array($date1, $date2))->where('browser', '!=', '')->orderBy('browser', 'ASC')->groupBy('browser')->get();
//os
$os_visits = $link->visits()->whereRaw('DATE(created_at) BETWEEN ? AND ?', array($date1, $date2))->where('os', '!=', '')->orderBy('os', 'ASC')->groupBy('os')->get();
The code is working perfectly fine but executing 6 mysql queries which could be a big problem for larger usage...
**sql queries dump...**
select * from `link_visits` where `link_visits`.`link_id` = '4' and DATE(created_at) BETWEEN '2014-09-15 00:00:00' AND '2014-09-17 00:00:00' group by `created_at` order by `created_at` asc
select * from `link_visits` where `link_visits`.`link_id` = '4' and DATE(created_at) BETWEEN '2014-09-15 00:00:00' AND '2014-09-17 00:00:00' group by `country_iso` order by `country_iso` asc
select count(*) as total_visits, medium from `link_visits` where `link_visits`.`link_id` = '4' and DATE(created_at) BETWEEN '2014-09-15 00:00:00' AND '2014-09-17 00:00:00' group by `medium` order by `medium` asc
select * from `link_visits` where `link_visits`.`link_id` = '4' and DATE(created_at) BETWEEN '2014-09-15 00:00:00' AND '2014-09-17 00:00:00' and `source` != '' group by `source` order by `source` asc
select * from `link_visits` where `link_visits`.`link_id` = '4' and DATE(created_at) BETWEEN '2014-09-15 00:00:00' AND '2014-09-17 00:00:00' and `browser` != '' group by `browser` order by `browser` asc
select * from `link_visits` where `link_visits`.`link_id` = '4' and DATE(created_at) BETWEEN '2014-09-15 00:00:00' AND '2014-09-17 00:00:00' and `os` != '' group by `os` order by `os` asc
Please suggest some good ways to optimize it better. thanks, i really need advanced help with this.
推荐答案
您可以在ex的关系中使用子查询.
you can use sub query in the relations for ex.
假设$ link是您的模型对象
assuming $link is you model object
$link->with(['Visits'=>function($q){
$q->whereRaw('DATE(created_at) BETWEEN ? AND ?', array($date1,$date2))->where('browser', '!=', '')->orderBy('browser', 'ASC')->groupBy('browser');
}])->get();
内部查询将相应地过滤所有记录,并且内部查询仅适用于Visits模型
the inner query will filter all your records accordingly and the inner query will apply only in the Visits model
我的建议是先不进行任何过滤就获取所有记录,然后使用filter()方法将其过滤到集合对象中!因此它将仅使用1个查询进行访问以获取所有记录,然后仅使用filter()方法对其进行重新排列
my suggestion is first fetch all the records without any filteration and then filter them in the object of collection with filter() method! so it will use only 1 query for visit to fetch all records then you only rearrange them using filter() method
这篇关于laravel渴望加载以最小化查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!