       1: <?php
       2: /**
       3:  * DataTables PHP libraries.
       4:  *
       5:  * PHP libraries for DataTables and DataTables Editor, utilising PHP 5.3+.
       6:  *
       7:  *  @author    SpryMedia
       8:  *  @copyright 2012 SpryMedia ( http://sprymedia.co.uk )
       9:  *  @license   http://editor.datatables.net/license DataTables Editor
      10:  *  @link      http://editor.datatables.net
      11:  */
      12:
      13: namespace DataTables\Editor;
      14: if (!defined('DATATABLES')) exit();
      15:
      16: use
      17:     DataTables,
      18:     DataTables\Editor,
      19:     DataTables\Editor\Field;
      20:
      21:
      22: /**
      23:  * Join table class for DataTables Editor.
      24:  *
      25:  * The Join class can be used with {@link Editor::join} to allow Editor to
      26:  * obtain joined information from the database.
      27:  *
      28:  * For an overview of how Join tables work, please refer to the
      29:  * {@link https://editor.datatables.net/manual/php/ Editor manual} as it is
      30:  * useful to understand how this class represents the links between tables,
      31:  * before fully getting to grips with it's API.
      32:  *
      33:  *  @example
      34:  *    Join the parent table (the one specified in the {@link Editor::table}
      35:  *    method) with the table *access*, with a link table *user__access*, which
      36:  *    allows multiple properties to be found for each row in the parent table.
      37:  *    <code>
      38:  *      Join::inst( 'access', 'array' )
      39:  *          ->link( 'users.id', 'user_access.user_id' )
      40:  *          ->link( 'access.id', 'user_access.access_id' )
      41:  *          ->field(
      42:  *              Field::inst( 'id' )->validator( 'Validate::required' ),
      43:  *              Field::inst( 'name' )
      44:  *          )
      45:  *    </code>
      46:  *
      47:  *  @example
      48:  *    Single row join - here we join the parent table with a single row in
      49:  *    the child table, without an intermediate link table. In this case the
      50:  *    child table is called *extra* and the two fields give the columns that
      51:  *    Editor will read from that table.
      52:  *    <code>
      53:  *        Join::inst( 'extra', 'object' )
      54:  *            ->link( 'user.id', 'extra.user_id' )
      55:  *            ->field(
      56:  *                Field::inst( 'comments' ),
      57:  *                Field::inst( 'review' )
      58:  *            )
      59:  *    </code>
      60:  */
      61: class Join extends DataTables\Ext {
      62:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
      63:      * Constructor
      64:      */
      65:
      66:     /**
      67:      * Join instance constructor.
      68:      *  @param string $table Table name to get the joined data from.
      69:      *  @param string $type Work with a single result ('object') or an array of
      70:      *    results ('array') for the join.
      71:      */
      72:     function __construct( $table=null, $type='object' )
      73:     {
      74:         $this->table( $table );
      75:         $this->type( $type );
      76:     }
      77:
      78:
      79:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
      80:      * Private properties
      81:      */
      82:
      83:     /** @var DataTables\Editor\Field[] */
      84:     private $_fields = array();
      85:
      86:     /** @var array */
      87:     private $_join = array(
      88:         "parent" => null,
      89:         "child" => null,
      90:         "table" => null
      91:     );
      92:
      93:     /** @var string */
      94:     private $_table = null;
      95:
      96:     /** @var string */
      97:     private $_type = null;
      98:
      99:     /** @var string */
     100:     private $_name = null;
     101:
     102:     /** @var boolean */
     103:     private $_get = true;
     104:
     105:     /** @var boolean */
     106:     private $_set = true;
     107:
     108:     /** @var string */
     109:     private $_aliasParentTable = null;
     110:
     111:     /** @var array */
     112:     private $_where = array();
     113:
     114:     /** @var boolean */
     115:     private $_whereSet = false;
     116:
     117:     /** @var array */
     118:     private $_links = array();
     119:
     120:     /** @var string */
     121:     private $_customOrder = null;
     122:
     123:     /** @var callable[] */
     124:     private $_validators = array();
     125:
     126:
     127:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     128:      * Public methods
     129:      */
     130:
     131:     /**
     132:      * Get / set parent table alias.
     133:      *
     134:      * When working with a self referencing table (i.e. a column in the table contains
     135:      * a primary key value from its own table) it can be useful to set an alias on the
     136:      * parent table's name, allowing a self referencing Join. For example:
     137:      *   <code>
     138:      *   SELECT p2.publisher
     139:      *   FROM   publisher as p2
     140:      *   JOIN   publisher on (publisher.idPublisher = p2.idPublisher)
     141:      *   <code>
     142:      * Where, in this case, `publisher` is the table that is used by the Editor instance,
     143:      * and you want to use `Join` to link back to the table (resolving a name for example).
     144:      * This method allows that alias to be set. Fields can then use standard SQL notation
     145:      * to select a field, for example `p2.publisher` or `publisher.publisher`.
     146:      *  @param string $_ Table alias to use
     147:      *  @return string|self Table alias set (which is null by default), or self if used as
     148:      *    a setter.
     149:      */
     150:     public function aliasParentTable ( $_=null )
     151:     {
     152:         return $this->_getSet( $this->_aliasParentTable, $_ );
     153:     }
     154:
     155:
     156:     /**
     157:      * Get / set field instances.
     158:      *
     159:      * The list of fields designates which columns in the table that will be read
     160:      * from the joined table.
     161:      *  @param Field $_... Instances of the {@link Field} class, given as a single
     162:      *    instance of {@link Field}, an array of {@link Field} instances, or multiple
     163:      *    {@link Field} instance parameters for the function.
     164:      *  @return Field[]|self Array of fields, or self if used as a setter.
     165:      *  @see {@link Field} for field documentation.
     166:      */
     167:     public function field ( $_=null )
     168:     {
     169:         if ( $_ !== null && !is_array($_) ) {
     170:             $_ = func_get_args();
     171:         }
     172:         return $this->_getSet( $this->_fields, $_, true );
     173:     }
     174:
     175:
     176:     /**
     177:      * Get / set field instances.
     178:      *
     179:      * An alias of {@link field}, for convenience.
     180:      *  @param Field $_... Instances of the {@link Field} class, given as a single
     181:      *    instance of {@link Field}, an array of {@link Field} instances, or multiple
     182:      *    {@link Field} instance parameters for the function.
     183:      *  @return Field[]|self Array of fields, or self if used as a setter.
     184:      *  @see {@link Field} for field documentation.
     185:      */
     186:     public function fields ( $_=null )
     187:     {
     188:         if ( $_ !== null && !is_array($_) ) {
     189:             $_ = func_get_args();
     190:         }
     191:         return $this->_getSet( $this->_fields, $_, true );
     192:     }
     193:
     194:
     195:     /**
     196:      * Get / set get attribute.
     197:      *
     198:      * When set to false no read operations will occur on the join tables.
     199:      *  @param boolean $_ Value
     200:      *  @return boolean|self Name
     201:      */
     202:     public function get ( $_=null )
     203:     {
     204:         return $this->_getSet( $this->_get, $_ );
     205:     }
     206:
     207:
     208:     /**
     209:      * Get / set join properties.
     210:      *
     211:      * Define how the SQL will be performed, on what columns. There are
     212:      * basically two types of join that are supported by Editor here, a direct
     213:      * foreign key reference in the join table to the parent table's primary
     214:      * key, or a link table that contains just primary keys for the parent and
     215:      * child tables (this approach is usually used with a {@link type} of
     216:      * 'array' since you can often have multiple links between the two tables,
     217:      * while a direct foreign key reference will typically use a type of
     218:      * 'object' (i.e. a single entry).
     219:      *
     220:      *  @param string|string[] $parent Parent table's primary key names. If used
     221:      *    with a link table (i.e. third parameter to this method is given, then
     222:      *    an array should be used, with the first element being the pkey's name
     223:      *    in the parent table, and the second element being the key's name in
     224:      *    the link table.
     225:      *  @param string|string[] $child Child table's primary key names. If used
     226:      *    with a link table (i.e. third parameter to this method is given, then
     227:      *    an array should be used, with the first element being the pkey's name
     228:      *    in the child table, and the second element being the key's name in the
     229:      *    link table.
     230:      *  @param string $table Join table name, if using a link table
     231:      *  @returns Join This for chaining
     232:      *  @deprecated 1.5 Please use the {@link link} method rather than this
     233:      *      method now.
     234:      */
     235:     public function join ( $parent=null, $child=null, $table=null )
     236:     {
     237:         if ( $parent === null && $child === null ) {
     238:             return $this->_join;
     239:         }
     240:
     241:         $this->_join['parent'] = $parent;
     242:         $this->_join['child'] = $child;
     243:         $this->_join['table'] = $table;
     244:         return $this;
     245:     }
     246:
     247:
     248:     /**
     249:      * Create a join link between two tables. The order of the fields does not
     250:      * matter, but each field must contain the table name as well as the field
     251:      * name.
     252:      *
     253:      * This method can be called a maximum of two times for an Mjoin instance:
     254:      *
     255:      * * First time, creates a link between the Editor host table and a join
     256:      *   table
     257:      * * Second time creates the links required for a link table.
     258:      *
     259:      * Please refer to the Editor Mjoin documentation for further details:
     260:      * https://editor.datatables.net/manual/php
     261:      *
     262:      * @param  string $field1 Table and field name
     263:      * @param  string $field2 Table and field name
     264:      * @return Join Self for chaining
     265:      * @throws \Exception Link limitations
     266:      */
     267:     public function link ( $field1, $field2 )
     268:     {
     269:         if ( strpos($field1, '.') === false || strpos($field2, '.') === false ) {
     270:             throw new \Exception("Link fields must contain both the table name and the column name");
     271:         }
     272:
     273:         if ( count( $this->_links ) >= 4 ) {
     274:             throw new \Exception("Link method cannot be called more than twice for a single instance");
     275:         }
     276:
     277:         $this->_links[] = $field1;
     278:         $this->_links[] = $field2;
     279:
     280:         return $this;
     281:     }
     282:
     283:
     284:     /**
     285:      * Specify the property that the data will be sorted by.
     286:      *
     287:      * @param  string $order SQL column name to order the data by
     288:      * @return Join Self for chaining
     289:      */
     290:     public function order ( $_=null )
     291:     {
     292:         // How this works is by setting the SQL order by clause, and since the
     293:         // join that is done in PHP is always done sequentially, the order is
     294:         // retained.
     295:         return $this->_getSet( $this->_customOrder, $_ );
     296:     }
     297:
     298:
     299:     /**
     300:      * Get / set name.
     301:      *
     302:      * The `name` of the Join is the JSON property key that is used when
     303:      * 'getting' the data, and the HTTP property key (in a JSON style) when
     304:      * 'setting' data. Typically the name of the db table will be used here,
     305:      * but this method allows that to be overridden.
     306:      *  @param string $_ Field name
     307:      *  @return String|self Name
     308:      */
     309:     public function name ( $_=null )
     310:     {
     311:         return $this->_getSet( $this->_name, $_ );
     312:     }
     313:
     314:
     315:     /**
     316:      * Get / set set attribute.
     317:      *
     318:      * When set to false no write operations will occur on the join tables.
     319:      * This can be useful when you want to display information which is joined,
     320:      * but want to only perform write operations on the parent table.
     321:      *  @param boolean $_ Value
     322:      *  @return boolean|self Name
     323:      */
     324:     public function set ( $_=null )
     325:     {
     326:         return $this->_getSet( $this->_set, $_ );
     327:     }
     328:
     329:
     330:     /**
     331:      * Get / set join table name.
     332:      *
     333:      * Please note that this will also set the {@link name} used by the Join
     334:      * as well. This is for convenience as the JSON output / HTTP input will
     335:      * typically use the same name as the database name. If you want to set a
     336:      * custom name, the {@link name} method must be called ***after*** this one.
     337:      *  @param string $_ Name of the table to read the join data from
     338:      *  @return String|self Name of the join table, or self if used as a setter.
     339:      */
     340:     public function table ( $_=null )
     341:     {
     342:         if ( $_ !== null ) {
     343:             $this->_name = $_;
     344:         }
     345:         return $this->_getSet( $this->_table, $_ );
     346:     }
     347:
     348:
     349:     /**
     350:      * Get / set the join type.
     351:      *
     352:      * The join type allows the data that is returned from the join to be given
     353:      * as an array (i.e. working with multiple possibly results for each record from
     354:      * the parent table), or as an object (i.e. working which one and only one result
     355:      * for each record form the parent table).
     356:      *  @param string $_ Join type ('object') or an array of
     357:      *    results ('array') for the join.
     358:      *  @return String|self Join type, or self if used as a setter.
     359:      */
     360:     public function type ( $_=null )
     361:     {
     362:         return $this->_getSet( $this->_type, $_ );
     363:     }
     364:
     365:
     366:     /**
     367:      * Set a validator for the array of data (not on a field basis)
     368:      *
     369:      * @param string $fieldName Name of the field that any error should be shown
     370:      *   against on the client-side
     371:      * @param callable $fn Callback function for validation
     372:      * @return self Chainable
     373:      */
     374:     public function validator ( $fieldName, $fn )
     375:     {
     376:         $this->_validators[] = array(
     377:             'fieldName' => $fieldName,
     378:             'fn' => $fn
     379:         );
     380:
     381:         return $this;
     382:     }
     383:
     384:
     385:     /**
     386:      * Where condition to add to the query used to get data from the database.
     387:      * Note that this is applied to the child table.
     388:      *
     389:      * Can be used in two different ways:
     390:      *
     391:      * * Simple case: `where( field, value, operator )`
     392:      * * Complex: `where( fn )`
     393:      *
     394:      *  @param string|callable $key   Single field name or a closure function
     395:      *  @param string|string[] $value Single field value, or an array of values.
     396:      *  @param string          $op    Condition operator: <, >, = etc
     397:      *  @return string[]|self Where condition array, or self if used as a setter.
     398:      */
     399:     public function where ( $key=null, $value=null, $op='=' )
     400:     {
     401:         if ( $key === null ) {
     402:             return $this->_where;
     403:         }
     404:
     405:         if ( is_callable($key) && is_object($key) ) {
     406:             $this->_where[] = $key;
     407:         }
     408:         else {
     409:             $this->_where[] = array(
     410:                 "key"   => $key,
     411:                 "value" => $value,
     412:                 "op"    => $op
     413:             );
     414:         }
     415:
     416:         return $this;
     417:     }
     418:
     419:
     420:     /**
     421:      * Get / set if the WHERE conditions should be included in the create and
     422:      * edit actions.
     423:      *
     424:      * This means that the fields which have been used as part of the 'get'
     425:      * WHERE condition (using the `where()` method) will be set as the values
     426:      * given.
     427:      *
     428:      * This is default false (i.e. they are not included).
     429:      *
     430:      *  @param boolean $_ Include (`true`), or not (`false`)
     431:      *  @return boolean Current value
     432:      */
     433:     public function whereSet ( $_=null )
     434:     {
     435:         return $this->_getSet( $this->_whereSet, $_ );
     436:     }
     437:
     438:
     439:
     440:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     441:      * Internal methods
     442:      */
     443:
     444:     /**
     445:      * Get data
     446:      *  @param Editor $editor Host Editor instance
     447:      *  @param string[] $data Data from the parent table's get and were we need
     448:      *    to add out output.
     449:      *  @param array $options options array for fields
     450:      *  @internal
     451:      */
     452:     public function data( $editor, &$data, &$options )
     453:     {
     454:         if ( ! $this->_get ) {
     455:             return;
     456:         }
     457:
     458:         $this->_prep( $editor );
     459:         $db       = $editor->db();
     460:         $dteTable = $editor->table();
     461:         $pkey     = $editor->pkey();
     462:         $idPrefix = $editor->idPrefix();
     463:
     464:         $dteTable = $dteTable[0];
     465:         if ( strpos($dteTable, ' as ') !== false ) {
     466:             $arr = explode(' as ', $dteTable);
     467:             $dteTable = $arr[0];
     468:             $this->_aliasParentTable = $arr[1];
     469:         }
     470:
     471:         $dteTableLocal = $this->_aliasParentTable ? // Can be aliased to allow a self join
     472:             $this->_aliasParentTable :
     473:             $dteTable;
     474:         $joinField = isset($this->_join['table']) ?
     475:             $this->_join['parent'][0] :
     476:             $this->_join['parent'];
     477:
     478:         // This is something that will likely come in a future version, but it
     479:         // is a relatively low use feature. Please get in touch if this is
     480:         // something you require.
     481:         if ( count( $pkey ) > 1 ) {
     482:             throw new \Exception("MJoin is not currently supported with a compound primary key for the main table", 1);
     483:         }
     484:
     485:         if ( count($data) > 0 ) {
     486:             $pkey = $pkey[0];
     487:             $pkeyIsJoin = $pkey === $joinField || $pkey === $dteTableLocal.'.'.$joinField;
     488:
     489:             // Sanity check that table selector fields are read only, and have an name without
     490:             // a dot (for DataTables mData to be able to read it)
     491:             for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
     492:                 $field = $this->_fields[$i];
     493:
     494:                 if ( strpos( $field->dbField() , "." ) !== false ) {
     495:                     if ( $field->set() !== Field::SET_NONE && $this->_set ) {
     496:                         echo json_encode( array(
     497:                             "sError" => "Table selected fields (i.e. '{table}.{column}') in `Join` ".
     498:                                 "must be read only. Use `set(false)` for the field to disable writing."
     499:                         ) );
     500:                         exit(0);
     501:                     }
     502:
     503:                     if ( strpos( $field->name() , "." ) !== false ) {
     504:                         echo json_encode( array(
     505:                             "sError" => "Table selected fields (i.e. '{table}.{column}') in `Join` ".
     506:                                 "must have a name alias which does not contain a period ('.'). Use ".
     507:                                 "name('---') to set a name for the field"
     508:                         ) );
     509:                         exit(0);
     510:                     }
     511:                 }
     512:             }
     513:
     514:             // Set up the JOIN query
     515:             $stmt = $db
     516:                 ->query( 'select' )
     517:                 ->distinct( true )
     518:                 ->get( $dteTableLocal.'.'.$joinField.' as dteditor_pkey' )
     519:                 ->get( $this->_fields('get') )
     520:                 ->table( $dteTable .' as '. $dteTableLocal );
     521:
     522:             if ( $this->order() ) {
     523:                 $stmt->order( $this->order() );
     524:             }
     525:
     526:             $this->_apply_where( $stmt );
     527:
     528:             if ( isset($this->_join['table']) ) {
     529:                 // Working with a link table
     530:                 $stmt
     531:                     ->join(
     532:                         $this->_join['table'],
     533:                         $dteTableLocal.'.'.$this->_join['parent'][0] .' = '. $this->_join['table'].'.'.$this->_join['parent'][1]
     534:                     )
     535:                     ->join(
     536:                         $this->_table,
     537:                         $this->_table.'.'.$this->_join['child'][0] .' = '. $this->_join['table'].'.'.$this->_join['child'][1]
     538:                     );
     539:             }
     540:             else {
     541:                 // No link table in the middle
     542:                 $stmt
     543:                     ->join(
     544:                         $this->_table,
     545:                         $this->_table.'.'.$this->_join['child'] .' = '. $dteTableLocal.'.'.$this->_join['parent']
     546:                     );
     547:             }
     548:
     549:             // Check that the joining field is available.  The joining key can
     550:             // come from the Editor instance's primary key, or any other field,
     551:             // including a nested value (from a left join). If the instance's
     552:             // pkey, then we've got that in the DT_RowId parameter, so we can
     553:             // use that. Otherwise, the key must be in the field list.
     554:             if ( $this->_propExists( $dteTable.'.'.$joinField, $data[0] ) ) {
     555:                 $readField = $dteTable.'.'.$joinField;
     556:             }
     557:             else if ( $this->_propExists( $joinField, $data[0] ) ) {
     558:                 $readField = $joinField;
     559:             }
     560:             else if ( ! $pkeyIsJoin ) {
     561:                 echo json_encode( array(
     562:                     "sError" => "Join was performed on the field '{$joinField}' which was not "
     563:                         ."included in the Editor field list. The join field must be included "
     564:                         ."as a regular field in the Editor instance."
     565:                 ) );
     566:                 exit(0);
     567:             }
     568:
     569:             // Get list of pkey values and apply as a WHERE IN condition
     570:             // This is primarily useful in server-side processing mode and when filtering
     571:             // the table as it means only a sub-set will be selected
     572:             // This is only applied for "sensible" data sets. It will just complicate
     573:             // matters for really large data sets:
     574:             // https://stackoverflow.com/questions/21178390/in-clause-limitation-in-sql-server
     575:             if ( count($data) < 1000 ) {
     576:                 $whereIn = array();
     577:
     578:                 for ( $i=0 ; $i<count($data) ; $i++ ) {
     579:                     $whereIn[] = $pkeyIsJoin ?
     580:                         str_replace( $idPrefix, '', $data[$i]['DT_RowId'] ) :
     581:                         $this->_readProp( $readField, $data[$i] );
     582:                 }
     583:
     584:                 $stmt->where_in( $dteTableLocal.'.'.$joinField, $whereIn );
     585:             }
     586:
     587:             // Go!
     588:             $res = $stmt->exec();
     589:             if ( ! $res ) {
     590:                 return;
     591:             }
     592:
     593:             // Map to primary key for fast lookup
     594:             $join = array();
     595:             while ( $row=$res->fetch() ) {
     596:                 $inner = array();
     597:
     598:                 for ( $j=0 ; $j<count($this->_fields) ; $j++ ) {
     599:                     $field = $this->_fields[$j];
     600:                     if ( $field->apply('get') ) {
     601:                         $inner[ $field->name() ] = $field->val('get', $row);
     602:                     }
     603:                 }
     604:
     605:                 if ( $this->_type === 'object' ) {
     606:                     $join[ $row['dteditor_pkey'] ] = $inner;
     607:                 }
     608:                 else {
     609:                     if ( !isset( $join[ $row['dteditor_pkey'] ] ) ) {
     610:                         $join[ $row['dteditor_pkey'] ] = array();
     611:                     }
     612:                     $join[ $row['dteditor_pkey'] ][] = $inner;
     613:                 }
     614:             }
     615:
     616:             // Loop over the data and do a join based on the data available
     617:             for ( $i=0 ; $i<count($data) ; $i++ ) {
     618:                 $rowPKey = $pkeyIsJoin ?
     619:                     str_replace( $idPrefix, '', $data[$i]['DT_RowId'] ) :
     620:                     $this->_readProp( $readField, $data[$i] );
     621:
     622:                 if ( isset( $join[$rowPKey] ) ) {
     623:                     $data[$i][ $this->_name ] = $join[$rowPKey];
     624:                 }
     625:                 else {
     626:                     $data[$i][ $this->_name ] = ($this->_type === 'object') ?
     627:                         (object)array() : array();
     628:                 }
     629:             }
     630:         }
     631:
     632:         // Field options
     633:         foreach ($this->_fields as $field) {
     634:             $opts = $field->optionsExec( $db );
     635:
     636:             if ( $opts !== false ) {
     637:                 $name = $this->name().
     638:                     ($this->_type === 'object' ? '.' : '[].').
     639:                     $field->name();
     640:                 $options[ $name ] = $opts;
     641:             }
     642:         }
     643:     }
     644:
     645:
     646:     /**
     647:      * Create a row.
     648:      *  @param Editor $editor Host Editor instance
     649:      *  @param int $parentId Parent row's primary key value
     650:      *  @param string[] $data Data to be set for the join
     651:      *  @internal
     652:      */
     653:     public function create ( $editor, $parentId, $data )
     654:     {
     655:         // If not settable, or the many count for the join was not submitted
     656:         // there we do nothing
     657:         if (
     658:             ! $this->_set ||
     659:             ! isset($data[$this->_name]) ||
     660:             ! isset($data[$this->_name.'-many-count'])
     661:         ) {
     662:             return;
     663:         }
     664:
     665:         $this->_prep( $editor );
     666:         $db = $editor->db();
     667:
     668:         if ( $this->_type === 'object' ) {
     669:             $this->_insert( $db, $parentId, $data[$this->_name] );
     670:         }
     671:         else {
     672:             for ( $i=0 ; $i<count($data[$this->_name]) ; $i++ ) {
     673:                 $this->_insert( $db, $parentId, $data[$this->_name][$i] );
     674:             }
     675:         }
     676:     }
     677:
     678:
     679:     /**
     680:      * Update a row.
     681:      *  @param Editor $editor Host Editor instance
     682:      *  @param int $parentId Parent row's primary key value
     683:      *  @param string[] $data Data to be set for the join
     684:      *  @internal
     685:      */
     686:     public function update ( $editor, $parentId, $data )
     687:     {
     688:         // If not settable, or the many count for the join was not submitted
     689:         // there we do nothing
     690:         if ( ! $this->_set || ! isset($data[$this->_name.'-many-count']) ) {
     691:             return;
     692:         }
     693:
     694:         $this->_prep( $editor );
     695:         $db = $editor->db();
     696:
     697:         if ( $this->_type === 'object' ) {
     698:             // update or insert
     699:             $this->_update_row( $db, $parentId, $data[$this->_name] );
     700:         }
     701:         else {
     702:             // WARNING - this will remove rows and then readd them. Any
     703:             // data not in the field list WILL BE LOST
     704:             // todo - is there a better way of doing this?
     705:             $this->remove( $editor, array($parentId) );
     706:             $this->create( $editor, $parentId, $data );
     707:         }
     708:     }
     709:
     710:
     711:     /**
     712:      * Delete rows
     713:      *  @param Editor $editor Host Editor instance
     714:      *  @param int[] $ids Parent row IDs to delete
     715:      *  @internal
     716:      */
     717:     public function remove ( $editor, $ids )
     718:     {
     719:         if ( ! $this->_set ) {
     720:             return;
     721:         }
     722:
     723:         $that = $this;
     724:         $this->_prep( $editor );
     725:         $db = $editor->db();
     726:
     727:         if ( isset($this->_join['table']) ) {
     728:             $stmt = $db
     729:                 ->query( 'delete' )
     730:                 ->table( $this->_join['table'] )
     731:                 ->or_where( $this->_join['parent'][1], $ids )
     732:                 ->exec();
     733:         }
     734:         else {
     735:             $stmt = $db
     736:                 ->query( 'delete' )
     737:                 ->table( $this->_table )
     738:                 ->where_group( function ( $q ) use ( $that, $ids ) {
     739:                     $q->or_where( $that->_join['child'], $ids );
     740:                 } );
     741:
     742:             $this->_apply_where( $stmt );
     743:
     744:             $stmt->exec();
     745:         }
     746:     }
     747:
     748:
     749:     /**
     750:      * Validate input data
     751:      *
     752:      * @param array $errors Errors array
     753:      * @param Editor $editor Editor instance
     754:      * @param string[] $data Data to validate
     755:      * @param string $action `create` or `edit`
     756:      * @internal
     757:      */
     758:     public function validate ( &$errors, $editor, $data, $action )
     759:     {
     760:         if ( ! $this->_set && ! isset($data[$this->_name.'-many-count']) ) {
     761:             return;
     762:         }
     763:
     764:         $this->_prep( $editor );
     765:
     766:         $joinData = isset($data[$this->_name]) ?
     767:             $data[$this->_name] :
     768:             [];
     769:
     770:         for ( $i=0 ; $i<count($this->_validators) ; $i++ ) {
     771:             $validator = $this->_validators[$i];
     772:             $fn = $validator['fn'];
     773:             $res = $fn( $editor, $action, $joinData );
     774:
     775:             if ( is_string($res) ) {
     776:                 $errors[] = array(
     777:                     "name" => $validator['fieldName'],
     778:                     "status" => $res
     779:                 );
     780:             }
     781:         }
     782:
     783:         if ( $this->_type === 'object' ) {
     784:             $this->_validateFields( $errors, $editor, $joinData, $this->_name.'.' );
     785:         }
     786:         else {
     787:             for ( $i=0 ; $i<count($joinData) ; $i++ ) {
     788:                 $this->_validateFields( $errors, $editor, $joinData[$i], $this->_name.'[].' );
     789:             }
     790:         }
     791:     }
     792:
     793:
     794:
     795:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     796:      * Private methods
     797:      */
     798:
     799:     /**
     800:      * Add local WHERE condition to query
     801:      *  @param \DataTables\Database\Query $query Query instance to apply the WHERE conditions to
     802:      *  @private
     803:      */
     804:     private function _apply_where ( $query )
     805:     {
     806:         for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
     807:             if ( is_callable( $this->_where[$i] ) ) {
     808:                 $this->_where[$i]( $query );
     809:             }
     810:             else {
     811:                 $query->where(
     812:                     $this->_where[$i]['key'],
     813:                     $this->_where[$i]['value'],
     814:                     $this->_where[$i]['op']
     815:                 );
     816:             }
     817:         }
     818:     }
     819:
     820:
     821:     /**
     822:      * Create a row.
     823:      *  @param \DataTables\Database $db Database reference to use
     824:      *  @param int $parentId Parent row's primary key value
     825:      *  @param string[] $data Data to be set for the join
     826:      *  @private
     827:      */
     828:     private function _insert( $db, $parentId, $data )
     829:     {
     830:         if ( isset($this->_join['table']) ) {
     831:             // Insert keys into the join table
     832:             $stmt = $db
     833:                 ->query('insert')
     834:                 ->table( $this->_join['table'] )
     835:                 ->set( $this->_join['parent'][1], $parentId )
     836:                 ->set( $this->_join['child'][1], $data[$this->_join['child'][0]] )
     837:                 ->exec();
     838:         }
     839:         else {
     840:             // Insert values into the target table
     841:             $stmt = $db
     842:                 ->query('insert')
     843:                 ->table( $this->_table )
     844:                 ->set( $this->_join['child'], $parentId );
     845:
     846:             for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
     847:                 $field = $this->_fields[$i];
     848:
     849:                 if ( $field->apply( 'set', $data ) ) { // TODO should be create or edit
     850:                     $stmt->set( $field->dbField(), $field->val('set', $data) );
     851:                 }
     852:             }
     853:
     854:             // If the where condition variables should also be added to the database
     855:             // Note that `whereSet` is now deprecated
     856:             if ( $this->_whereSet ) {
     857:                 for ( $i=0, $ien=count($this->_where) ; $i<$ien ; $i++ ) {
     858:                     if ( ! is_callable( $this->_where[$i] ) ) {
     859:                         $stmt->set( $this->_where[$i]['key'], $this->_where[$i]['value'] );
     860:                     }
     861:                 }
     862:             }
     863:
     864:             $stmt->exec();
     865:         }
     866:     }
     867:
     868:
     869:     /**
     870:      * Prepare the instance to be run.
     871:      *
     872:      * @param  Editor $editor Editor instance
     873:      * @private
     874:      */
     875:     private function _prep ( $editor )
     876:     {
     877:         $links = $this->_links;
     878:
     879:         // Were links used to configure this instance - if so, we need to
     880:         // back them onto the join array
     881:         if ( $this->_join['parent'] === null && count($links) ) {
     882:             $editorTable = $editor->table();
     883:             $editorTable = $editorTable[0];
     884:             $joinTable = $this->table();
     885:             if ( strpos($editorTable, ' as ') !== false ) {
     886:                 $arr = explode(' as ', $editorTable);
     887:                 $editorTable = $arr[0];
     888:                 $this->_aliasParentTable = $arr[1];
     889:             }
     890:
     891:             if ( $this->_aliasParentTable ) {
     892:                 $editorTable = $this->_aliasParentTable;
     893:             }
     894:
     895:             if ( count( $links ) === 2 ) {
     896:                 // No link table
     897:                 $f1 = explode( '.', $links[0] );
     898:                 $f2 = explode( '.', $links[1] );
     899:
     900:                 if ( $f1[0] === $editorTable ) {
     901:                     $this->_join['parent'] = $f1[1];
     902:                     $this->_join['child'] = $f2[1];
     903:                 }
     904:                 else {
     905:                     $this->_join['parent'] = $f2[1];
     906:                     $this->_join['child'] = $f1[1];
     907:                 }
     908:             }
     909:             else {
     910:                 // Link table
     911:                 $f1 = explode( '.', $links[0] );
     912:                 $f2 = explode( '.', $links[1] );
     913:                 $f3 = explode( '.', $links[2] );
     914:                 $f4 = explode( '.', $links[3] );
     915:
     916:                 // Discover the name of the link table
     917:                 if ( $f1[0] !== $editorTable && $f1[0] !== $joinTable ) {
     918:                     $this->_join['table'] = $f1[0];
     919:                 }
     920:                 else if ( $f2[0] !== $editorTable && $f2[0] !== $joinTable ) {
     921:                     $this->_join['table'] = $f2[0];
     922:                 }
     923:                 else if ( $f3[0] !== $editorTable && $f3[0] !== $joinTable ) {
     924:                     $this->_join['table'] = $f3[0];
     925:                 }
     926:                 else {
     927:                     $this->_join['table'] = $f4[0];
     928:                 }
     929:
     930:                 $this->_join['parent'] = array( $f1[1], $f2[1] );
     931:                 $this->_join['child'] = array( $f3[1], $f4[1] );
     932:             }
     933:         }
     934:     }
     935:
     936:
     937:     /**
     938:      * Update a row.
     939:      *  @param \DataTables\Database $db Database reference to use
     940:      *  @param int $parentId Parent row's primary key value
     941:      *  @param string[] $data Data to be set for the join
     942:      *  @private
     943:      */
     944:     private function _update_row ( $db, $parentId, $data )
     945:     {
     946:         if ( isset($this->_join['table']) ) {
     947:             // Got a link table, just insert the pkey references
     948:             $db->push(
     949:                 $this->_join['table'],
     950:                 array(
     951:                     $this->_join['parent'][1] => $parentId,
     952:                     $this->_join['child'][1]  => $data[$this->_join['child'][0]]
     953:                 ),
     954:                 array(
     955:                     $this->_join['parent'][1] => $parentId
     956:                 )
     957:             );
     958:         }
     959:         else {
     960:             // No link table, just a direct reference
     961:             $set = array(
     962:                 $this->_join['child'] => $parentId
     963:             );
     964:
     965:             for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
     966:                 $field = $this->_fields[$i];
     967:
     968:                 if ( $field->apply( 'set', $data ) ) {
     969:                     $set[ $field->dbField() ] = $field->val('set', $data);
     970:                 }
     971:             }
     972:
     973:             // Add WHERE conditions
     974:             $where = array($this->_join['child'] => $parentId);
     975:             for ( $i=0, $ien=count($this->_where) ; $i<$ien ; $i++ ) {
     976:                 $where[ $this->_where[$i]['key'] ] = $this->_where[$i]['value'];
     977:
     978:                 // Is there any point in this? Is there any harm?
     979:                 // Note that `whereSet` is now deprecated
     980:                 if ( $this->_whereSet ) {
     981:                     if ( ! is_callable( $this->_where[$i] ) ) {
     982:                         $set[ $this->_where[$i]['key'] ] = $this->_where[$i]['value'];
     983:                     }
     984:                 }
     985:             }
     986:
     987:             $db->push( $this->_table, $set, $where );
     988:         }
     989:     }
     990:
     991:
     992:     /**
     993:      * Create an SQL string from the fields that this instance knows about for
     994:      * using in queries
     995:      *  @param string $direction Direction: 'get' or 'set'.
     996:      *  @returns array Fields to include
     997:      *  @private
     998:      */
     999:     private function _fields ( $direction )
    1000:     {
    1001:         $fields = array();
    1002:
    1003:         for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
    1004:             $field = $this->_fields[$i];
    1005:
    1006:             if ( $field->apply( $direction, null ) ) {
    1007:                 if ( strpos( $field->dbField() , "." ) === false ) {
    1008:                     $fields[] = $this->_table.'.'.$field->dbField() ." as ".$field->dbField();;
    1009:                 }
    1010:                 else {
    1011:                     $fields[] = $field->dbField();// ." as ".$field->dbField();
    1012:                 }
    1013:             }
    1014:         }
    1015:
    1016:         return $fields;
    1017:     }
    1018:
    1019:
    1020:     /**
    1021:      * Validate input data
    1022:      *
    1023:      * @param array $errors Errors array
    1024:      * @param Editor $editor Editor instance
    1025:      * @param string[] $data Data to validate
    1026:      * @param string $prefix Field error prefix for client-side to show the
    1027:      *   error message on the appropriate field
    1028:      * @internal
    1029:      */
    1030:     private function _validateFields ( &$errors, $editor, $data, $prefix )
    1031:     {
    1032:         for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
    1033:             $field = $this->_fields[$i];
    1034:             $validation = $field->validate( $data, $editor );
    1035:
    1036:             if ( $validation !== true ) {
    1037:                 $errors[] = array(
    1038:                     "name" => $prefix.$field->name(),
    1039:                     "status" => $validation
    1040:                 );
    1041:             }
    1042:         }
    1043:     }
    1044: }
    1045: