Multi times the same row

Maikel

hey,

i have the following php editor code, this works as expected, except that the table will display multiple times the same 'layers' row if there are multiple records in the plan_has_layer table.
This happens because of the left-joins i'm adding to be able to support the where field correctly.

any solution? or how can i solve this?

Is there a way to do the where without the 2 leftjoins? as this will solve it.

$ed = Editor::Inst($db, 'layers')
        ->fields(
            Field::inst('layers.id'),
            Field::inst('layers.sectie_id')
                ->options( 
                    Options::inst()
                        ->table( 'plannen' )
                        ->value( 'id' )
                        ->label( 'naam' )
                        ->where(function ($q) {                             
                              $q->where('project_id', PRJinfo['id'], '=');
                        })
                ),
            Field::inst('layers.name'),
            Field::inst('layers.tabblad'),
            Field::inst('layers.kleur'),
            Field::inst('layers.symbol_only'),
            Field::inst('layers.scale_factor'),
            Field::inst('layers.icon_id')
                ->options(
                    Options::inst()
                        ->table( 'icons' )
                        ->value( 'id' )
                        ->label( array('naam', 'class', 'rotate') )
                        ->render( function ( $row ) {
                            return '<span><i class="'.$row['class'].'" style="--fa-rotate-angle: '.$row['rotate'].'deg"></i>&nbsp;&nbsp;'.$row['naam'].'</span>';
                        } )
                ),
            Field::inst('icons.class'),
            Field::inst('icons.naam'),
            Field::inst('icons.rotate'),
            Field::inst('layers.document_id')
                ->options( 
                    Options::inst()
                        ->table( 'document' )
                        ->value( 'id' )
                        ->label( 'naam' )
                        ->where(function ($q) {                             
                              $q->where('project_id', PRJinfo['id'], '=');
                        })
                ),
            Field::inst('document.naam')
        )
        ->leftJoin( 'icons', 'icons.id', '=', 'layers.icon_id' )
        ->leftJoin( 'document', 'document.id', '=', 'layers.document_id' )
        ->leftJoin( 'plan_has_layer', 'plan_has_layer.layer_id', '=', 'layers.id')
        ->leftJoin( 'plannen', 'plan_has_layer.plan_id', '=', 'plannen.id')
        ->join(
            Mjoin::inst( 'plannen' )
                ->link( 'layers.id', 'plan_has_layer.layer_id' )
                ->link( 'plannen.id', 'plan_has_layer.plan_id' )
                ->order( 'name asc' )
                ->fields(
                    Field::inst( 'id' )
                        ->options( Options::inst()
                            ->table( 'plannen' )
                            ->value( 'id' )
                            ->label( 'naam' )
                            ->where(function ($q) {
                                $q->where('project_id', PRJinfo['id'], '=');
                            })
                        ),
                    Field::inst( 'naam' )
                )
                ->where('plannen.project_id', PRJinfo['id'])
        )
        ->where('plannen.project_id', PRJinfo['id'])
        ->validator( function ( $editor, $action, $data ) {
            if ( $action === Editor::ACTION_CREATE ) {
                $r = $editor->db()->raw()->exec('SELECT count(*) as num FROM layers INNER JOIN plan_has_layer ON plan_has_layer.layer_id=layers.id INNER JOIN plannen on plannen.id=plan_has_layer.plan_id WHERE plannen.project_id='.PRJinfo['id'])->fetch();
                if ( $r['num'] >= PRJinfo['maxLayers'] ) {
                    return 'Maximum aantal layers bereikt!';
                }
            }
        })
        ->debug(DEBUG)
        ->process($_POST)
        ->json();

allan

If you have DEBUG as true then the JSON response to the client-side will include the SQL that Editor is generating and running. That could be very useful to see, and then tweak for your needs. If you can find an SQL command that will suit, then we can probably figure out how to make it work in Editor.

I don't know much about your data, but if plannen.project_id is the limiting factor, but plan_has_layer can have multiple entries, which appears to be the case, then yes, in SQL that would result in multiple rows being returned.

Can you add a project_id to the layers table? That way you could do the condition on that.

Allan

Maikel

yeah i was thinking the same thing, if i add a new field this will be solved.