phplaraveleloquentrelationshipquery-builder

Laravel Eloquent: Excluding Titles with Quizzes or Contests While Including Those with Practices


I'm working on a Laravel application and I need help with filtering titles based on their relationships with practices, quizzes, and contests. Specifically, I want to retrieve titles that have associated practices but do not have any associated quizzes or contests. However, I'm running into an issue where titles that have both practices and quizzes (or contests) are being excluded from the result set.

Here’s a simplified version of my code:

public function index(Request $request): JsonResponse
{
    $user = User::find(auth()->id());
    
    // ... other request handling code

    $titlesQuery = Title::with(['practices', 'quizzes', 'contests']);

    if (!$user->is_accepted) {
        $titlesQuery->whereHas('practices')
                     ->whereDoesntHave('quizzes')
                     ->whereDoesntHave('contests');
    }

    // Additional filters and search terms

    return $titlesQuery->paginate($limit);
}

Issue:
The problem I’m facing is that when I run this query, any title that has a quiz (even if it also has a practice) is completely excluded from the result. I want to only exclude titles with quizzes or contests if they don’t have any practices, and return only the practice where necessary.

Example Scenario For example, if I have the following records:

Title A has both a practice and a quiz.
Title B has only a practice.
Title C has a quiz.
Title C has a contest.

I would like my query to return:

Title A (should return the practice & exclude the quiz)
Title B (should be included)
Title C (should not be included because it is a quiz)
Title D (should not be included because it is a contest)

Additional Information

My Title model has relationships defined with practices, quizzes, and contests. I want to maintain other filters (like search, category, and subcategory) that I apply later in the query.

My model relationships:

//Title:
 public function quizzes()
    {
        return $this->hasMany(Quiz::class);
    }

    public function practices()
    {
        return $this->hasMany(Practice::class);
    }

    public function contests()
    {
        return $this->hasMany(Contest::class);
    }
//Quiz, Practice, Contest
    public function title()
    {
        return $this->belongsTo(Title::class);
    }

What I’ve Tried I’ve tried restructuring my query to use whereHas for practices first, followed by whereDoesntHave for quizzes and contests, but it still excludes titles that have quizzes when I only want to exclude them when they don’t have practices.

I ALSO TRIED TO RUN A SUBQUERY LIKE:

$titlesQuery->whereHas('practices')
    ->where(function ($query) {
        // Exclude titles that have quizzes or contests
        $query->whereDoesntHave('quizzes')
              ->whereDoesntHave('contests');
         });

Any guidance on how to achieve this would be greatly appreciated!


Solution

  • After trying several approaches, I found a working solution that properly filters titles with practices and selectively includes quizzes or contests based on specific criteria (such as user access is_accepted). Here's the final code that worked for me:

    public function index(Request $request)
        {
            $titlesQuery = Title::select('id', 'name', 'category_id', 'subcategory_id', 'created_at');
    
            // I Loaded title with practices for all users
            $titlesQuery->with(['practices' => function ($query) {
                $query->select('id', 'title_id', 'is_group_test', 'is_locked', 'image_url', 'created_at');
                //other practice based filters
            }]);
    
            // Then Only if user is accepted, I will load Title with quizzes and contests alongside their individual constraints.
            if ($user->is_accepted) {
                $titlesQuery->with([
                    'quizzes' => function ($query) use ($user) {
                        $query->select('id', 'title_id', 'is_group_test', 'is_locked', 'image_url', 'created_at');
                        //other quiz based filters
                    },
                    'contests' => function ($query) use ($user) {
                        $query->select('id', 'title_id', 'is_group_test', 'is_locked', 'image_url', 'created_at');
                        //other contest based filters
                    }
                ]);
            }
    
            // Additional filters and search terms
    
            return $titlesQuery->paginate($limit);
        }
    

    With this, when a user is not accepted only Title with practices are loaded. And if a user is accepted, The the practice loads as well as quizzes and contests applying all other constraints that are necessary.