CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-8
Nevil Saul Blemuss
Nevil Saul Blemuss

Reputation: 345

Get users that are only subscribed in Laravel Cashier - all users not a user

Here is the query I tried to write and gives an error

$users =User::has('subscriptions', function (Builder $q) {
 $q->whereNotNull('ends_at');
})->get();

Getting this error

SQLSTATE[42601]: Syntax error: 7 ERROR: SELECT * with no tables specified is not valid LINE 1: ...sers"."id" = "subscriptions"."user_id") = (select * where "e... ^ (SQL: select * from "users" where (select count(*) from "subscriptions" where "users"."id" = "subscriptions"."user_id") = (select * where "ends_at" > now) and "users"."deleted_at" is null)

When I write this code I get results but need to filter result to get a list of subscribed users without calling User::all() then loop to filter.

User::has('subscriptions')->get();

Upvotes: 1

Views: 974

Answers (4)

S Ahmed Naim
S Ahmed Naim

Reputation: 304

$users = User::with(['subscriptions' => function ($query) {
    return $query->whereNotNull('ends_at');
}])->get();

Or,

$users = User::with('subscriptions')->whereHas('subscriptions', function ($query) {
    return $query->whereNotNull('ends_at');
})->get();

This will give you only subscribed users. But if you fetch all the users and then apply filter to the fetched result, then every row will be fetched from the database, which is not a good practice.

Upvotes: 1

zain malik
zain malik

Reputation: 11

Do it like this

$users = User::with(['subscriptions' => static function ($query) { $query->whereNotNull('ends_at'); }])->get();

Upvotes: 1

Abdulla Nilam
Abdulla Nilam

Reputation: 38652

use

$users = User::with(['subscriptions' => static function ($query) {
    $query->whereNotNull('ends_at');
}])->get();

Read Constraining Eager Loads

To query, you need to load the subscriptions relationship first.

Upvotes: 2

Joukhar
Joukhar

Reputation: 872

Solution is here 😁

$users =  User::whereHas('subscriptions', function (Builder $q) {
        return $q->active();
    })->get()->toArray();

Have a good day

Upvotes: 2

Related Questions