How can I optimize repeated recipe queries for HomepageSections?

I think your performance is totally fine. Caching the DB results would be sufficient, using the flexible function would be event better.

If you still want to optimize even further, is it possible to set up relationships between home page sections and recipes? That way we can start eager loading.

Or you might think about getting all relevant recipes in a single DB query, than assign them to the home page sections.

// Retrieve all relevant recipes.
$recipes = Recipe::where(...)->get();

$homepageSections = HomepageSection::visible()
    ->ordered()
    ->get()
    ->each(function ($homePageSection) use ($recipes) {
        $homePageSection->setRelation('recipes', $recipes->where(...));
    });

@kevinbui @krisi_gjika @ghabe Need some advices here

The Problem

After implementing the recipe sections, I noticed the page generates 21 SQL queries (see debugbar screenshot

Current Implementation

Homepage Controller loads visible sections and maps them with recipes:

class HomeController extends Controller
{
    public function __invoke()
    {
        // Get visible & ordered by 'order' column sections
        $homepageSections = HomepageSection::visible()->ordered()->get();

        // Map homepageSections to get a new array of sections
        $sections = $homepageSections->map(function ($section) {
            return [
                'id' => $section->slug,
                'title' => $section->name,
                'recipes' => $section->getRecipes(),
                'visible' => $section->visible,
                'order' => $section->order,
            ];
        });

        return view('index', compact('sections'));
    }
}

HomepageSection.php (relevant method)

public function getRecipes(): Collection
{
    $query = Recipe::with(['dishCategory', 'cuisine'])
        ->select(['id', 'name', 'image', 'cuisine_id', 'dish_category_id']);

    return match($this->type) {
        'popular' => $query->popular()->limit($this->limit)->get(),
        'latest' => $query->latest()->limit($this->limit)->get(),
        'category' => $query->byCategory($this->category_slug)->limit($this->limit)->get(),
        default => collect(),
    };
}

Recipe.php (scope method):

    public function scopeByCategory(Builder $query, string $categorySlug): Builder
    {
        return $query->whereHas('dishCategory', function ($q) use ($categorySlug) {
            $q->where('slug', $categorySlug);
        });
    }

Key Observations

• Each of my 7 sections calls getRecipes() independently

• Despite using with('dishCategory'), Laravel makes:

  • Separate queries for each section's recipes

  • Duplicate category lookups (same categories queried multiple times)

• The whereHas in byCategory compounds the problem

What I've Tried

• Caching individual sections → Helps but feels hacky

• Eager loading in getRecipes() → Doesn't prevent query duplication

Conclusion

Honestly, I am not sure if there is a mistake at all, but 21 query for the home page looks like a potential place to implement some fixes, I can make a cache logic for those sections, but I still know that there is a lot of duplicated queries and I can't fix them.

Would be grateful for your help

Best regards

IMO, you could use the union query to get all the category_slug once, then from the data, you could use collection method to filter it.

Its look a bit messy, but could be a temporary solution before the categories getting more and more

e.g:

$homepageSections = HomepageSection::visible()->ordered()->get();
$query = Recipe::with('cuisine')->select(['id', 'name', 'image', 'cuisine_id', 'dish_category_id']);

// getting the popular
$popularRecipes =  $query->popular()->limit($limit)->get();
// getting the latest
$latestRecipes = $query->latest()->limit($limit)->get();

// Building the union query with the distinct category_slug
HomepageSection::query()->select('category')->distinct('category_slug')->where('type','category')->each(function(HomepageSection $homepageSection)use(&$query){
	$query = $query->union($query->withWhereHas('dishCategory', function ($q) use ($homepageSection) {
		$q->where('slug',$homepageSection->slug)->limit(4);
	});
});

$slugReceipes = $query->get();
$homepageSections->map(
// the rest of the codes
)