Datatables filterColumn doesnt seem to work-CodePudding

Having this code:

$campaigns = CampaignMembers::with(['company', 'note'])
    ->select(['*', DB::raw('count(*) as count')])
    ->where('campaign_id', $campaign_id)
    ->havingRaw('count = ?', [42])
    ->groupBy('customer_id');

return DataTables::of($campaigns)
    ->filterColumn('count', function($q, $kw) {           
       $q->select(['*', DB::raw('count(*) as count')])
           ->where('campaign_id', 8)
           ->havingRaw('count = ?', [42])
           ->groupBy('customer_id');         
    })               
    ->addColumn('show', '<a href="/showCampaignCompany/{{$customer_id}}"><i >visibility</i></a>')  
    ->addColumn('edit', '<a href="/editCustomer/{{$id}}"><i >edit</i></a>')
    ->addColumn('delete', '<a href="#" data-id="{{$id}}" ><i >delete</i></a>')                     
    ->rawColumns(['show', 'edit', 'delete']) 
    ->make(true);

Trying to make a count(*) result searchable, I have added a call to filterColumn() in order to perform that custom search since searching in an alias column is not supported. The problem is that it looks like that the filterColumn() result is simply ignored. Since I have hardcoded the count value, I expected to receive the same results all the time. But I instead receive new. filtered results. What do I miss?

Using the latest yajra datatables 9.19

Edit for some relationship info:

The CampaignMembers model has a customer_id. The customer table can be either a company or a physical person. It is not really important to talk about relationships since I group everything using customer_id that is a "local" column even if later, I fetch the name of the company or person through a relationship. The count is derived from the the CampaignMembers table. It counts how many times, the same customer_id is found. Simple.

CodePudding user response：

The filterColumn() method filters an existing object, so you don't want to do this in your initial query. In the filterColumn() callback you're provided access to the query object, which has already had the select(), where(), etc methods called so there's no need to do it again. You're also given the search term and it can be provided to the comparison function.

$campaigns = CampaignMembers::with(['company', 'note'])
    ->select(['*', DB::raw('count(*) as count')])
    ->where('campaign_id', $campaign_id)
    ->groupBy('customer_id');

return DataTables::of($campaigns)
    ->filterColumn('count', function($q, $kw) {           
       $q->having('count', $kw)
    })               
    ->addColumn('show', '<a href="/showCampaignCompany/{{$customer_id}}"><i >visibility</i></a>')  
    ->addColumn('edit', '<a href="/editCustomer/{{$id}}"><i >edit</i></a>')
    ->addColumn('delete', '<a href="#" data-id="{{$id}}" ><i >delete</i></a>')                     
    ->rawColumns(['show', 'edit', 'delete']) 
    ->make(true);

(As a matter of semantics, you should not be creating those HTML columns in your controller. Doing it client side keeps your presentation layer away from the application logic.)

CodePudding user response：

Well after 3 days, I managed to solve it. I concatenated the result into one column to make the table more clear. The solution feels very complicated but it works well. Certainly with a performance penalty:

First I created a custom attribute in the model:

protected $appends = ['customer_count'];

public function getCustomerCountAttribute()
{
   return $this->company->name . '(' .$this->where('customer_id', $this->customer_id)->count() . ')';
}

The name of the company resides in the relation so I did this in the javascript declaration of the table:

{data: 'customer_count', name: 'company.name'},

Then, in the controller, the function called by ajax looks like this:

$campaigns = CampaignMembers::with(['company', 'note'])
        ->where('campaign_id', $campaign_id)
        ->groupBy('customer_id')
        ->select('campaign_members.*');     
        
return DataTables::of($campaigns)   
->filterColumn('company.name', function($q, $kw) use($campaigns){
      $countIds = $campaigns->get()->filter(function($item) use ($kw) {
      if(Str::contains($item->customer_count, $kw))
         return $item->customer_count;
      })->pluck('id');

      $q->whereHas('company', function ($query) use ($kw) {
                    return $query->where('name', 'LIKE', "%$kw%");
      })->orWhereIn('campaign_members.id', $countIds); 
 })->toJson();

This way, even if the search partialy matches a count number or the name of the company, both with return results.