Looking for suggestions to reduce query count for index page

In this request I AM looking for alternative suggestions to what I am prompting in regard to the way I retrieve data.

I am trying to achieve an index page similar to SAMPLE

where I have pinned post at the top, then for each of the parent categories (there will be multiple child categories displayed for each parent) I want to display either the most popular or the newest. So I set up a system that allows switching between the two. These articles are also all filtered by Hobby. (ie baseball articles, gardening articles, etc.)

Currently, i managed to get the articles for each section to display (though im still working on the actual components for each section) However I end up with 29 queries. Although quick, I'm wondering if there is a better way to reduce these query counts.

So to recap, I need 1 query to determine the parent level categories, then for each category, i want the newest, and the most popular.

I also want articles that are pinned, all added to an articles collection.

I do still need to figure out what to do if at least 5 pinned articles are not present, or if no articles fit for a category.

At this point, I am wondering if it would be less resource intensive just to query all articles for a given topic, and filter at the view level. I'll assume caching might help also once I get the basic logic determined.

Current code:

 public function index(Request $request, Hobby $hobby = null)
    {
        $categories = Tag::where('tag_type', 12)->whereNull('parent_id')->get();

        $hobby_id = $hobby->id; // Assuming $hobby is the chosen hobby


        $articles = collect();
        // Get pinned articles
        $pinned = $hobby->hobbyArticles()
                        ->status('published')
                        ->ArticleType('article')
                        ->pinned()
                        ->orderBy('view_count', 'desc')
                        ->take(5)
                        ->get([
                            'id', 'title', 'slug', 'pinned', 'premium_cost',
                            'view_count', 'user_id', 'post_as', 'group_id',
                            'published_on',
                        ]);
        $articles->put('pinned', $pinned);
        foreach ($categories as $category) {
            // Get top 5 newest articles
            $newest = $hobby->hobbyArticles()
                            ->status('published')
                            ->ArticleType('article')
                            ->whereHas('categories',
                                function ($query) use ($category) {
                                    $query->where('parent_id', $category->id);
                                })
                            ->orderBy('published_on', 'desc')
                            ->take(6)
                            ->get([
                                'id', 'title', 'slug', 'pinned', 'premium_cost',
                                'view_count', 'user_id', 'post_as', 'group_id',
                                'published_on',
                            ]);

            // Get top 5 most popular articles
            $popular = $hobby->hobbyArticles()
                             ->status('published')
                             ->ArticleType('article')
                             ->whereHas('categories',
                                 function ($query) use ($category) {
                                     $query->where('parent_id', $category->id);
                                 })
                             ->orderBy('view_count', 'desc')
                             ->take(6)
                             ->get([
                                 'id', 'title', 'slug', 'pinned',
                                 'premium_cost',
                                 'view_count', 'user_id', 'post_as', 'group_id',
                                 'published_on',
                             ]);


            // Add the articles to the collection
            $articles->put($category->tag, [
                'newest'  => $newest,
                'popular' => $popular,
            ]);
        }

This creates a collection like this:

$articles => [pinned => [articles 1-5],

Category 1 => [newest=>[articles 1-5],
popular = [articles 1-5] ] Category 2 => [newest=>[articles 1-5],
popular = [articles 1-5] ] ... ]

and so forth.

It works, but not sure if its the best way of doing this as there might be thousands of articles to filter if not 10's or 100's of thousands. With my current categories, I should end up with a collection of 65 articles max.

Is there a more efficient way to make this query?

LaryAI

1 year ago

Level 58

To reduce the number of queries and improve the efficiency of your index page, you can leverage eager loading and more complex queries to fetch the necessary data in fewer queries. Here's a revised approach:

Eager Load Relationships: Use eager loading to reduce the number of queries when fetching related models.
Single Query for Articles: Fetch all necessary articles in a single query and then group them in PHP.

Here's a revised version of your code:

public function index(Request $request, Hobby $hobby = null)
{
    $categories = Tag::where('tag_type', 12)->whereNull('parent_id')->get();
    $hobby_id = $hobby->id; // Assuming $hobby is the chosen hobby

    // Get all necessary articles in a single query
    $articlesQuery = $hobby->hobbyArticles()
        ->status('published')
        ->ArticleType('article')
        ->with(['categories' => function ($query) {
            $query->whereNull('parent_id');
        }])
        ->get([
            'id', 'title', 'slug', 'pinned', 'premium_cost',
            'view_count', 'user_id', 'post_as', 'group_id',
            'published_on',
        ]);

    // Separate pinned articles
    $pinned = $articlesQuery->where('pinned', true)->sortByDesc('view_count')->take(5);

    // Initialize articles collection
    $articles = collect(['pinned' => $pinned]);

    foreach ($categories as $category) {
        // Filter articles by category
        $categoryArticles = $articlesQuery->filter(function ($article) use ($category) {
            return $article->categories->contains('id', $category->id);
        });

        // Get top 5 newest articles
        $newest = $categoryArticles->sortByDesc('published_on')->take(5);

        // Get top 5 most popular articles
        $popular = $categoryArticles->sortByDesc('view_count')->take(5);

        // Add the articles to the collection
        $articles->put($category->tag, [
            'newest' => $newest,
            'popular' => $popular,
        ]);
    }

    return view('your-view', compact('articles'));
}

Explanation:

Eager Loading: The with(['categories' => function ($query) { ... }]) part ensures that the categories are loaded with the articles in a single query.
Single Query for Articles: The $articlesQuery fetches all necessary articles in one go.
Filtering and Sorting in PHP: After fetching all articles, we filter and sort them in PHP, which reduces the number of database queries.

Additional Tips:

Caching: Implement caching to store the results of these queries, especially if the data doesn't change frequently. This will significantly reduce the load on your database.
Indexes: Ensure that your database tables are properly indexed, especially on columns used in WHERE, ORDER BY, and JOIN clauses.

By following this approach, you should be able to reduce the number of queries and improve the performance of your index page.

Please or to participate in this conversation.