Get a max date column using where clause in Laravel query builder

user_8573455 0
Viewed 590

I have a pivot table that is intermediate between users and subscriptions. Here is the schema:

 Schema::create('users_subscriptions', function (Blueprint $table) {
            $table->bigInteger('user_id')->unsigned();
            $table->bigInteger('subscription_code_id')->unsigned()->nullable();
            $table->timestamps();
            $table->timestamp('expired_at')->nullable();

            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('subscription_code_id')->references('id')->on('subscription_codes')->onDelete('cascade');
        });

So the relation is many-to-many, and I need to look for this pivot table to get the expired subscripsion users. Here is my query builder:

$query->select(self::columns())
                  ->join('users_subscriptions', 'users.id', '=', 'users_subscriptions.user_id')
                  ->where('users_subscriptions.expired_at', '<=', now())
                  ->groupBy('users.id')
                  ->orderBy('users_subscriptions.expired_at', 'asc')

Sample data of the pivot table users_subscritions:

user_id subscription_code_id created_at updated_at expired_at
1 69440 2021-01-23 20:55:37 2021-01-23 20:55:37 2019-01-23 20:55:37
1 69441 2021-01-23 21:01:14 2021-01-23 21:01:14 2023-01-23 20:55:37

The issue is that the query looking for the first record in the pivot table and check against it. I need to look for the max expired_at and check against only. The expected result is to check for the second row in the pivot table because it has the max expired_at value. Therefore, the user_id 1 has a valid subscription.

I think I need to use subquery but not sure how to accomplish this.

Thank you

laravel mysql
1 Answers

I think this will return what you want:

SELECT user_id, MAX(expired_at) AS ed
FROM users_subscriptions
GROUP BY user_id
HAVING ed > NOW();

I'm not sure how to write it in query builder format though. What I did there is returning the MAX(expired_at) for each id then group it bt id. I notice that your query builder have users_subscriptions join with users but judging from the create table statement, running this query on users_subscriptions is enough to get the expired date value and I'm guessing that the join with users is just there to return user additional info. The next operation after GROUP BY is HAVING - this is to filter out expired_at. Note that since I assign MAX(expired_at) with alias ed, I reuse the same alias in HAVING. However, I'm not sure the behaviour of query builder whether this is acceptable or not but if it's not, you can change to:

HAVING MAX(expired_at) > NOW()

P/S: Whether its >, >=, <= or <, I'll let you decide that .. because I see you're using <= in your query builder.

Here's a fiddle for reference: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b95ebc606916e60dc0ed25777c0390a7

user_109106920
Related