M\:l;ZfbJ^X (qvIn۳sRE]>= sHR<י.yrHlp޴-)QApgvThn+46,y9*G jUȴ|C#0" KE]VnÛ]JS4C??Gv`*2+m*(>ycjLzLh=ZK> _Mㄆ⏥\A+RVbO1@\48ಬz"c`0G)C4ML,zXZK~ɫ;-[O-߀oa๟G}[v7i'mzఔP|jD,ї7B['uTQ"b K!h|tG)<]^|G DW'1ae<]]`kdSYb`-~ڷqZ۸-{0V,w~1W]ß$1 gߠX?Dޟ/LZg0Rs,ժf)kɱB7ay!zƟBS*.wB"pD ꤎдm7|P{7XɮdC8l/3}zϔc1N?PoMhӪ .QiIXR!MQ46hK%0v&ؚ'̎EN(c-N-OZ0 P$e,7P/A{Ǚdvuc{z{r]( 3!ɤf+xȤuWɖR-V~ꌅ|SG6̞7%"-Jm62,RZ'ph~W[ {Cx Hf| )=Klm(5IRdPT^L*86 l߄YQpPG$DE7̝Ե@\IzYpa0 (uyf ,S[?^X1jHoO_Nܬe=zU"@PI+ގ8J߲^\l=,(jݯ .:~ =_S΃IR{b4-̜im0v!5n/x8f| )=Klm(5IRdPT^. 1ad" МMkQAXJg ;io@(iM)Q[ݯ .:~ =_S΄u5C#b-g$N#cٿ)%my$؉)f$AP[FM( Aeg;Q/ÃF8ly̦Z5~5DZr숳dЧ"h@.GqBRnꌅ|SGzƟBS*l:'̑x8=6z5Zo!:Dlxسd}#tEo$6dfC$5h@.GqBRnꌅ|SGzƟBS*% ?ι մ¿zwdH8X+j L!+U LK&eȝk jPf| )=Klm(5IRdPT^OXBC*sy˨/xQ)|BğRM^{e8/RC - 08d|01;ĥX@aE{>x8=#X53ыn1Gdf4 #alOkn)F3#`HH5lF/DTe;xzUjeFR:.1}zwdH8X+jPtoŒoȌ X2Ήe,f$AP[FM( Aeg;Q/Ã-a:[ Wj%C{Oh¹@ 92(h@.GqBRnꌅ|SGZRJVR< N*^ &y_sS 84p'W>C҄Ȏuyf ,S[?^X1jHoO " \af; sD1lr'!w,(Ь)A yvyq-qK*ɔeۘiLpz i8{ \* $6yG嗆~[z3,)H;pTZK Nz?ya|tuOr&G lZI':na6ۼpYh Z4-P)'_kKQ}gY |+ܦՑNeܳ4} (d/H6spV~ AWWӛw"ʸ+Ƙ=\᫗N;:.l?;A%B>$:W D=(Lkɪ$ڭ$imh($ʘ=~*mi9՟_n>ִxՒFeVg+02+À̎1^%~og)X,@HY8 Av&KlO8tSٓqB W@mȥ:os41&PFwGJKF `7Ī]y"GC |~~ەKT*žZ9Ž 4fxӞ%"YD{@_Hr E7W֨aΏx2L(H(W/5[#]M6nj4F9S[oW"^}xDGлW# -0Z4Lu<ţs-X*Bք^-^, \r<{%piS 7^R]a9F6Dh:_**Ac\.]6*'M!bXP)X䮽}y ѢV(mv xulo{oQr^B! 488,jU{ 7EѓTA.g'pL]'DǒL?*B}]#)yNtбLXB4&0.մxQiu/;pBH[Ԗ, -x O%M{'YtSٓqB W@m#`s>pUqUTX(nz~h_>8.w8-e!Z-gy"GC |ޣr1)YT]9DiZ9Ž 43|ҴEr 9r8s| 5"^΂h-PEfP! */ 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: array(); 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: