Laravel-datatables: Duplicate results when filtering by one-to-many relation

[DonovanChan]

Summary of problem or feature request

I'm receiving duplicate rows when filtering on a column with a belongsToMany relation.

I have a table of Materials. Each Material can have one or more Geos. The table is processed server-side. I have a column filter on the Geo names.

Code snippet of problem

// Relation in Material.php

/**
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function geos()
{
    return $this->belongsToMany(Geo::class)->orderBy('name');
}

// Excerpt from request data for column being filtered

{
  input: {
    columns: [
      {
        data: "geo_names"
        name: "geos.name"
        orderable: "true"
        search: {value: "AMR", regex: "false"}
        searchable: "true"
      }
    ]
}

// Controller

public function index()
{
    $model = Material::with([
            'geos',
            'product',
            'product.types',
            'vendor'
        ])
        ->selectRaw('distinct materials.*');

    return DataTables::eloquent($model)
        ->toJson();
}

This results in the following query:

select distinct materials.*, `geos`.`name` from `materials` left join `geo_material` on `geo_material`.`material_id` = `materials`.`id` left join `geos` on `geo_material`.`geo_id` = `geos`.`id` where exists (select * from `geos` inner join `geo_material` on `geos`.`id` = `geo_material`.`geo_id` where `materials`.`id` = `geo_material`.`material_id` and LOWER(`geos`.`name`) LIKE "%%amr%%" and `geos`.`deleted_at` is null);

Removing geos.name from the select clause would probably fix this, but I'm not sure how to do that via the package API.

System details

• GNU/Linux running in Docker on MacOS 10.13.6
• PHP 7.1.21
• Laravel 5.7.13
• Laravel-Datatables-Oracle v8.13.1

[DonovanChan]

I've resolved this for now by customizing the column filter in my controller:

public function index()
{
    $model = Material::withTableRelations()
        ->selectRaw('materials.*');
    $datatable = DataTables::eloquent($model);

    if (!empty($datatable->request->columnKeyword(4))) {
        $datatable
            ->filterColumn('geos.name', function ($query, $keyword) {
                $query->whereHas('geos', function ($query) use ($keyword) {
                    $query->where('geo_material.geo_id', filter_var($keyword, FILTER_SANITIZE_NUMBER_INT));
                });
            });
    }

    return $datatable->toJson();
}

Now distinct isn't required in the query. That actually caused additional problems when attempting to sort the records.