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();