ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
v5:userguide:active_record

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Last revisionBoth sides next revision
userguide:active_record [2015/12/04 20:20] – mnewnhamv5:userguide:active_record [2016/01/13 02:31] – ↷ Links adapted because of a move operation mnewnham
Line 15: Line 15:
   * Works with PHP4 and PHP5 and provides equivalent functionality in both versions of PHP.   * Works with PHP4 and PHP5 and provides equivalent functionality in both versions of PHP.
   * ADOdb_Active_Record works when you are connected to multiple databases. Zend's only works when connected to a default database.   * ADOdb_Active_Record works when you are connected to multiple databases. Zend's only works when connected to a default database.
-  * Support for [[reference:$ADODB_ASSOC_CASE]]. The field names are upper-cased, lower-cased or left in natural case depending on this setting.+  * Support for [[v5:reference:adodb_assoc_case]]. The field names are upper-cased, lower-cased or left in natural case depending on this setting.
   * No field name conversion to camel-caps style, unlike Zend's implementation which will convert field names such as 'first_name' to 'firstName'.   * No field name conversion to camel-caps style, unlike Zend's implementation which will convert field names such as 'first_name' to 'firstName'.
   * NewADOConnection::GetActiveRecords() and ADOConnection::GetActiveRecordsClass() functions in adodb.inc.php.   * NewADOConnection::GetActiveRecords() and ADOConnection::GetActiveRecordsClass() functions in adodb.inc.php.
Line 376: Line 376:
 ===== ClassHasMany ===== ===== ClassHasMany =====
  
-To globally define a one-to-many relationship we use the static function ADODB_Active_Record::ClassHasMany($class, $relation, $foreignKey = '', $foreignClass = 'ADODB_Active_Record'). For example, we have 2 tables, persons (parent table) and children (child table) linked by persons.id = children.person_id. The variable $person->children is an array that holds the children. To define this relationship:+To globally define a one-to-many relationship we use the static function ADODB_Active_Record::ClassHasMany($class, $relation, $foreignKey = ' ', $foreignClass = 'ADODB_Active_Record'). For example, we have 2 tables, persons (parent table) and children (child table) linked by persons.id = children.person_id. The variable $person->children is an array that holds the children. To define this relationship:
  
 <code php> <code php>
Line 397: Line 397:
 </code> </code>
  
-By default, data returned by HasMany() is unsorted. To define an order by clause (or define a SELECT LIMIT window), see LoadRelations() below. Another point is that all children are loaded only when the child member is accessed (in __get), and not when the Load() function of the parent object is called. This helps to conserve memory.+By default, data returned by HasMany() is unsorted. To define an order by clause (or define a SELECT LIMIT window), see LoadRelations() below. Another point is that all children are loaded only when the child member is accessed (in  <code php>__get()</code>), and not when the Load() function of the parent object is called. This helps to conserve memory.
  
 To create and save new parent and child records: To create and save new parent and child records:
Line 436: Line 436:
 ADODB_Active_Record::ClassHasMany('person', 'children','person_id', 'child'); ADODB_Active_Record::ClassHasMany('person', 'children','person_id', 'child');
 </code> </code>
-Lastly some troubleshooting issues. We use the __get() method to set $p->children below. So once $p->children is defined by accessing it, we don't change the child reference, as shown below:+Lastly some troubleshooting issues. We use the <code php>__get()</code> method to set $p->children below. So once $p->children is defined by accessing it, we don't change the child reference, as shown below:
 <code php> <code php>
 ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); ADODB_Active_Record::ClassHasMany('person', 'children','person_id');
Line 452: Line 452:
  
 For some classes, the mapping between class name and table name (which is the pluralised version) might not match. For example, the class name might be person, but the table name might be people. So we have 2 tables, people (parent table) and children (child table) linked by people.id = children.person_id. For some classes, the mapping between class name and table name (which is the pluralised version) might not match. For example, the class name might be person, but the table name might be people. So we have 2 tables, people (parent table) and children (child table) linked by people.id = children.person_id.
-Then you use the following static function ADODB_Active_Record::TableHasMany($table, $relation, $foreignKey = '', $foreignClass = 'ADODB_Active_Record') like this:+Then you use the following static function ADODB_Active_Record::TableHasMany($table, $relation, $foreignKey = ' ', $foreignClass = 'ADODB_Active_Record') like this:
  
 ADODB_Active_Record::TableHasMany('people', 'children', 'person_id') ADODB_Active_Record::TableHasMany('people', 'children', 'person_id')
