Retrieves the latest data from a foreign key in a relation

Question

I want to ask, can I retrieve the last foreign key data in the relation ?.

In this case I want to retrieve the last user transaction data.

for example in the transaction table between 3 months ago and now there are some transaction data as follows.

{
    "current_page": 1,
    "data": [
        {
            "id": 8,
            "user_id": 1,
            "type": "sell",
            "created_at": "2019-12-11 11:55:31",
            "updated_at": "2019-12-11 11:55:31",
            "tax": null,
            "invoice_number": null,
            "name": "Fredy"
        },
        {
            "id": 7,
            "user_id": 1,
            "type": "buy",
            "created_at": "2019-11-20 17:14:55",
            "updated_at": "2019-11-20 17:14:55",
            "tax": null,
            "tax_percent": "0.90",
            "invoice_number": null,
            "name": "Fredy"
        },
        {
            "id": 6,
            "user_id": 8,
            "type": "buy",
            "created_at": "2019-11-20 16:28:33",
            "updated_at": "2019-11-20 16:28:33",
            "tax": null,
            "name": "Tommy"
        },
        {
            "id": 5,
            "user_id": 2,
            "type": "buy",
            "created_at": "2019-11-20 15:47:57",
            "updated_at": "2019-11-20 15:47:57",
            "tax": null,
            "name": "Sarah"
        },
        {
            "id": 4,
            "user_id": 3,
            "type": "buy",
            "created_at": "2019-11-20 15:47:31",
            "updated_at": "2019-11-20 15:47:31",
            "tax": null,
            "name": "John Doe"
        },
    ],
}

This is the sample code that i made

public function index()
{
   $start = Carbon::now()->subMonths(3)->format('Y-m-d') . ' 00:00:00';
   $end = Carbon::now()->format('Y-m-d') . ' 23:59:59';

   return $query = DB::table('transaction')
                     ->join('users', 'users.id', '=', 'gold_transaction.user_id')
                     ->select('transaction.id', 'user_id', 'users.*')
                     ->whereBetween('transaction.created_at', [$start, $end])
                     ->whereIn('transaction.id', [DB::raw("SELECT MAX(transaction.id) FROM transaction GROUP BY user_id")])->get();
}

What do i do if i only want to display users who did't make transaction more than 7 days from the last transaction for all users ?

Thank you.

There is nothing wrong in the code that I made. It's just, I want to retrieve users data where if there are the same foreign key in the transaction I am only want to retrieve the last foreign key data and don't have the same foreign key. Then bring up users who don;t make transactions start from the last data of the transaction until For more than 7 days. Can i use the condition in query builder ? Or is there another way ? @Qonvex620 — Auxulry, Jan 20 '20 at 08:53
Check https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql and see how you can convert one of those queries to eloquent/query builder. I know it's tagged MySQL but many of those queries are pure SQL and should work for Postgres — apokryfos, Jan 20 '20 at 08:53

score 0 · Accepted Answer · answered Jan 20 '20 at 08:46

0

Then you can do it like below,

Get user's transaction

$transaction = User::with(['transactions' => function ($q) use($start, $end){
   $q->whereBetween('transactions.created_at', [$start, $end]);
])->find($user_id);

Get all users with their transactions

$transaction = User::with(['transactions' => function ($q) use($start, $end){
     $q->whereBetween('transactions.created_at', [$start, $end]);
])->get();

assuming that you have transactions relation in your user model like below

public function transactions() {
     return $this->hasMany('App\Transaction', 'user_id');
}

answered Jan 20 '20 at 08:46

Qonvex620

3,819
1
8
15

I tried the method above but i didn't get the data i was looking for. Can i take all users who make transactions and retrieve the latest transaction data from all users and then only show users who didn't make transaction more than 7 days ? – Auxulry Jan 20 '20 at 08:59
didn't make transaction more than 7 days ? so you will compare if current transaction is not greater to 7 days to it's previous transactions ? – Qonvex620 Jan 20 '20 at 09:15
Yeah, that's right, but i want to do a group by where I only take the last user transactions in these 3 months and compare which users didn't make transactions for more than 7 days and call data users who don't more than 7 days start from the last transaction that users did. I tried this before in sql command **SELECT id, user_id FROM transaction WHERE id IN (SELECT MAX(id) FROM transaction GROUP BY user_id );** – Auxulry Jan 20 '20 at 09:29

Retrieves the latest data from a foreign key in a relation

1 Answers1