Hi, I have a search page that gives you articles sorted by the likes they get. But the page loads very slow 10 seconds!
I know that the problem is with number of likes. In my view I show every post with number of likes they have. When I remove that and also remove The part where I include 'likes' the speed is OK. Is there anyway I can make it faster?
Here is the search method in my controller:
class ArticlesController extends Controller
{
public function index()
{
$categories = Category::with('subcategories')->get();
$subcategories = $categories->pluck('subcategories')->flatten();
$countries = Country::getAll();
$articles = (new Article)->newQuery();
$requestValues = [
'sortBy' => 're', //recent
'q' => '', // search query
'country' => 1, // country id
'cat' => '', // category
'sub' => '' // subcategory
];
$sorts = collect([
[
'name' => __('labels.recent'),
'code' => 're' // recent
],
[
'name' => __('labels.liked'),
'code' => 'li' // likes
],
]);
if (request()->has('sort') && $sorts->where('code', request('sort'))->first()) {
$requestValues['sortBy'] = request('sort');
}
if ($requestValues['sortBy'] === 'li') {
$articles->withCount('likes')->orderBy('likes_count', 'desc'); // this is also making it slow
} else {
$articles->orderBy('created_at', 'desc');
}
if (request()->has('country') && $countries->where('id', request('country'))->first()) {
$requestValues['country'] = request('country');
}
$articles->with(['blog', 'photos', 'likes']) // 'likes' makes it slow
->whereHas('blog', function ($query) use ($requestValues) {
$query->where('status', BlogStatus::APPROVED)
->where('country_id', $requestValues['country']);
})
->where(function ($query) {
$query->where('status', ArticleStatus::APPROVED)
->orWhere('status', ArticleStatus::PENDING);
});
if (request()->has('q') && !empty(request('q'))) {
$requestValues['q'] = request('q');
$articles->where(function ($query) use ($requestValues) {
$query->where('title_en', 'LIKE', '%' . $requestValues['q'] . '%')
->orWhere('title_ar', 'LIKE', '%' . $requestValues['q'] . '%')
->orWhere('body_en', 'LIKE', '%' . $requestValues['q'] . '%')
->orWhere('body_ar', 'LIKE', '%' . $requestValues['q'] . '%');
});
}
if (request()->has('sub') && !empty(request('sub'))) {
if ($requestValues['sub'] = $subcategories->where('id', request('sub'))->first()) {
$articles->where('subcategory_id', $requestValues['sub']->id);
$requestValues['sub'] = $requestValues['sub']['id'];
}
} else {
if (request()->has('cat') && !empty(request('cat'))) {
if ($requestValues['cat'] = $categories->where('id', request('cat'))->first()) {
$subs = $requestValues['cat']->subcategories->pluck('id')->toArray();
$articles->whereIn('subcategory_id', $subs);
$requestValues['cat'] = $requestValues['cat']['id'];
}
}
}
$articles = $articles->simplePaginate(10);
return view('articles.index', compact('articles', 'categories', 'countries', 'subcategories', 'requestValues', 'sorts'));
}
}
Here is the article model
class Article extends Model
{
public function blog()
{
return $this->belongsTo(Blog::class);
}
public function photos()
{
return $this->hasMany(ArticlePhoto::class);
}
public function subcategory()
{
return $this->belongsTo(Subcategory::class);
}
public function likes()
{
return $this->morphMany(Like::class, 'liked');
}
}
here is the blog model
class Blog extends Model
{
public function articles()
{
return $this->hasMany(Article::class);
}
public function user()
{
return $this->belongsTo(User::class);
}
public function country()
{
return $this->belongsTo(Country::class);
}
}