Line 460: Line 460:
 For some classes, the mapping between class name and table name (which is the pluralised version) might not match or the primary key is not the default id. For example, the class name might be person, but the table name might be people. So we have 2 tables, people (parent table) and children (child table) linked by people.pid = children.person_id. For some classes, the mapping between class name and table name (which is the pluralised version) might not match or the primary key is not the default id. For example, the class name might be person, but the table name might be people. So we have 2 tables, people (parent table) and children (child table) linked by people.pid = children.person_id.
  
-Then you use the following static function ADODB_Active_Record::TableKeyHasMany($table, $tablePKey, $relation, $foreignKey = '', $foreignClass = 'ADODB_Active_Record') like this:+Then you use the following static function ADODB_Active_Record::TableKeyHasMany($table, $tablePKey, $relation, $foreignKey = ' ', $foreignClass = 'ADODB_Active_Record') like this:
  
 ADODB_Active_Record::TableKeyHasMany('people', 'pid', 'children', 'person_id') ADODB_Active_Record::TableKeyHasMany('people', 'pid', 'children', 'person_id')
Line 520: Line 520:
 This older method is deprecated and ClassHasMany/TableHasMany/TableKeyHasMany should be used. This older method is deprecated and ClassHasMany/TableHasMany/TableKeyHasMany should be used.
  
-The older way to define a one-to-many relationship is to use $parentobj->HasMany($relation, $foreignKey = ''). For example, we have 2 tables, persons (parent table) and children (child table) linked by persons.id = children.person_id. The variable $person->children is an array that holds the children. To define this relationship:+The older way to define a one-to-many relationship is to use $parentobj->HasMany($relation, $foreignKey = ' '). For example, we have 2 tables, persons (parent table) and children (child table) linked by persons.id = children.person_id. The variable $person->children is an array that holds the children. To define this relationship:
 <code php> <code php>
 class person extends ADOdb_Active_Record{} class person extends ADOdb_Active_Record{}
Line 558: Line 558:
  
 Also if no data is loaded into the child instance, then $p will return null; Also if no data is loaded into the child instance, then $p will return null;
 +<code php>
 +ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id');
  
- ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id'); +$ch = new kid(); 
- +$p = $ch->person; # $p is null 
- $ch = new kid(); +</code>
- $p = $ch->person; # $p is null+
 Another way to define the class of the parent (which otherwise defaults to ADODB_Active_Record) as follows: Another way to define the class of the parent (which otherwise defaults to ADODB_Active_Record) as follows:
  
 +<code php>
 +class kid extends ADOdb_Active_Record{};
 +class person extends ADOdb_Active_Record{... your modifications ... };
 +ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id', 'person');
 +</code>
  
- class kid extends ADOdb_Active_Record{}; +===== TableBelongsTo =====
- class person extends ADOdb_Active_Record{... your modifications ... }; +
- ADODB_Active_Record::ClassBelongsTo('kid','person','person_id','id', 'person'); +
-TableBelongsTo+
  
 If the child table differs from the convention that the child table name is the plural of the child class name, use this function: ADODB_Active_Record::TableBelongsTo($childTable, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record'). If the child table differs from the convention that the child table name is the plural of the child class name, use this function: ADODB_Active_Record::TableBelongsTo($childTable, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record').
Line 575: Line 578:
 E.g. the class is child, but the table name is children, and the link between the two tables is children.person_id = person.id: E.g. the class is child, but the table name is children, and the link between the two tables is children.person_id = person.id:
  
