Laravel eloquent where max date equal to past 30 days

Question

I have this eloquent:

$users = [1,2,3,4,5];
$transactions = Transaction::whereIn('user_id', $users)
            ->where(DB::raw('DATE(MAX(created_at))'), Carbon::now()->subMonth()->format('Y-m-d'))->get();

return $transactions;

But it return me this error: General error: 1111 Invalid use of group function (SQL: select * from transactions where 0 = 1

How can I get all the users that last transaction equal to past 30 days?

Here is the raw query incase you needed:

select * from `transactions` where `0` = 1 and DATE(MAX(created_at)) = ?

Table Structure:

Users Table: id (not autoincrement), name, gender, created_at

Transactions Table: id (not autoincrement), user_id (FK), amount, created_at

My current solution:

Iterate every user and check their max created_at.

Thanks

What is it that you are trying to query exactly, it looks like there is a much more eloquent way to do this? You are querying where the `user_id` is in your array, but what are you trying to do with the date? — James, Apr 06 '16 at 08:20
Hi @James. Sorry for my bad explanation. I've tried to query all transactions within those array of user that the last transaction is equal to 1 month — ssuhat, Apr 06 '16 at 08:22
When you say is equal to 1 month, are you wanting those where the `created_at` date is exactly one month in the past or those that are 1 month or older? — James, Apr 06 '16 at 08:24
You can't use a grouping function (like `MAX`) inside a `WHERE` clause. In a straight MySQL query, you would need to move this to a `HAVING` clause. — Anthony, Apr 06 '16 at 08:53
For example (in Eloquent) `->having(DB::raw('DATE(MAX(created_at))'), Carbon::now()->subMonth()->format('Y-m-d'))` so swap out your `where->` with `having->` — Anthony, Apr 06 '16 at 08:59
@Anthony the problem with having i've tried it is: It will return all the transactions that less than a month — ssuhat, Apr 06 '16 at 09:05
I know this does not solve your problem, but you don't need any foreach (as you mention in comments below) just use collections `unique()`: `$transactions->unique('user_id')` and you get what you want. I have searched some topics that might help but nothing is Eloquent way, see: http://stackoverflow.com/questions/21760789/limit-number-of-rows-per-id-in-a-sql-query?lq=1 Also use `whereDate()`.. — Kyslik, Apr 06 '16 at 09:28

score 0 · Answer 1 · answered Apr 06 '16 at 08:30

0

You could simply do this without having to use a DB::raw() query.

Try this:

$users = [1,2,3,4,5];
$transactions = Transaction::whereIn('user_id', $users)
            ->where('created_at', '=', Carbon::now()->format('Y-m-d'))
            ->get();

return $transactions;

answered Apr 06 '16 at 08:30

James

15,754
12
73
91

I've tried this way, but the problem is if user has 2 transactions it will keep showing transaction with equal 1 month. I want to get the last transaction from the user. If the last is != 1 month don't show the transaction. – ssuhat Apr 06 '16 at 08:31
@SSuhat so you are only after the latest transaction from each of those users that is one month old? – James Apr 06 '16 at 08:32
correct. Even the user has 100 transactions. I only want to take the latest. – ssuhat Apr 06 '16 at 08:33
Do you have to use the `whereIn`, or could you run a separate query for each user and return them as one bulk set? – James Apr 06 '16 at 08:33
@Kyslik limiting the result will limit it for all of the users and only return one result, rather than one for each user. – James Apr 06 '16 at 08:34
I want to get every user data. I've solution with foreach the user one by one. but i don't think its a good idea because it will slow down my server if the user is big. But if the best solution is iterate the user one by one, i don't have choice to take that way – ssuhat Apr 06 '16 at 08:35
There may be a way to do this with `whereIn()` but I'm not too sure how to limit it to only one for each person sorry. – James Apr 06 '16 at 08:46
No problem @James. Thanks for trying to help solve this. :) – ssuhat Apr 06 '16 at 09:11

Sushant Yadav · Answer 2 · 2016-04-06T09:27:33.100

0

Now what I tried is that I sorted the table in decreasing order as to evaluate the latest record when it will be group by user Id...

$users = [1,2,3,4,5];
$transactions = Transaction::select(DB::raw('select id from transactions order by transaction.created_at desc as table1' )->leftJoin('transactions','id','=','table1.id')->whereIn('user_id', $users)
            ->where('created_at', '=', Carbon::now()->subMonth()->format('Y-m-d'))->groupBy('transaction.user_id')
            ->get();

return $transactions;

edited Apr 06 '16 at 09:27

answered Apr 06 '16 at 09:01

Sushant Yadav

726
1
13
28

This should working. But i forget to tell, that my transaction ID is not increment. so using MAX(id) won't get latest id – ssuhat Apr 06 '16 at 09:09

score 0 · Answer 3 · answered Apr 06 '16 at 09:07

0

There is no need to do a raw query.

$users = [1,2,3,4,5];
$transactions = Transaction::whereIn('user_id', $users)
                ->whereDate('created_at', '=', Carbon::today()->subDays(30)->toDateString())->get();

return $transactions;

answered Apr 06 '16 at 09:07

Claudio King

1,606
1
10
12

I've tried this way, but the problem is if user has 2 transactions it will keep showing transaction with equal 1 month. I want to get the latest transaction from the user. If the last is != 1 month don't show the transaction. – ssuhat Apr 06 '16 at 09:09
Have you tried `->whereDate('created_at', '<=', Carbon::today()->subDays(30)->toDateString()) ` – Claudio King Apr 06 '16 at 09:18
I've tried your way, still i can all more than 30 days (duplicate result). – ssuhat Apr 06 '16 at 09:22

score 0 · Answer 4 · answered Apr 06 '16 at 09:15

0

you have to use whereBetween

$users = [1,2,3,4,5];
$transactions = Transaction::select(DB::raw('DATE(MAX(created_at))'))->whereIn('user_id', $users)
        ->whereBetween('created_at',array(Carbon::now(),Carbon::now()->subMonth()->format('Y-m-d')))->get();

return $transactions;

answered Apr 06 '16 at 09:15

Imtiaz Pabel

5,307
1
19
24

no OP does not, OP can use simple `whereDate('created_at', Carbon::now()->subMonth()->format('Y-m-d'))`, problem he is trying to solve is with duplicates, OP wants only one record per user. – Kyslik Apr 06 '16 at 09:32

Laravel eloquent where max date equal to past 30 days

4 Answers4