search

yajra / laravel-datatables

jQuery DataTables API for Laravel
https://yajrabox.com/docs/laravel-datatables
MIT License
4.73k stars 856 forks source link

Complex Relationships or Collections #3153

Open joegoldman opened 2 days ago

joegoldman commented 2 days ago

Hi,

I'm trying to do some stats rollups in a datatable, and make the columns of the rollup orderable. I'm having troubles.

To start, I am using a Datatable class from php artisan datatables:make

The basic structure is: Player PlayerStats

A player hasMany PlayerStats.

I want to list each player in a table, then get the SUM of stat1, stat2, stat3 etc next to them, and even an average of each stat, so columns would be like:

Name, Stat1 Total, Stat1 AVG, Stat2 Total, Stat2 AVG ...

I would like to be able to sort by these columns so I can see the leader of Stat1 etc.

Obviously the ordering stuff does not work as it can't apply the correct where / order to the related model. I also tried creatiing a manual array/struct and creating a collection, which gets me the datatable but im strugling to get the CollectionDataTable to a Html ->render() function and not sure where I am going wrong.

Any tips on how to tackle this issue on laravel side? My next step would be just returning the JSON and doing the datatable stuff in the view a bit more manually, but I'd like to avoid that if at all possible.

Thanks.

yajra commented 2 days ago

You can use withCount or withAvg in your query and then include it in the columns. Or use sub select query.

columns: [
  {data: 'stat1_total', searchable: false}
]