- ADODB_Active_Record::TableBelongsTo('children','person','person_id','id'); + ADODB_Active_Record::TableBelongsTo('children','person','person_id','id'); 
-TableKeyBelongsTo+ 
 +===== TableKeyBelongsTo ====
  
 If the child table differs from the convention that the child table name is the plural of the child class name or the primary key is not 'id', use this function: ADODB_Active_Record::TableKeyBelongsTo($childTable, $childKey, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record'). If the child table differs from the convention that the child table name is the plural of the child class name or the primary key is not 'id', use this function: ADODB_Active_Record::TableKeyBelongsTo($childTable, $childKey, $relationName, $foreignKey, $parentPrimaryKey = 'id', $parentClass = 'ADODB_Active_Record').
Line 582: Line 586:
 E.g. the class is child, but the table name is children and primary key is ch_id, and the link between the two tables is children.person_id = person.id: E.g. the class is child, but the table name is children and primary key is ch_id, and the link between the two tables is children.person_id = person.id:
  
- ADODB_Active_Record::TableKeyBelongsTo('children','ch_id', 'person','person_id','id'); +ADODB_Active_Record::TableKeyBelongsTo('children','ch_id', 'person','person_id','id'); 
-BelongsTo+ 
 +===== BelongsTo =====
  
 The following is deprecated. Use ClassBelongsTo/TableBelongsTo/TableKeyBelongsTo instead. The following is deprecated. Use ClassBelongsTo/TableBelongsTo/TableKeyBelongsTo instead.
  
 The older way to define the parent of the current object is using BelongsTo($relationName, $foreignKey, $parentPrimaryKey = 'id'). In the example below, we have a child table children, and a parent table person. We have a link children.person_id = persons.id. We create a child first, then link it to the parent: The older way to define the parent of the current object is using BelongsTo($relationName, $foreignKey, $parentPrimaryKey = 'id'). In the example below, we have a child table children, and a parent table person. We have a link children.person_id = persons.id. We create a child first, then link it to the parent:
- +<code> 
- class Child extends ADOdb_Active_Record{}; +class Child extends ADOdb_Active_Record{}; 
- $ch = new Child('children',array('id')); +$ch = new Child('children',array('id')); 
- $ch->BelongsTo('person','person_id','id');  ## this can be simplified to $ch->BelongsTo('person') +$ch->BelongsTo('person','person_id','id');  ## this can be simplified to $ch->BelongsTo('person') 
-                                             ## as foreign key defaults to $table.'_id' and +                                            ## as foreign key defaults to $table.'_id' and 
-                                             ## parent pkey defaults to 'id' +                                            ## parent pkey defaults to 'id' 
- $ch->Load('id=1'); +$ch->Load('id=1'); 
- $p = $ch->person; +$p = $ch->person; 
- if (!$p || $p->name_first != 'John') echo "Error loading belongsTo<br>"; +if (!$p || $p->name_first != 'John') echo "Error loading belongsTo<br>"; 
- else echo "OK loading BelongTo<br>";+else echo "OK loading BelongTo<br>"; 
 +</code>
 You only need to define BelongsTo() once in a script as it is global for all instances. You only need to define BelongsTo() once in a script as it is global for all instances.
  
-LoadRelations+===== LoadRelations =====
  
-Sometimes you want to load only a subset of data in a relationship. For example, you could load all female children sorted by children.name using LoadRelations($relation, $whereOrderBy = '', $offset = -1, $limit = -1): +Sometimes you want to load only a subset of data in a relationship. For example, you could load all female children sorted by children.name using LoadRelations($relation, $whereOrderBy = ' ', $offset = -1, $limit = -1): 
- +<code php> 
- # assume this has been called: +# assume this has been called: 
- #   ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); +#   ADODB_Active_Record::ClassHasMany('person', 'children','person_id'); 
- $person = new person(); +$person = new person(); 
- $person->Load('id=23'); +$person->Load('id=23'); 
- # Load doesn't load children until $person->children is accessed or LoadRelations is called: +# Load doesn't load children until $person->children is accessed or LoadRelations is called: 
- $person->LoadRelations('children',"gender='F' order by name");+$person->LoadRelations('children',"gender='F' order by name"); 
 +</code>
 Lastly, if you have lots of child data, you can define a window of data of records to load. In the following example, we load a window of 100 records at a time: Lastly, if you have lots of child data, you can define a window of data of records to load. In the following example, we load a window of 100 records at a time:
  
 +<code php>
 +# assume this has been called:
 +#  ADODB_Active_Record::ClassHasMany('Account', 'transactions','account_id');
 +#acc = new Account();
 +$acc->Load('id=23');
  
- # assume this has been called: +$start = 0; 
- #  ADODB_Active_Record::ClassHasMany('Account', 'transactions','account_id'); +while(true) { 
- $acc = new Account(); +    $acc->LoadRelations('transactions',"tx_done=0 order by trxdate", $start, $start+100); 
- $acc->Load('id=23'); +    if (!$acc->transactions) break; 
- +    foreach ($acc->transactions as $k => $trx) { 
- $start = 0; + ## process 
- while(true) { + $trx->tx_done = 1; 
- $acc->LoadRelations('transactions',"tx_done=0 order by trxdate", $start, $start+100); + $trx->save(); 
- if (!$acc->transactions) break; +    } 
- foreach ($acc->transactions as $k => $trx) { +    $start += 100; 
- ## process +    unset($acc->transactions);
- $trx->tx_done = 1; +
- $trx->save(); +
- } +
- $start += 100; +
- unset($acc->transactions); +
  
- }+} 
 +</code>
 The $offset is 0-based, and $limit is the number of records to retrieve. The default is to ignore $offset (-1) and $limit (-1). The $offset is 0-based, and $limit is the number of records to retrieve. The default is to ignore $offset (-1) and $limit (-1).
  
