add column on a nested relationship

Question

Is it possible to add a column on a nested relationship ?

For example, i have the following tables

groups
- id
- title

categories
- id
- title
- parent_id
- group_id

threads
- id
- title
- replies_count
- category_id

I want to do the following

class GroupController extends Controller {

public function index(){
return Groups::with(['categories'])->get()
}

class Category extends Model{

protected $withCount = ['threads'];
}

Therefore, when i eager-load the 'categories' i also eager-load each the threads associated with each category. But i want also to add a new column on the category model, with the some of the replies_count, the same as withCount.

For example

groups => [
"id" => 1,
"title" => "some-title"
"categories" => [
 "id" => 1,
 "title" => "category-title",
 "threads_count" => 2,
 "replies_count_sum" => 5
  ]
]

In the example above, the group with id=1 has a category associated with it, and the category has 2 threads associated with it and the total number of replies on these 2 threads are 5

Answer 1

you should be able to do it using withCount and sub Query Join

$values=Group::with(['categories'=>function($query)use(
{
$repliesSumCountForCategory=Threads::selectRaw('category_id,sum(replies_count) as replies_count_sum')->groupBy('category_id');

   $query->withCount('threads');
   $query->join($repliesSumCountForCategory,'replies_sum_count_for_category',
function($join)
{
$join->on('categories.id','=','replies_sum_count_for_category.category_id')
}
$query->addSelect('replies_sum_count_for_category.replies_count_sum');
}
])->get();

i did not get the chance to test it, please let me know if it helps ...