Again: SELECT DISTINCT

rf1234

@allan, SELECT DISTINCT has been discussed multiple times in various threads. Recently in this one: https://datatables.net/forums/discussion/55145/select-distinct#latest

I think there are situations in which you really need this. I have a fairly complex join in this Editor instance. And no matter what I do - and I really tried everything. It's not possible to specify the join and / or the where clause in a way that the results are unique. My work around is the "PHP SELECT DISTINCT" in the "postGet" event handler. It's ok, it works and I can live with it.

I think it would be great if Editor could do a SELECT DISTINCT. Why would it hurt for editing? I mean you have the row id ?!

Here is my code which makes something from my change log legible for my users. This code works but it will produce multiple identical rows without the '"postGet" solution.

Editor::inst( $db, 'log' )
    ->field(
        Field::inst( 'log.id' )->set( false ),
        Field::inst( 'log.user_id AS log.changer' )->set( false )
            ->getFormatter( function($val, $data, $opts) {
                return getUserName($val);
            }), 
        Field::inst( 'log.values AS log.affected_user' )->set( false )
            ->getFormatter( function($val, $data, $opts) {
                $row = json_decode($val, true);
                return getUserName($row["userId"]);
            }),     
        Field::inst( 'log.action' )->set( false ),
        Field::inst( 'log.values AS log.user_role' )->set( false )
            ->getFormatter( function($val, $data, $opts) {
                $row = json_decode($val, true);
                return $row["ctr_govdept_has_user"]["role"];
            }), 
        Field::inst( 'log.values AS log.user_dept' )->set( false )
            ->getFormatter( function($val, $data, $opts) {
                $row = json_decode($val, true);
                $govNameStr = '';
                foreach ($row["gov"] as $gn) {
                    $govNameStr .= ( $gn["govName"] . ', ' );
                }
                $govNameStr = substr($govNameStr, 0, -2);
                return $row["ctr_govdept"]["dept_name"] . ' (' . 
                       $govNameStr . ')';
            }),                 
        Field::inst( 'log.when as log.update_time' )->set( false )
    )
    ->leftJoin( 'ctr_govdept_has_user_complete', 'log.user_id', '=', 'ctr_govdept_has_user_complete.user_id' )
    ->leftJoin( 'ctr_govdept_has_ctr_installation', 'ctr_govdept_has_user_complete.ctr_govdept_id', '=', 'ctr_govdept_has_ctr_installation.ctr_govdept_id' )
    ->leftJoin( 'ctr_installation_has_principal', 'ctr_govdept_has_ctr_installation.ctr_installation_id', '=', 'ctr_installation_has_principal.ctr_installation_id' )
    ->where( function($q) {
        $q ->where( 'ctr_installation_has_principal.user_id', $_SESSION['id'] );
        $q ->where( 'log.table', 'ctr_govdept_has_user' );
    })
    ->on( 'postGet', function ( $e, &$data, $id ){ 
       $data = array_values(array_unique($data, SORT_REGULAR));
    })
    ->process($_POST)
    ->json();

allan

I mean you have the row id ?!

And for that reason how would you do a select distinct? The row id is unique per row, so it is already distinct surely?

Allan

rf1234

This is probably a misunderstanding: If I don't use DISTINCT (or my PHP work around in Editor) native SQL and Editor will return 25 rows instead of 5 because it is going to be 5 times the exact same row with five time the exact same id (in my case it is the field log.id).

Of course the id itself is unique, but if you have complex joins it can be impossible to make MySql return unique values without using DISTINCT.

rf1234

Well, probably the log table is a very special case: It is a table that has most of the information in a container you cannot join with. It is the field "values" that contains the JSON encoded log values. That probably causes the duplicates here: You can't do a join that specifies all the key / foreign key relationships properly. Hence the duplicates probably. Checked a couple of stack overflow posts and they seemed to confirm this view. In addition: I've never had this problem with any other table and I have about 100 in my application. So I assume (even though I can't say I fully understand this) that the "container thing" is the cause of the issue.

allan

Thanks for the explanation - yes there are cases where joins can effectively result in duplicate rows (although duplicate in the sense only that not all the data is being selected, thus they look identical).

In that specific case using ->distinct() might be desirable, and your workaround for that is a good one. The only other option would be to change the query Editor uses to add the ->distinct() call.

I'm not yet convinced in my head that's going to be something that would be overall useful to add to Editor, or will just cause more harm than good.

I'll keep this discussion open to see if we get more feedback.

Allan

rf1234

This is my last one on this ... I think it would be a useful feature. But for CRUD it would probably only work if you have tables with a unique (incremental) id, which is the industry standard. Somehow you would need to make sure that this is guaranteed in a data table that uses "distinct".

Roland