Home > Net >  how to get latest record for each user in laravel
how to get latest record for each user in laravel

Time:11-30

Hello all i want a latest record from table in laravel for each customer but i am not getting result as i expected below is my object and code.i want a latest entry for each customer from table

[
    {
        "id": 1,
        "customer_id": 10,
        "bill_no": 1,
        "bill_period": "",
        "from_date": "2022-11-21",
        "to_date": "2022-11-27",
        "month": "Nov 2022",
        "total_litres": "5600",
        "amount": "420000",
        "previous_balance": "0",
        "total_amount": "420000",
        "amount_paid": "350000",
        "adjusted": "0",
        "pending_amount": "70000",
        "created_at": "2022-11-26 05:57:54",
        "updated_at": "2022-11-27 03:11:57",
        "customer_name": "qwe"
    },
    {
        "id": 2,
        "customer_id": 11,
        "bill_no": 2,
        "bill_period": "",
        "from_date": "2022-11-21",
        "to_date": "2022-11-27",
        "month": "Nov 2022",
        "total_litres": "1680",
        "amount": "129360",
        "previous_balance": "0",
        "total_amount": "129360",
        "amount_paid": "120000",
        "adjusted": "9360",
        "pending_amount": "0",
        "created_at": "2022-11-26 05:57:54",
        "updated_at": "2022-11-27 03:13:05",
        "customer_name": "rty"
    },
    {
        "id": 4,
        "customer_id": 13,
        "bill_no": 4,
        "bill_period": "",
        "from_date": "2022-11-21",
        "to_date": "2022-11-27",
        "month": "Nov 2022",
        "total_litres": "560",
        "amount": "42000",
        "previous_balance": "0",
        "total_amount": "42000",
        "amount_paid": "42000",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-26 05:57:54",
        "updated_at": "2022-11-27 03:13:27",
        "customer_name": "uio"
    },
    {
        "id": 5,
        "customer_id": 14,
        "bill_no": 5,
        "bill_period": "",
        "from_date": "2022-11-21",
        "to_date": "2022-11-27",
        "month": "Nov 2022",
        "total_litres": "500",
        "amount": "39000",
        "previous_balance": "0",
        "total_amount": "39000",
        "amount_paid": "39000",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-26 05:57:54",
        "updated_at": "2022-11-27 03:13:34",
        "customer_name": "asd"
    },
    {
        "id": 6,
        "customer_id": 15,
        "bill_no": 6,
        "bill_period": "",
        "from_date": "2022-11-21",
        "to_date": "2022-11-27",
        "month": "Nov 2022",
        "total_litres": "560",
        "amount": "42560",
        "previous_balance": "0",
        "total_amount": "42560",
        "amount_paid": "42560",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-26 05:57:54",
        "updated_at": "2022-11-27 03:13:42",
        "customer_name": "fgh"
    },
    {
        "id": 7,
        "customer_id": 17,
        "bill_no": 7,
        "bill_period": "",
        "from_date": "2022-11-21",
        "to_date": "2022-11-27",
        "month": "Nov 2022",
        "total_litres": "490",
        "amount": "38220",
        "previous_balance": "0",
        "total_amount": "38220",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "38220",
        "created_at": "2022-11-26 05:57:54",
        "updated_at": null,
        "customer_name": "jkl"
    },
    {
        "id": 8,
        "customer_id": 18,
        "bill_no": 8,
        "bill_period": "",
        "from_date": "2022-11-21",
        "to_date": "2022-11-27",
        "month": "Nov 2022",
        "total_litres": "315",
        "amount": "24570",
        "previous_balance": "0",
        "total_amount": "24570",
        "amount_paid": "24570",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-26 05:57:54",
        "updated_at": "2022-11-26 10:28:45",
        "customer_name": "zxc"
    },
    {
        "id": 9,
        "customer_id": 10,
        "bill_no": 9,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-04",
        "month": "Nov 2022",
        "total_litres": "5600",
        "amount": "420000",
        "previous_balance": "70000",
        "total_amount": "490000",
        "amount_paid": "450000",
        "adjusted": "0",
        "pending_amount": "40000",
        "created_at": "2022-11-27 03:16:17",
        "updated_at": "2022-11-27 11:52:52",
        "customer_name": "qwe"
    },
    {
        "id": 10,
        "customer_id": 11,
        "bill_no": 10,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-04",
        "month": "Nov 2022",
        "total_litres": "1680",
        "amount": "129360",
        "previous_balance": "0",
        "total_amount": "129360",
        "amount_paid": "115000",
        "adjusted": "0",
        "pending_amount": "14360",
        "created_at": "2022-11-27 03:16:17",
        "updated_at": "2022-11-27 11:52:52",
        "customer_name": "rty"
    },
    {
        "id": 12,
        "customer_id": 13,
        "bill_no": 12,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-04",
        "month": "Nov 2022",
        "total_litres": "560",
        "amount": "42000",
        "previous_balance": "0",
        "total_amount": "42000",
        "amount_paid": "40000",
        "adjusted": "0",
        "pending_amount": "2000",
        "created_at": "2022-11-27 03:16:17",
        "updated_at": "2022-11-27 11:52:52",
        "customer_name": "uio"
    },
    {
        "id": 13,
        "customer_id": 14,
        "bill_no": 13,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-04",
        "month": "Nov 2022",
        "total_litres": "490",
        "amount": "38220",
        "previous_balance": "0",
        "total_amount": "38220",
        "amount_paid": "38220",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-27 03:16:17",
        "updated_at": "2022-11-27 11:52:52",
        "customer_name": "asd"
    },
    {
        "id": 14,
        "customer_id": 15,
        "bill_no": 14,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-04",
        "month": "Nov 2022",
        "total_litres": "560",
        "amount": "42560",
        "previous_balance": "0",
        "total_amount": "42560",
        "amount_paid": "42560",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-27 03:16:17",
        "updated_at": "2022-11-27 11:52:52",
        "customer_name": "fgh"
    },
    {
        "id": 15,
        "customer_id": 17,
        "bill_no": 15,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-04",
        "month": "Nov 2022",
        "total_litres": "490",
        "amount": "38220",
        "previous_balance": "38220",
        "total_amount": "76440",
        "amount_paid": "76440",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-27 03:16:17",
        "updated_at": "2022-11-27 11:52:52",
        "customer_name": "jkl"
    },
    {
        "id": 16,
        "customer_id": 18,
        "bill_no": 16,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-04",
        "month": "Nov 2022",
        "total_litres": "315",
        "amount": "24570",
        "previous_balance": "0",
        "total_amount": "24570",
        "amount_paid": "24570",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-27 03:16:17",
        "updated_at": "2022-11-27 11:52:52",
        "customer_name": "zxc"
    },
    {
        "id": 17,
        "customer_id": 10,
        "bill_no": 17,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "5600",
        "amount": "420000",
        "previous_balance": "40000",
        "total_amount": "460000",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "460000",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "qwe"
    },
    {
        "id": 18,
        "customer_id": 11,
        "bill_no": 18,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "1680",
        "amount": "129360",
        "previous_balance": "14360",
        "total_amount": "143720",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "143720",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "rty"
    },
    {
        "id": 20,
        "customer_id": 13,
        "bill_no": 20,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "600",
        "amount": "45000",
        "previous_balance": "2000",
        "total_amount": "47000",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "47000",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "uio"
    },
    {
        "id": 21,
        "customer_id": 14,
        "bill_no": 21,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "495",
        "amount": "38610",
        "previous_balance": "0",
        "total_amount": "38610",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "38610",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "asd"
    },
    {
        "id": 22,
        "customer_id": 15,
        "bill_no": 22,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "560",
        "amount": "42560",
        "previous_balance": "0",
        "total_amount": "42560",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "42560",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "fgh"
    },
    {
        "id": 23,
        "customer_id": 17,
        "bill_no": 23,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "490",
        "amount": "38220",
        "previous_balance": "0",
        "total_amount": "38220",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "38220",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "jkl"
    },
    {
        "id": 24,
        "customer_id": 18,
        "bill_no": 24,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "315",
        "amount": "24570",
        "previous_balance": "0",
        "total_amount": "24570",
        "amount_paid": "24570",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "zxc"
    },
    {
        "id": 25,
        "customer_id": 12,
        "bill_no": 25,
        "bill_period": "",
        "from_date": "2022-11-18",
        "to_date": "2022-11-27",
        "month": "Nov 2022",
        "total_litres": "4001",
        "amount": "296074",
        "previous_balance": "0",
        "total_amount": "296074",
        "amount_paid": "250000",
        "adjusted": "0",
        "pending_amount": "46074",
        "created_at": "2022-11-29 12:26:51",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "mno"
    },
    {
        "id": 26,
        "customer_id": 12,
        "bill_no": 26,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-07",
        "month": "Nov 2022",
        "total_litres": "4000",
        "amount": "296000",
        "previous_balance": "46074",
        "total_amount": "342074",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "342074",
        "created_at": "2022-11-29 12:36:02",
        "updated_at": null,
        "customer_name": "mno"
    }
]

