Right join

RagnarGrootKoerkamp · September 2016

We are currently using the following editor setup for the (php) backend:

We've got a table of As, which contain an Aid and Bid. This table is left-joined (using $editor->leftJoin()) to the B table, and the row data A.Aid, A.Ainfo, A.Bid, B.Binfo is returned to the client.

When the client changes a row, the Bid is submitted alongside the normal information, so that the php-editor instance knows which joined row to act on.
Row removal is handled by a preRemove event setting A.deleted to false and stopping any further processing.
The problem occurs when a new row is created:
Editor only created an A row, and leaves the B table as is. I need it to create the B row first, so that its Bid can be used for the creation of the A row.

I'm guessing that this would usually be called a right join. Would there be a nice solution to solve this problem, other then implementing right joins myself? I imagine the implementation would be quite similar to the implementation of left joins, except they are created before the parent row is created, and now removed after the parent row is removed.

Note on changing a row

Due to the nature of this construction, the data in a single B row may be returned multiple times to the client (this is what we want). When a single B row is edited, we will also send all other A rows that share this B row to the client, so that it will update all data accordingly. (This should be possible by modifying the _update function and _get function to use a list of ids, instead of only using a single id. But maybe it's easier to implement right joins in the core of the library, instead of using new random callbacks)

Edit: possible solution

I think I can solve this using a preCreate event to first create the B row and setting its id in the submitted data. That would require me to extract all B columns from the $values parameter and then building the query using a second editor instance. This has the drawback that all columns of B must be defined twice.

RagnarGrootKoerkamp · September 2016

I solved this by using the preCreate event to create a B row:

if(!$values['Bid']){
 $editor->on('preCreate', function($editor, $values){
  $Beditor = Editor::inst('B', 'Bid')->fields(..);
  $id = $Beditor->_insert_or_update(null, $values); // this re-uses the submitted values
  // The returned data overwrites the submitted data
  // (This requires some modifications in $editor->_trigger and the firing of the preEdit event.
  return $values;
 }
});

The client handles the duplicate data in the postSubmit event:

    editor.on('postSubmit', function(e, json, data, action){
        // for each returned row
        var rows = json.data;
        for(var i=0;i<rows.length; ++i){
            // find the B-data in this row
            var Bid = rows[i].Bid;
            var Aid = rows[i].Aid;
            var Brow = $.extend({},rows[i]);
            delete Brow.DT_RowId;
            delete Brow.Aid;
            delete Brow.Bid;

            // and update the sibling rows,
            // but now the row itself
            $.each(table.rows()[0], function(index, row){
                var tablerow = table.row(row);
                var olddata = tablerow.data();
                if(olddata.Bid === Bid &&
                    olddata.Aid !== Aid){
                    var newdata = $.extend(olddata, Brow);
                    tablerow.data(newdata);
                }
            });
        }
    });

Right join

Right join

Note on changing a row

Edit: possible solution

Answers

Howdy, Stranger!

Categories

DataTables

Right join

Right join

Note on changing a row

Edit: possible solution

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables