Read only Distinct Values from serverside

Khalid Teli

I want to only display distinct values in my table(two columns) on client side. :

The data that gets displayed atm looks like this:

# Column1 Column2
Car 1oct '19 - 30sep '20
Car 1oct '19 - 30sep '20
Car 1oct '19 - 30sep '20
Bus 1Nov'19 - 31Oct'20
Bus 1Nov'19 - 31Oct'20
Bus 1Nov'19 - 31Oct'20

Is it possible to display only distinct values, so my table looks like this?

Car 1oct '19 - 30sep '20
Bus 1Nov'19 - 31Oct'20

Is this achievable without using RAW SQL query and select distinct values?

Thank you

colin

The simplest way, as you say, would be on the server with the SQL. An alternative, would be to remove the duplicate rows in ajax.dataSrc. The last example on that page shows how the data can be manipulated, you can do this to remove rows from the returned data too,

Colin

Khalid Teli

@colin
Thank you. But is it possible to use Distinct in Editor Field on server-side?
I was following this examp,e but no luck so far :
https://datatables.net/forums/discussion/55145/select-distinct

As I mentioned if I use, RAW SQL , it is quite achievable but can this be done using a DISTINCT query inside Editor field , something like this:

Field::inst(‘DISTINCT’, ‘XXX’)
              ->set(false)

would it be better doing it from server-side rather than using ajax.dataSrc option?

Khalid Teli

@colin Thank you, I did it using ajax.dataSrc option

  dataSrc: function(json) {
       var Vehicles = [];
       return json.data.filter(function(item) {
         if (!~Vehicles.indexOf(item.vehicle_type)) {
           Vehicles.push(item.vehicle_type);
           return item;
         }
       })
    }

However, I am not sure if it is a best practice to do it from client side rather than server-side?

allan

The reason we don't provide a distinct option with the Editor PHP class is that each row must be distinct on account of having its own ID. Without a way to uniquely identify each row, you can't uniquely edit it.

So I feel there is something else going on here - I'm not sure what your goal is. Is this just a readonly table? Or are you trying to edit in groups?

Allan

Khalid Teli

@allan
Absolutely, that makes perfect sense.

It is just read-only table. I am not trying to edit anything here. Just read the distinct values from database table and display them using DataTables.

As of now, I used VIEW in database and read that using Datatables. I was just wondering if we could use something on Server-side editor fields to display distinct values.

Thank you

allan

Do you mean distinct rows (rather than a value per column)? The only way to do that would be with a VIEW as you say.

Allan

Khalid Teli

@allan Sorry for late reply. Yes, I meant distinct rows and I have done that using VIEW . Many thanks