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
userguide:active_record [2015/12/04 20:20] – mnewnhamv5:userguide:active_record [2019/05/08 16:32] (current) – [Setting the Table Name] ADODB_ASSOC_CASE not $ADODB_ASSOC_CASE dregad
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 152: Line 152:
 We provide two ways to define your own table: We provide two ways to define your own table:
  
-1. Use a constructor parameter to override the default table naming behaviour.+  - Use a constructor parameter to override the default table naming behaviour. <code php> 
 +class person extends ADOdb_Active_Record {} 
 +$person = new person('People'); 
 +</code> 
 +  - Define it in a class declaration: <code php> 
 +class person extends ADOdb_Active_Record 
 +{ 
 +    var $_table = 'People'; 
 +} 
 +$person = new person(); 
 +</code>
  
- class person extends ADOdb_Active_Record{} +==== ADODB_ASSOC_CASE ====
- $person = new person('People'); +
-2. Define it in a class declaration:+
  
- class person extends ADOdb_Active_Record +This allows you to control the case of field names and properties. For example, in Oracle all field names are uppercase by default, so you can force them to lowercase using ''define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_LOWER);''.
- { +
- var $_table = 'People'; +
- } +
- $person = new person(); +
-$ADODB_ASSOC_CASE+
  
-This allows you to control the case of field names and properties. For example, all field names in Oracle are upper-case by default. So you can force field names to be lowercase using $ADODB_ASSOC_CASE. Legal values are as follows:+For further details on valid values and usage, see [[v5:reference:adodb_assoc_case]] reference.
  
- 0: lower-case +<code php> 
- 1: upper-case +// ADODB_ASSOC_CASE ==  ADODB_ASSOC_CASE_LOWER
- 2: native-case +
-So to force all Oracle field names to lower-case, use +
- +
-$ADODB_ASSOC_CASE = 0;+
 $person = new person('People'); $person = new person('People');
 $person->name = 'Lily'; $person->name = 'Lily';
-$ADODB_ASSOC_CASE = 2;+ 
 +// ADODB_ASSOC_CASE ==  ADODB_ASSOC_CASE_UPPER
 $person2 = new person('People'); $person2 = new person('People');
 $person2->NAME = 'Lily'; $person2->NAME = 'Lily';
-Also see $ADODB_ASSOC_CASE.+</code>
  
-ADOdb_Active_Record::Save()+==== ADOdb_Active_Record::Save() ====
  
 Saves a record by executing an INSERT or UPDATE SQL statement as appropriate. Saves a record by executing an INSERT or UPDATE SQL statement as appropriate.
Line 188: Line 188:
 Returns 0 on failed UPDATE, and 1 on UPDATE if data has changed, and -1 if no data was changed, so no UPDATE statement was executed. Returns 0 on failed UPDATE, and 1 on UPDATE if data has changed, and -1 if no data was changed, so no UPDATE statement was executed.
  
-ADOdb_Active_Record::Replace()+==== ADOdb_Active_Record::Replace() ====
  
 ADOdb supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise. ADOdb supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise.
Line 210: Line 210:
 </code> </code>
  
-ADOdb_Active_Record::Find($whereOrderBy, $bindarr=false, $pkeyArr=false)+==== ADOdb_Active_Record::Find($whereOrderBy, $bindarr=false, $pkeyArr=false) ====
  
 We want to retrieve an array of active records based on some search criteria. For example: We want to retrieve an array of active records based on some search criteria. For example:
 <code php> <code php>
 class person extends ADOdb_Active_Record { class person extends ADOdb_Active_Record {
-var $_table = 'people';+    var $_table = 'people';
 } }
  
Line 221: Line 221:
 $peopleArray = $person->Find("name like ? order by age", array('Sm%')); $peopleArray = $person->Find("name like ? order by age", array('Sm%'));
 </code> </code>
 +
 ===== Quoting Identifiers ===== ===== Quoting Identifiers =====
  
Line 376: Line 377:
 ===== 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 398:
 </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 437:
 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 453:
  
 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 461:
 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 521:
 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 559:
  
 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 579:
 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 587:
 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) { 
 + ## process 
 + $trx->tx_done = 1; 
 + $trx->save(); 
 +    } 
 +    $start += 100; 
 +    unset($acc->transactions);
  
- $start = 0; +} 
- while(true) { +</code>
- $acc->LoadRelations('transactions',"tx_done=0 order by trxdate", $start, $start+100); +
- if (!$acc->transactions) break; +
- foreach ($acc->transactions as $k => $trx) { +
- ## process +
- $trx->tx_done = 1; +
- $trx->save(); +
- } +
- $start += 100; +
- unset($acc->transactions); +
- +
- +
- }+
 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 660:
  $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 674:
  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 688:
  $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 697:
 $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 709:
 ===== 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 1013:
 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.1449256827.txt.gz · Last modified: 2017/04/21 11:39 (external edit)