Laravel: Merge two query builders

Question

I have a table of courses which will be free to access or an admin will need to click something to let users see the course.

The course table looks like this:

| id | title          | invite_only | 
|----|----------------|-------------|
| 1  | free course    | 0           |
| 2  | private course | 1           |

Separate from this I have a course_user table, where initially users request access, then admins can approve or deny access:

| id | user_id | course_id | approved | declined |
|----|---------|-----------|----------|----------|
| 1  | 3       | 2         | 1        | 0        |
| 2  | 4       | 1         | 0        | 1        |
| 3  | 4       | 2         | 0        | 0        |

I'd like to index all the courses a user has access to:

class User extends model{
  public function myCourses(){
    $public = $this->publicCourses;
    $invited = $this->invitedCourses;
    return $public->merge($invited);
  }
  public function publicCourses(){
    return $this
      ->hasMany('App\Course')
      ->where('invite_only', false);
  }
  public function invitedCourses(){
    return $this
      ->belongsToMany("\App\Course")
      ->using('App\CourseUser')
      ->wherePivot('approved', 1);
  }
}

How can I make the myCourses function return the results of both publicCourses and invitedCourses by doing only one database query? I'd like to merge the two query builder instances.

Answer 1

According to the doc, you can use union to merge query builders. But as far as I know, it does not work with relations. So maybe you should do it from within controller instead of model. This is an example based on what I understand from your example:

$q1 = App\Course::join('course_user', 'course_user.course_id', 'courses.id')
    ->join('users', 'users.id', 'course_user.user_id')
    ->where('courses.invite_only', 0)
    ->select('courses.*');
$q2 = App\Course::join('course_user', 'course_user.course_id', 'courses.id')
    ->join('users', 'users.id', 'course_user.user_id')
    ->where('courses.invite_only', 1)
    ->where('course_user.approvoed', 1)
    ->select('courses.*');
$myCourses = $q1->unionAll($q2)->get();

You can also refactor the code further by creating a join scope in App\Course.