phplaravelyajra-datatable

Datatables filterColumn doesnt seem to work


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 class="material-icons">visibility</i></a>')  
    ->addColumn('edit', '<a href="/editCustomer/{{$id}}"><i class="material-icons">edit</i></a>')
    ->addColumn('delete', '<a href="#" data-id="{{$id}}" class="deleteTemplate"><i class="material-icons">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.


Solution

  • 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.