-Acknowledgements+===== Acknowledgements =====
  
 Thanks to Chris Ravenscroft for original one-to-many code (chris#voilaweb.com). Thanks to Chris Ravenscroft for original one-to-many code (chris#voilaweb.com).
  
-ADOConnection Supplement+===== ADOConnection Supplement =====
  
-ADOConnection::GetActiveRecords()+==== ADOConnection::GetActiveRecords() ====
  
 This allows you to retrieve an array of ADOdb_Active_Records. Returns false if an error occurs. This allows you to retrieve an array of ADOdb_Active_Records. Returns false if an error occurs.
  
 +<code php>
 $table = 'products'; $table = 'products';
 $whereOrderBy = "name LIKE 'A%' ORDER BY Name"; $whereOrderBy = "name LIKE 'A%' ORDER BY Name";
Line 651: Line 659:
  $rec->save();  $rec->save();
 } }
 +</code>
 And to retrieve all records ordered by specific fields: And to retrieve all records ordered by specific fields:
 +<code php>
 $whereOrderBy = "1=1 ORDER BY Name"; $whereOrderBy = "1=1 ORDER BY Name";
 $activeRecArr = $db->GetActiveRecords($table); $activeRecArr = $db->GetActiveRecords($table);
-To use bind variables (assuming ? is the place-holder for your database):+//To use bind variables (assuming ? is the place-holder for your database):
  
 $activeRecArr = $db->GetActiveRecords($tableName, 'name LIKE ?', $activeRecArr = $db->GetActiveRecords($tableName, 'name LIKE ?',
Line 664: Line 673:
  array('A%'), array('id'));  array('A%'), array('id'));
 ADOConnection::GetActiveRecordsClass() ADOConnection::GetActiveRecordsClass()
 +</code>
 This allows you to retrieve an array of objects derived from ADOdb_Active_Records. Returns false if an error occurs. This allows you to retrieve an array of objects derived from ADOdb_Active_Records. Returns false if an error occurs.
  
 +<code php>
 class Product extends ADOdb_Active_Record{}; class Product extends ADOdb_Active_Record{};
 $table = 'products'; $table = 'products';
Line 677: Line 687:
  $rec->save();  $rec->save();
 } }
 +</code>
 To use bind variables (assuming ? is the place-holder for your database): To use bind variables (assuming ? is the place-holder for your database):
 +<code php>
 $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?', $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?',
  array('A%'));  array('A%'));
Line 685: Line 696:
 $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?', $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?',
  array('A%'), array('id'));  array('A%'), array('id'));
 +
 +</code>
 ADOConnection::ErrorMsg() ADOConnection::ErrorMsg()
  
Line 695: Line 708:
 ===== ActiveRecord Code Sample ===== ===== ActiveRecord Code Sample =====
  
-<?php+<code php>
 include('../adodb.inc.php'); include('../adodb.inc.php');
 include('../adodb-active-record.inc.php'); include('../adodb-active-record.inc.php');
Line 999: Line 1012:
 PHP5 specific: Make GetActiveRecords*() return an Iterator. PHP5 specific: Make GetActiveRecords*() return an Iterator.
  
-PHP5 specific: Change PHP5 implementation of Active Record to use __get() and __set() for better performance.+PHP5 specific: Change PHP5 implementation of Active Record to use <code php>__get() and __set()</code> for better performance.
v5/userguide/active_record.txt · Last modified: 2019/05/08 16:32 by dregad