bewlo result i want

[
        
    {
        "id": 17,
        "customer_id": 10,
        "bill_no": 17,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "5600",
        "amount": "420000",
        "previous_balance": "40000",
        "total_amount": "460000",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "460000",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "qwe"
    },
    {
        "id": 18,
        "customer_id": 11,
        "bill_no": 18,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "1680",
        "amount": "129360",
        "previous_balance": "14360",
        "total_amount": "143720",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "143720",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "rty"
    },
    {
        "id": 20,
        "customer_id": 13,
        "bill_no": 20,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "600",
        "amount": "45000",
        "previous_balance": "2000",
        "total_amount": "47000",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "47000",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "uio"
    },
    {
        "id": 21,
        "customer_id": 14,
        "bill_no": 21,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "495",
        "amount": "38610",
        "previous_balance": "0",
        "total_amount": "38610",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "38610",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "asd"
    },
    {
        "id": 22,
        "customer_id": 15,
        "bill_no": 22,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "560",
        "amount": "42560",
        "previous_balance": "0",
        "total_amount": "42560",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "42560",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "fgh"
    },
    {
        "id": 23,
        "customer_id": 17,
        "bill_no": 23,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "490",
        "amount": "38220",
        "previous_balance": "0",
        "total_amount": "38220",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "38220",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "jkl"
    },
    {
        "id": 24,
        "customer_id": 18,
        "bill_no": 24,
        "bill_period": "",
        "from_date": "2022-12-05",
        "to_date": "2022-12-11",
        "month": "Dec 2022",
        "total_litres": "315",
        "amount": "24570",
        "previous_balance": "0",
        "total_amount": "24570",
        "amount_paid": "24570",
        "adjusted": "0",
        "pending_amount": "0",
        "created_at": "2022-11-27 11:53:45",
        "updated_at": "2022-11-29 12:35:01",
        "customer_name": "zxc"
    },
    {
        "id": 26,
        "customer_id": 12,
        "bill_no": 26,
        "bill_period": "",
        "from_date": "2022-11-28",
        "to_date": "2022-12-07",
        "month": "Nov 2022",
        "total_litres": "4000",
        "amount": "296000",
        "previous_balance": "46074",
        "total_amount": "342074",
        "amount_paid": "0",
        "adjusted": "0",
        "pending_amount": "342074",
        "created_at": "2022-11-29 12:36:02",
        "updated_at": null,
        "customer_name": "mno"
    }
]

i want a unique data of each customer with their latest entry below is my code. any help would be appreciated.

$data = DB::table('weekly_billing')
                ->leftJoin('customers', 'weekly_billing.customer_id', '=', 'customers.id')
                ->select('weekly_billing.*','customers.customer_name',DB::raw('max(weekly_billing.created_at)'))                
                ->groupBy('weekly_billing.customer_id')                                                                                                                
                ->get();

CodePudding user response:

You could establish this by using the where() statement. Another option would be using ->orderBy('columnname', 'desc'). You could use the created_at or updated_at in this instance. Also take a look at this link for more information.

CodePudding user response:

You can add ->latest() before ->get() method

So, you have

$data = DB::table('weekly_billing')
                ->leftJoin('customers', 'weekly_billing.customer_id', '=', 'customers.id')
                ->select('weekly_billing.*','customers.customer_name',DB::raw('max(weekly_billing.created_at)'))                
                ->groupBy('weekly_billing.customer_id')
                ->latest()                                                                                         
                ->get();

  • Related