Laravel billable get records where subscription not ended

Question

I'm using Laravel Billable and I've got this hooked up to a different model (Client) and all works fine. However, I'm trying to fetch all records on a model where the subscription end date is null. I'm doing the following;

$collection = Model::where('client.subscriptions', function ($q) {
    $q->where('ends_at', NULL);
})->get();

I get the following error:

Undefined table: 7 ERROR: missing FROM-clause entry for table "client" LINE 1: select * from "table" where "client"."subscriptions" =... ^ (SQL: select * from "table" where "client"."subscriptions" = (select * where "ends_at" is null))

Update

Code Update

   $jobs = \App\Models\Job::with(['client' => function($query) {
    $query->whereHas('subscriptions', function ($query){
        $query->where('ends_at', NULL);
    });
}])->get();

Example Database

(model) Job (db:name = 'jobs')

id | client_id | name

(model) Client (db:name = 'clients')

id | name 

(model) Subscription (db:name = 'subscriptions')

id | client_id | stripe_plan | trial_ends_at | ends_at 

Client Model

/**
 * Get all of the subscriptions for the Stripe model.
 *
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function subscriptions()
{
   // foreign key is client_id
    return $this->hasMany(Subscription::class, $this->getForeignKey())->orderBy('created_at', 'desc');
}

Job Model

public function client()
{
    return $this->belongsTo(Client::class);
}

Now using this query;

 $jobs = \App\Models\Job::with(['client' => function($query) {
        $query->whereHas('subscriptions', function ($query){
            $query->where('ends_at', NULL);
        });
    }])->get();

I still get the records where the subscription ends_at is not null. Any ideas what I'm doing wrong?

Answer 1

The correct query is;

$jobs = Job::whereHas('client', function($query) {
    $query->whereHas('subscriptions', function ($query) {
        $query->where('ends_at', NULL);
    });
})->get();