Is this too complex for Eloquent?

Let's say I have 3 tables (unnecessary columns omitted in example):

• issues table with columns: id, status_id, assigned_to_id - issues currently assigned to users
• statuses table with columns: id, is_closed - issues statuses
• time_entries table with columns: assignee_id, issue_id, spent_on, hours - issues that users spent time on

I need to calculate the sum of the number of open issues currently assigned to each user and the number of issues that user spent time on within given period. Also following should not be counted multiple times:

• If multiple time entries exist for same issue
• If user spent time on issue and issue is currently assigned to him.

That's my code atm which works fine according to my tests:

        $query = 'select assignee_id, COUNT(*) as participated_issues_count from (';
        $query .= 'select id, assigned_to_id as assignee_id from issues where exists (select * from statuses where issues.status_id = statuses.id and is_closed = ?)';
        $query .= ' UNION ';
        $query .= 'select distinct issues.id as id, time_entries.assignee_id from time_entries inner join issues on time_entries.issue_id = issues.id where spent_on > ? and spent_on < ?';
        $query .= ') s group by assignee_id';
        $participatedIssuesCount = DB::select($query, [0, $periodStartDate, $periodEndDate]);              

Same as SQL query:

select assignee_id, COUNT(*) as participated_issues_count from (select id, assigned_to_id as assignee_id from issues where exists (select * from statuses where issues.status_id = statuses.id and is_closed = ?) UNION select distinct issues.id as id, time_entries.assignee_id from time_entries inner join issues on time_entries.issue_id = issues.id where spent_on > ? and spent_on < ?) s group by assignee_id

But this is raw SQL. I spent a long time trying to do the same as Eloquent or Query Builder query but it never works as I need it to.

I tried this:

        $spentIssues =  TimeEntry::spentWithin($periodStartDate, $periodEndDate)
            ->join('issues', 'time_entries.issue_id', '=', 'issues.id')
            ->select('issues.id as id', 'time_entries.assignee_id')
            ->distinct();

        $participated_issues_count = Issue::open()
            ->select('id', 'assigned_to_id as assignee_id')
            ->union($spentIssues)
            ->groupBy('assignee_id')
            ->select(DB::raw('count(*) as participated_issues_count'));

But in ends up in totally wrong SQL query which doesn't even execute:

select * from (select count(*) as participated_issues_count from "issues" where exists (select * from "statuses" where "issues"."status_id" = "statuses"."id" and "is_closed" = ?) group by "assignee_id") union select * from (select distinct "issues"."id" as "id", "time_entries"."assignee_id" from "time_entries" inner join "issues" on "time_entries"."issue_id" = "issues"."id" where "spent_on" > ? and "spent_on" < ?)

So what do you think is the best way to do this? Leave is at as raw SQL? Do separate queries and merge the result on PHP side? Or maybe you know how to do correct Eloquent query?

Thank you for spending your time reading this long post.