Eloquent whereNotExists not generating correctly

Question

I have the following SQL which pulls a root node from a nested table (relative to the user, i.e. this highest node to which a user is assigned),

SELECT 
   child_domain.*, child_subscription.user_id 
FROM
   domains AS child_domain 
JOIN subscriptions 
   AS child_subscription 
   ON child_subscription.domain_id = child_domain.id
WHERE NOT EXISTS
   (
      SELECT 1 
         FROM   domains AS parent_domain 
         JOIN subscriptions 
            AS parent_subscription
            ON parent_subscription.domain_id = parent_domain.id 
         WHERE  
            parent_domain.start < child_domain.start 
         AND
            parent_domain.end > child_domain.end 
         AND 
            parent_subscription.user_id = child_subscription.user_id
    );

The above returns the root node of nested rows, the tables look something like this,

-----------   -----------------   ---------
| DOMAINS |   | SUBSCRIPTIONS |   | USERS |
-----------   -----------------   ---------
| id      |   | user_id       |   | id    |
| start   |   | domains_id    |   | name  |
| end     |   -----------------   ---------
-----------

However, I'm having trouble converting it to Eloquent. The problems appears to be in the subquery (I've tried whereDoesntHave and whereNotExists, but neither work).

DB::table('domains as child_domain')
   ->join('subscriptions as child_subscription','child_subscription.domain_id','child_domain.id')
   ->whereNotExists('domains as parent_domain',function($query){
      $query
         ->join('subscriptions as parent_subscription','parent_subscription.domain_id','parent_domain.id')
         ->where('parent_domain.end','>','parent_domain.end')
         ->where('parent_domain.start'.'<','parent_domain.start')
         ->where('parent_subscription.user_id', 'child_subscription.user_id')->get();
});

The code above, when I output the toSql() shows,

select * from `domains` as `child_domain` inner join `subscriptions` as `child_subscription` on `child_subscription`.`domain_id` = `child_domain`.`id` where not exists (select *)

Any help appreciated.

Answer 1

Tested and generates exactly what you want.

DB::table('domains as child_domain')
    ->join('subscriptions as child_subscription', 'child_subscription.domain_id', 'child_domain.id')
    ->whereNotExists(function($query) {
        $query->selectRaw(1)
            ->from('domains as parent_domain')
            ->join('subscriptions as parent_subscription', 'parent_subscription.domain_id', 'parent_domain.id')
            ->whereColumn('parent_domain.start', '<', 'child_domain.start')
            ->whereColumn('parent_domain.end', '>', 'child_domain.end')
            ->whereColumn('parent_subscription.user_id', 'child_subscription.user_id');
    })
    ->select('child_domain.*', 'child_subscription.user_id')
    ->get();

Query crafted

select `child_domain`.*, `child_subscription`.`user_id` from `domains` as `child_domain` inner join `subscriptions` as `child_subscription` on `child_subscription`.`domain_id` = `child_domain`.`id` where not exists (select 1 from `domains` as `parent_domain` inner join `subscriptions` as `parent_subscription` on `parent_subscription`.`domain_id` = `parent_domain`.`id` where `parent_domain`.`start` < `child_domain`.`start` and `parent_domain`.`end` > `child_domain`.`end` and `parent_subscription`.`user_id` = `child_subscription`.`user_id`)