Advanced where queries using whereRaw() to count relations

You can try this (not sure if it works) :

1. Number of budget relationship (@JarekTkaczyk style see).

class Project extends Model {

    public function budget_count () {
        return $this->hasOne('App\Budget')
                ->selectRaw('project_id, count(*) as budget_count')
                ->groupBy('project_id');
    }

} 

2. Use whereHas to filter it

$projects = Project::whereHas(function ($query) use ($min_budgets) {

    $query->where('budget_count', '>=', $min_budgets);

})->get();

this code is returning thousands of Projects and I've found using Eloquent to be too slow

3. Use the chunk method instead of get (return slices of N eloquent models)

$query = Project::whereHas(function ($query) use ($min_budgets) {

    $query->where('budget_count', '>=', $min_budgets);

});

$query->chunk(100, function ($projects) {

    foreach ($projects as $project) {

        // Projects are retrieved 100 by 100 so no overhead of creating 10 000 eloquent objects.

    }

});

Few ways also. Query scopes, use Eloquent instead of query builder with proper relationships (count() method) and eager or lazy eager loading.......

Thanks, @pmall

The idea of using Project::whereHas() got me where I needed to, though I went about it a bit differently. The method signature for whereHas is a bit different from what you showed and for my needs paginate was more useful than chunk, so for anyone else interested in my solution my code is below. As you rightly pointed out, calling chunk() means instantiating only n Eloquent records (rather than the entire Projects table), and the paginate() method does the same (ie. behind the scenes it's only fetching n records from the database and retuning just the results for that 'page'.

So, my new code for reference, inspired by @pmall 's answer:

<?php 

class Project extends \Illuminate\Database\Eloquent\Model {

    public function budgets()
    {
        return $this->hasMany('Budget', 'project_id');
    }

}

class Budget extends \Illuminate\Database\Eloquent\Model {

    public function project()
    {
        return $this->belongsTo('Project', 'project_id');
    }

}

class ProjectRepository {

    public function all()
    {
        $query = Project::query();

        if ($filter_by_projects_with_any_number_of_budgets)
        {
            // Honestly, I'm not sure what the closure is meant to be used for,
            // but just passing an empty function like this seems to work!
            $query->whereHas('budgets', function(){}, '>=', 1);
        }

        if ($filter_by_projects_with_just_one_budget)
        {
            $query->whereHas('budgets', function(){}, '=', 1);
        }

        if ($filter_by_projects_with_two_or_more_budgets)
        {
            $query->whereHas('budgets', function(){}, '>', 1);
        }

        // `paginate()` runs the query against the database but only fetches the 
        // 20 records needed for the current page.
        // 
        // Note that the current page is determined by the 'page' parameter in 
        // $_GET array, so including `?page=3` in your URL will be automatically 
        // picked up by Laravel and you'll get records 41 to 60 returned. Note 
        // that the current pagination 'page' can be shown by calling 
        // \DB::getPaginator()->getCurrentPage() and can be overridden by calling 
        // \DB::getPaginator()->setCurrentPage(2) and passing the desired page 
        // number. This is actually what I'm doing in my real repository so I can 
        // call it outside of the HTTP context making it a bit more portable.
        $projects = $query->paginate(20);

        return $projects;
    }

}

@pmall It seems that I didn't actually need to include a budget_count method on my Project model. Can you see anything wrong with my new code, or have I missed a concept that you'd included in yours?

@Harrisonbro I just realized my answer above is pure BS ^^ in fact you just have to use has :

$query->has('budgets', '>=', 1);