summaryrefslogtreecommitdiff
path: root/demos/quickstart/protected/pages/Database/ActiveRecord.page
blob: 78947faf3c85c4c6f13ce2f76a5761ce4d44a806 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
<com:TContent ID="body" >
<!-- $Id $ -->
<h1 id="138046">Active Record</h1>
<com:SinceVersion Version="3.1a" />
<p id="690478" class="block-content">Active Records are objects that wrap a row in a database table or view,
    encapsulate the database access and add domain logic on that data.
    The basics of an Active Record are business classes, e.g., a
    <tt>Products</tt> class,  that match very closely the record structure
    of an underlying database table. Each Active Record will be responsible for
    saving and loading data to and from the database. </p>
<div class="info"><b class="note">Info:</b>
    The data structure of an Active Record should  match that of a table
    in the database.
    Each column of a table should have a corresponding member variable or property in the
    Active Record class the represents the table.
</div>

<h2 id="138047">When to Use It</h2>
<p id="690479" class="block-content">Active Record is a good choice for domain logic that isn't too complex,
    such as creates, reads, updates, and deletes. Derivations and validations
    based on a single record work well in this structure. Active Record has the
    primary advantage of simplicity. It's easy to build
    Active Records, and they are easy to understand.</p>

    <p id="690480" class="block-content">However, as your business logic grows in complexity, you'll soon want
    to use your object's direct relationships, collections, inheritance, and so
    forth. These don't map easily onto Active Record, and adding them piecemeal
    gets very messy.
    Another argument against Active Record is the fact that it couples the object
    design to the database design. This makes it more difficult to refactor as a project goes forward.</p>

    <p id="690481" class="block-content">The alternative is to use a Data Mapper that separates the roles of the
        business object and how these objects are stored.
        Prado provides a complimentary choice between Active Record and
        <a href="?page=Database.SqlMap">SqlMap Data Mapper</a>.
        A SqlMap Data Mapper can be used to load Active Record objects, in turn; these
        Active Record objects can be used to update the database.
        The "relationship" between Active Records and <a href="?page=Database.SqlMap">SqlMap</a> is illustrated in the
    following diagram. More details regarding the SqlMap Data Mapper can be found in
    the <a href="http://www.pradosoft.com/demos/sqlmap/">SqlMap Manual</a>.
    <img src=<%~ sqlmap_active_record.png %> alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/>
    </p>

    <p id="690482" class="block-content">
        The Active Record class has functionality to perform the following tasks.
    </p>
    <ul id="u1" class="block-content">
        <li>Create, Retrieve, Update and Delete records.</li>
        <li>Finder methods to wrap commonly used SQL queries and return Active Record objects.</li>
        <li>Fetch relationships (related foreign objects) such as "has many", "has one", "belongs to" and "many to many" via association table.</li>
        <li>Lazy loading of relationships.</li>
    </ul>
<h2 id="144005">Design Implications</h2>
<p id="720009" class="block-content">
Prado's implementation of Active Record does not maintain referential identity. Each object obtained using
Active Record is a copy of the data in the database. For example, If you ask for
a particular customer and get back a <tt>Customer</tt> object, the next time you ask for
that customer you get back another instance of a <tt>Customer</tt> object. This implies that a strict
comparison (i.e., using <tt>===</tt>) will return false, while loose comparison (i.e., using <tt>==</tt>) will
return true if the object values are equal by loose comparison.
<p id="720010" class="block-content">
<p id="720011" class="block-content">
This is design implication related to the following question.
<i>"Do you think of the customer as an object, of which there's only one,
or do you think of the objects you operate on as <b>copies</b> of the database?"</i>
Other O/R mappings will imply that there is only one Customer object with custID 100, and it literally is that customer.
If you get the customer and change a field on it, then you have now changed that customer.
<i>"That constrasts with: you have changed this copy of the customer, but not that copy.
And if two people update the customer on two copies of the object, whoever updates first,
or maybe last, wins."</i> [A. Hejlsberg 2003]
</p>

<h2 id="142010">Database Supported</h2>
<p id="p1" class="block-content">
The Active Record implementation utilizes the <a href="?page=Database.DAO">Prado DAO</a> classes for data access.
The current Active Record implementation supports the following database.
</p>
<ul>
    <li><a href="http://www.mysql.com">MySQL 4.1 or later</a></li>
    <li><a href="http://www.postgres.com">Postgres SQL 7.3 or later</a></li>
    <li><a href="http://www.sqlite.org">SQLite 2 and 3</a></li>
    <li><a href="#">MS SQL 2000 or later</a></li>
    <li><a href="http://www.oracle.com">Oracle Database (alpha)</a></li>
</ul>
<p id="710009" class="block-content">Support for other databases can be provided when there are sufficient demands.</p>

<h1 id="138048">Defining an Active Record</h1>
<p id="690483" class="block-content">Let us
    consider the following "<tt>users</tt>" table that contains two columns named "<tt>username</tt>" and "<tt>email</tt>",
    where "<tt>username</tt>" is also the primary key.
<com:TTextHighlighter Language="sql" CssClass="source block-content" id="code_690147">
CREATE TABLE users
(
    username VARCHAR( 20 ) NOT NULL ,
    email VARCHAR( 200 ) ,
    PRIMARY KEY ( username )
);
</com:TTextHighlighter>
</p>
<p id="690484" class="block-content">Next we define our Active Record class that corresponds to the "<tt>users</tt>" table.
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690148">
class UserRecord extends TActiveRecord
{
    const TABLE='users'; //table name

    public $username; //the column named "username" in the "users" table
    public $email;

    /**
     * @return TActiveRecord active record finder instance
     */
    public static function finder($className=__CLASS__)
    {
        return parent::finder($className);
    }
}
</com:TTextHighlighter>
</p>
<p id="690485" class="block-content">Each column of the "<tt>users</tt>" table must have corresponding
    property of the same name as the column name in the <tt>UserRecord</tt> class.
    Of course, you also define additional member variables or properties that does not exist in the table structure.
    The class constant
    <tt>TABLE</tt> is optional when the class name is the same as
    the table name in the database, otherwise <tt>TABLE</tt> must
    specify the table name that corresponds to your Active Record class.
</p>

<div class="tip"><b class="note">Tip:</b>
You may specify qualified table names. E.g. for MySQL, <tt>TABLE = "`database1`.`table1`"</tt>.
</div>

<div class="note"><b class="note">Note:</b>
Since version <b>3.1.3</b> you can also use a method <tt>table()</tt> to define the table name. 
This allows you to dynamically specify which table should be used by the ActiveRecord.
<com:TTextHighlighter Language="php" CssClass="source block-content">
class TeamRecord extends TActiveRecord
{
    public function table() {
        return 'Teams';
    }
}
</com:TTextHighlighter>
</div>

<p class="block-content" id="ar_as_component">
    Since <tt>TActiveRecord</tt> extends <tt>TComponent</tt>, setter and
    getter methods can be defined to allow control over how variables
    are set and returned. For example, adding a <tt>$level</tt> property to the UserRecord class:
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690149">
class UserRecord extends TActiveRecord {
    ... //existing definitions as above

    private $_level;
    public function setLevel($value) {
        $this->_level=TPropertyValue::ensureInteger($value,0);
    }
    public function getLevel($value){
        return $this->_level;
    }
}
</com:TTextHighlighter>
<p id="710010" class="block-content">More details regarding TComponent can be found in the <a href="?page=Fundamentals.Components">Components documentation</a>.
Later we shall use the getter/setters to allow for lazy loading of relationship objects.
</p>

<div class="info"><b class="note">Info:</b>
<tt>TActiveRecord</tt> can also work with database views by specifying the constant <tt>TABLE</tt>
corresponding to the view name. However, objects returned
from views are read-only, calling the <tt>save()</tt> or <tt>delete()</tt> method
will raise an exception.
</div>

<p id="690486" class="block-content">
    The static method <tt>finder()</tt> returns an <tt>UserRecord</tt> instance
    that can be used to load records from the database. The loading of records
    using the finder methods is discussed a little later. The <tt>TActiveRecord::finder()</tt>
    static method takes the name of an Active Record class as parameter.
</p>

<h2 id="138049">Setting up a database connection</h2>
<p id="690487" class="block-content">
    A default database connection for Active Record can be set as follows.
    See <a href="?page=Database.DAO">Establishing Database Connection</a> for
    further details regarding creation of database connection in general.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690150">
//create a connection and give it to the Active Record manager.
$dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL
$conn = new TDbConnection($dsn, 'dbuser','dbpass');
TActiveRecordManager::getInstance()->setDbConnection($conn);
</com:TTextHighlighter>

<p id="710011" class="block-content">Alternatively, you can create a base class and override the <tt>getDbConnection()</tt>
method to return a database connection. This is a simple way to permit multiple
connections and multiple databases. The following code demonstrates defining
the database connection in a base class (not need to set the DB connection anywhere else).
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
class MyDb1Record extends TActiveRecord
{
    public function getDbConnection()
    {
        static $conn;
        if($conn===null)
            $conn = new TDbConnection('xxx','yyy','zzz');
        return $conn;
    }
}
class MyDb2Record extends TActiveRecord
{
    public function getDbConnection()
    {
        static $conn;
        if($conn===null)
            $conn = new TDbConnection('aaa','bbb','ccc');
        return $conn;
    }
}
</com:TTextHighlighter>


<h3 class="prado-specific">Using <tt>application.xml</tt> within the Prado Framework</h3>
<div class="prado-specific">
<p id="690488" class="block-content">
    The default database connection can also be configured using a <tt>&lt;module&gt;</tt>
    tag in the <a href="?page=Configurations.AppConfig">application.xml</a>
    or <a href="?page=Configurations.PageConfig">config.xml</a> as follows.
<com:TTextHighlighter Language="xml" CssClass="source block-content" id="code_690151">
<modules>
  <module class="System.Data.ActiveRecord.TActiveRecordConfig" EnableCache="true">
    <database ConnectionString="pgsql:host=localhost;dbname=test"
        Username="dbuser" Password="dbpass" />
  </module>
</modules>
</com:TTextHighlighter>
<div class="tip"><b class="note">Tip:</b>
    The <tt>EnableCache</tt> attribute when set to "true" will cache the table
    meta data, that is, the table columns names, indexes and constraints are
    saved in the cache and reused. You must clear or disable the cache if you
    wish to see changes made to your table definitions. A <a href="?page=Advanced.Performance#6402">cache
    module</a> must also be defined for the cache to function.
</div>
</p>

<p id="690489" class="block-content">A <tt>ConnectionID</tt> property can be specified with value corresponding
    to another <tt>TDataSourceConfig</tt> module configuration's ID value. This allows
    the same database connection to be used in other modules such as <a href="?page=Database.SqlMap">SqlMap</a>.
<com:TTextHighlighter Language="xml" CssClass="source block-content" id="code_690152">
<modules>
  <module class="System.Data.TDataSourceConfig" id="db1">
    <database ConnectionString="pgsql:host=localhost;dbname=test"
        Username="dbuser" Password="dbpass" />
  </module>

  <module class="System.Data.ActiveRecord.TActiveRecordConfig"
        ConnectionID="db1" EnableCache="true"  />

  <module class="System.Data.SqlMap.TSqlMapConfig"
        ConnectionID="db1"  ... />
</modules>
</com:TTextHighlighter>
</p>
</div>

<h2 id="138050">Loading data from the database</h2>
<p id="690490" class="block-content">
    The <tt>TActiveRecord</tt> class provides many convenient methods to find
    records from the database. The simplest is finding one record by matching a primary key or a
    composite key (primary keys that consists of multiple columns).
    See the <com:DocLink ClassPath="System.Data.ActiveRecord.TActiveRecord" /> for
    more details.
</p>

<div class="info"><b class="note">Info:</b>
All finder methods that may return 1 record only will return <tt>null</tt> if no matching data
is found. All finder methods that return an array of records will return an empty array if no
matching data is found.
</div>

	<h3 id="138055"><tt>findByPk()</tt></h3>
    <p id="690491" class="block-content">Finds one record using only a primary key or a composite key.
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690153">
$finder = UserRecord::finder();
$user = $finder->findByPk($primaryKey);

//when the table uses a composite key
$record = $finder->findByPk($key1, $key2, ...);
$record = $finder->findByPk(array($key1, $key2,...));
</com:TTextHighlighter>
</p>

    <h3 id="138056"><tt>findAllByPks()</tt></h3>
    <p id="690492" class="block-content">Finds multiple records using a list of primary keys or composite keys.
The following are equivalent for primary keys (primary key consisting of only one column/field).
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690154">
$finder = UserRecord::finder();
$users = $finder->findAllByPks($key1, $key2, ...);
$users = $finder->findAllByPks(array($key1, $key2, ...));
</com:TTextHighlighter>
The following are equivalent for composite keys.
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690155">
//when the table uses composite keys
$record = $finder->findAllByPks(array($key1, $key2), array($key3, $key4), ...);

$keys = array(  array($key1, $key2), array($key3, $key4), ... );
$record = $finder->findAllByPks($keys);
</com:TTextHighlighter>


<h3 id="138057"><tt>find()</tt></h3>
<p id="690493" class="block-content">Finds <b>one single record</b> that matches the criteria. The criteria
    can be a partial SQL string or a <tt>TActiveRecordCriteria</tt> object.</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690156">
$finder = UserRecord::finder();

//:name and :pass are place holders for specific values of $name and $pass
$finder->find('username = :name AND password = :pass',
                          array(':name'=>$name, ':pass'=>$pass));

//using position place holders
$finder->find('username = ? AND password = ?', array($name, $pass));
//same as above
$finder->find('username = ? AND password = ?', $name, $pass);

//$criteria is of TActiveRecordCriteria
$finder->find($criteria); //the 2nd parameter for find() is ignored.
</com:TTextHighlighter>

<p id="690494" class="block-content">The <tt>TActiveRecordCriteria</tt> class has the following properties:
</p>
    <ul id="u2" class="block-content">
        <li><tt>Parameters</tt> -- name value parameter pairs.</li>
        <li><tt>OrdersBy</tt> -- column name and ordering pairs.</li>
        <li><tt>Condition</tt> -- parts of the WHERE SQL conditions.</li>
        <li><tt>Limit</tt> -- maximum number of records to return.</li>
        <li><tt>Offset</tt> -- record offset in the table.</li>
    </ul>

<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690157">
$criteria = new TActiveRecordCriteria;
$criteria->Condition = 'username = :name AND password = :pass';
$criteria->Parameters[':name'] = 'admin';
$criteria->Parameters[':pass'] = 'prado';
$criteria->OrdersBy['level'] = 'desc';
$criteria->OrdersBy['name'] = 'asc';
$criteria->Limit = 10;
$criteria->Offset = 20;
</com:TTextHighlighter>

<div class="note"><b class="note">Note:</b>
For MSSQL and when <tt>Limit</tt> and <tt>Offset</tt> are positive integer values. The
actual query to be executed is modified by the
<com:DocLink ClassPath="System.Data.ActiveRecord.Common.Mssql.TMssqlCommandBuilder"
Text="TMssqlCommandBuilder"
/>
class according to
<a href="http://troels.arvin.dk/db/rdbms/#select-limit-offset">http://troels.arvin.dk/db/rdbms/</a>
to emulate the <tt>Limit</tt> and <tt>Offset</tt> conditions.
</div>

<h3 id="138058"><tt>findAll()</tt></h3>
<p id="690495" class="block-content">Same as <tt>find()</tt> but returns an array of objects.</p>

<h3 id="138059"><tt>findBy*()</tt> and <tt>findAllBy*()</tt></h3>
<p id="690496" class="block-content">Dynamic find method using parts of the method name as search criteria.
Method names starting with <tt>findBy</tt> return 1 record only
and method names starting with <tt>findAllBy</tt> return an array of records.
The condition is taken as part of the method name after <tt>findBy</tt> or <tt>findAllBy</tt>.

The following blocks of code are equivalent:
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690158">
$finder->findByName($name)
$finder->find('Name = ?', $name);
</com:TTextHighlighter>

<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690159">
$finder->findByUsernameAndPassword($name,$pass);
$finder->findBy_Username_And_Password($name,$pass);
$finder->find('Username = ? AND Password = ?', $name, $pass);
</com:TTextHighlighter>

<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690160">
$finder->findAllByAge($age);
$finder->findAll('Age = ?', $age);
</com:TTextHighlighter>

<div class="tip"><b class="note">Tip:</b>
You may also use a combination of <tt>AND</tt> and <tt>OR</tt> as a condition in the dynamic methods.
</div>

<h3 id="138060"><tt>findBySql()</tt> and <tt>findAllBySql()</tt></h3>
<p id="690497" class="block-content">Finds records using full SQL where <tt>findBySql()</tt>
return an Active Record and <tt>findAllBySql()</tt>returns an array of record objects.
For each column returned, the corresponding Active Record class must define a member variable or
property for each corresponding column name.
<com:TTextHighlighter Language="php" CssClass="source block-content">
class UserRecord2 extends UserRecord
{
    public $another_value;
}
$sql = "SELECT users.*, 'hello' as another_value FROM users";
$users = TActiveRecord::finder('UserRecord2')->findAllBySql($sql);
</com:TTextHighlighter>
</p>
<h3 id="138061"><tt>count()</tt></h3>
<p id="690498" class="block-content">Find the number of matchings records, accepts same parameters as the <tt>findAll()</tt> method.</p>

<h2 id="138051">Inserting and updating records</h2>
<p id="690499" class="block-content">
Add a new record using TActiveRecord is very simple, just create a new Active
Record object and call the <tt>save()</tt> method. E.g.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690161">
$user1 = new UserRecord();
$user1->username = "admin";
$user1->email = "admin@example.com";
$user1->save(); //insert a new record

$data = array('username'=>'admin', 'email'=>'admin@example.com');
$user2 = new UserRecord($data); //create by passing some existing data
$user2->save(); //insert a new record
</com:TTextHighlighter>
<div class="tip"><b class="note">Tip:</b>
If you insert a new record into a MySQL table that has columns
defined with "autoincrement", the Active Record objects will be updated with the new
incremented value.</div>

<p id="690500" class="block-content">
To update a record in the database, just change one or more properties of
the Active Record object that has been loaded from the database and then
call the <tt>save()</tt> method.

<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690162">
$user = UserRecord::finder()->findByName('admin');
$user->email="test@example.com"; //change property
$user->save(); //update it.
</com:TTextHighlighter>
</p>

<p id="710012" class="block-content">
Active Record objects have a simple life-cycle illustrated in the following diagram.
</p>
<img src=<%~ object_states.png %> alt="Active Records Life Cycle" id="fig:cycle.png" class="figure"/>
<p id="690501" class="block-content">
We see that new TActiveRecord objects are created by either using one of the <tt>find*()</tt>
methods or using creating a new instance by using PHP's <tt>new</tt> keyword. Objects
created by a <tt>find*()</tt> method starts with <tt>clean</tt> state. New instance of
TActiveRecord created other than by a <tt>find*()</tt> method starts with <tt>new</tt> state.
Whenever you
call the <tt>save()</tt> method on the TActiveRecord object, the object enters the <tt>clean</tt>
state. Objects in the <tt>clean</tt> becomes <tt>dirty</tt> whenever one of more of its
internal states are changed. Calling the <tt>delete()</tt> method on the object
ends the object life-cycle, no further actions can be performed on the object.
</p>

<h2 id="138052">Deleting existing records</h2>
<p id="690502" class="block-content">
    To delete an existing record that is already loaded, just call the <tt>delete()</tt> method.
    You can also delete records in the database by primary keys without
    loading any records using the <tt>deleteByPk()</tt> method (and equivalently the <tt>deleteAllByPks()</tt> method).
    For example, to delete one or several records with tables using one or more primary keys.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690163">
$finder->deleteByPk($primaryKey); //delete 1 record
$finder->deleteAllByPks($key1,$key2,...); //delete multiple records
$finder->deleteAllByPks(array($key1,$key2,...)); //delete multiple records
</com:TTextHighlighter>

<p id="690503" class="block-content">
For composite keys (determined automatically from the table definitions):
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690164">
$finder->deleteByPk(array($key1,$key2)); //delete 1 record

//delete multiple records
$finder->deleteAllByPks(array($key1,$key2), array($key3,$key4),...);

//delete multiple records
$finder->deleteAllByPks(array( array($key1,$key2), array($key3,$key4), .. ));
</com:TTextHighlighter>

<h3 id="138052a"><tt>deleteAll()</tt> and <tt>deleteBy*()</tt></h3>
<p id="690502a" class="block-content">
To delete by a criteria, use <tt>deleteAll($criteria)</tt> and <tt>deleteBy*()</tt>
with similar syntax to <tt>findAll($criteria)</tt> and <tt>findAllBy*()</tt> as
described above.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690163a">
//delete all records with matching Name
$finder->deleteAll('Name = ?', $name);
$finder->deleteByName($name);

//delete by username and password
$finder->deleteBy_Username_And_Password($name,$pass);
</com:TTextHighlighter>

<h2 id="138053">Transactions</h2>
<p id="690504" class="block-content">All Active Record objects contain the property <tt>DbConnection</tt>
    that can be used to obtain a transaction object.
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690165">
$finder = UserRecord::finder();
$finder->DbConnection->Active=true; //open if necessary
$transaction = $finder->DbConnection->beginTransaction();
try
{
    $user = $finder->findByPk('admin');
    $user->email = 'test@example.com'; //alter the $user object
    $user->save();
    $transaction->commit();
}
catch(Exception $e) // an exception is raised if a query fails
{
    $transaction->rollBack();
}
</com:TTextHighlighter>

<h2 id="142011">Events</h2>
<p id="710013" class="block-content">
The TActiveRecord offers two events, <tt>OnCreateCommand</tt> and <tt>OnExecuteCommand</tt>.
</p>

<p id="710014" class="block-content">The <tt>OnCreateCommand</tt> event is raised when a command is prepared and
parameter binding is completed. The parameter object is <tt>TDataGatewayEventParameter</tt> of which the
<tt>Command</tt> property can be inspected to obtain the SQL query to be executed.
</p>

<p id="710015" class="block-content">
The <tt>OnExecuteCommand</tt> event is raised when a command is executed and the
result from the database was returned. The parameter object is <tt>TDataGatewayResultEventParameter</tt>
of which the <tt>Result</tt> property contains the data return from the database.
The data returned can be changed by setting the <tt>Result</tt> property.
</p>

<h3 id="142016">Logging Example</h3>
<p id="710016" class="block-content">Using the <tt>OnExecuteCommand</tt> we can attach an event handler to log
the entire SQL query executed for a given TActiveRecord class or instance. For example, we define
a base class and override either the <tt>getDbConnection()</tt> or the constructor.
</p>

<com:TTextHighlighter Language="php" CssClass="source block-content">
class MyDb1Record extends TActiveRecord
{
    public function getDbConnection()
    {
        static $conn;
        if($conn===null)
        {
            $conn = new TDbConnection('xxx','yyy','zzz');
            $this->OnExecuteCommand[] = array($this,'logger');
        }
        return $conn;
    }
    public function logger($sender,$param)
    {
        var_dump($param->Command->Text);
    }
}
//alternatively as per instance of per finder object
function logger($sender,$param)
{
    var_dump($param->Command->Text);
}
TActiveRecord::finder('MyRecord')->OnExecuteCommand[] = 'logger';
$obj->OnExecuteCommand[] = array($logger, 'log'); //any valid PHP callback.
</com:TTextHighlighter>

<h1 id="ar_relations">Active Record Relationships</h1>
<com:SinceVersion Version="3.1rc1" />
<p id="690504a" class="block-content">
The Prado Active Record implementation supports the foreign key mappings for database
that supports foreign key constraints. For Active Record relationships to function the
underlying database must support foreign key constraints (e.g. MySQL using InnoDB).
</p>

<p id="710017" class="block-content">
In the following sections we will consider the following table relationships between
<tt>Teams</tt>, <tt>Players</tt>, <tt>Skills</tt> and <tt>Profiles</tt>.
</p>
<img src=<%~ ar_relations.png %> class="figure" />


<p id="710018" class="block-content">The goal is to obtain object models that represent to some degree the entity
relationships in the above figure.
</p>

<img src=<%~ ar_objects.png %> class="figure" />

<p class="block-content">
There is a mismatch between relationships with objects and table relationships.
First there's a difference in representation. Objects handle links by storing references
that are held by the runtime memory-managed environment. Relational databases handle
links by forming a key into another table. Second, objects can easily use collections
to handle multiple references from a single field, while normalization forces
all entity relation links to be single valued. This leads to reversals of the data
structure between objects and tables. The approach taken in the Prado Active Record
design is to use the table foreign key constraints to derive object relationships. This implies
that the underlying database must support foreign key constraints.
</p>
<div class="tip"><b class="note">Tip:</b>
For SQLite database, you may create tables that defines the foreign key
constraints such as the example below. However, these constraints are <b>NOT</b>
enforced by the SQLite database itself.
<com:TTextHighlighter Language="sql" CssClass="source block-content">
CREATE TABLE foo
(
    id INTEGER NOT NULL PRIMARY KEY,
    id2 CHAR(2)
);
CREATE TABLE bar
(
    id INTEGER NOT NULL PRIMARY KEY,
    foo_id INTEGER
        CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE
);
</com:TTextHighlighter>
</div>

<h2 id="142012">Foreign Key Mapping</h2>
<p class="block-content">The entity relationship between the <tt>Teams</tt> and <tt>Players</tt> table is what is known
as an 1-M relationship. That is, one Team may contain 0 or more Players. In terms of
object relationships, we say that a <tt>TeamRecord</tt> object <b>has many</b> <tt>PlayerRecord</tt> objects.
(Notice the reversal of the direction of relationships between tables and objects.)
</p>

<h3 id="142017">Has Many Relationship</h3>
<p id="710020" class="block-content">
We model the <tt>Team</tt> object as the following Active Record classes.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
class TeamRecord extends TActiveRecord
{
    const TABLE='Teams';
    public $name;
    public $location;

    public $players=array();  // this declaration is no longer needed since v3.1.2

    //define the $player member having has many relationship with PlayerRecord
    public static $RELATIONS=array
    (
        'players' => array(self::HAS_MANY, 'PlayerRecord', 'team_name'),
    );

    public static function finder($className=__CLASS__)
    {
        return parent::finder($className);
    }
}
</com:TTextHighlighter>
<p id="710021" class="block-content">
The static <tt>$RELATIONS</tt> property of <tt>TeamRecord</tt> defines that the
property <tt>$players</tt> <b>has many</b> <tt>PlayerRecord</tt>s. Multiple relationships
is permitted by defining each relationship with an entry in the <tt>$RELATIONS</tt>
array where array key for the entry corresponds to the property name.
In <tt>array(self::HAS_MANY, 'PlayerRecord')</tt>, the first element defines the
relationship type, the valid types are <tt>self::HAS_MANY</tt>, <tt>self::HAS_ONE</tt>,
<tt>self::BELONGS_TO</tt> and <tt>self::MANY_TO_MANY</tt>.
The second element is a string <tt>'PlayerRecord'</tt> that corresponds to the
class name of the <tt>PlayerRecord</tt> class.
And the third element 'team_name' refers to the foreign key column in the Players table that
references to the Teams table.
</p>

<div class="note"><b class="note">Note:</b>
As described in the code comment above, since version <b>3.1.2</b>, related properties no longer
need to be explicitly declared. By default, they will be implicitly declared according to
keys of the <tt>$RELATIONS</tt> array. A major benefit of declared related properties implicitly
is that related objects can be automatically loaded in a lazy way. For example, assume we have
a <tt>TeamRecord</tt> instance <tt>$team</tt>. We can access the players via <tt>$team->players</tt>,
even if we have never issued fetch command for players. If <tt>$players</tt> is explicitly declared,
we will have to use the <tt>with</tt> approach described in the following to fetch the player records.
</div>

<p id="710022" class="block-content">
The foreign key constraint of the <tt>Players</tt> table is used to determine the corresponding
<tt>Teams</tt> table's corresponding key names. This is done automatically handled
in Active Record by inspecting the <tt>Players</tt> and <tt>Teams</tt> table definitions.
</p>

<div class="info"><b class="note">Info:</b>
Since version <b>3.1.2</b>, Active Record supports multiple foreign key
references of the same table. Ambiguity between multiple foreign key references to the same table is
resolved by providing the foreign key column name as the 3rd parameter in the relationship array.
For example, both of the following foreign keys <tt>owner_id</tt> and <tt>reporter_id</tt>
references to the same table defined in <tt>UserRecord</tt>.
<com:TTextHighlighter Language="php" CssClass="source block-content">
class TicketRecord extends TActiveRecord
{
     public $owner_id;
	 public $reporter_id;

     public $owner;     // this declaration is no longer needed since v3.1.2
	 public $reporter;  // this declaration is no longer needed since v3.1.2

	 public static $RELATION=array
	 (
	     'owner' => array(self::BELONGS_TO, 'UserRecord', 'owner_id'),
		 'reporter' => array(self::BELONGS_TO, 'UserRecord', 'reporter_id'),
	 );
}
</com:TTextHighlighter>
This is applicable to relationships including <tt>BELONGS_TO</tt>, <tt>HAS_ONE</tt> and
<tt>HAS_MANY</tt>. See section <a href="#142021">Self Referenced Association Tables</a> for solving ambiguity of <tt>MANY_TO_MANY</tt>
relationships.
</div>

<p id="710023" class="block-content">The "has many" relationship is not fetched automatically when you use any of the Active Record finder methods.
You will need to explicitly fetch the related objects as follows. In the code below, both lines
are equivalent and the method names are case insensitive.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
$team = TeamRecord::finder()->withPlayers()->findAll();
$team = TeamRecord::finder()->with_players()->findAll(); //equivalent
</com:TTextHighlighter>
<p id="710024" class="block-content">
The method <tt>with_xxx()</tt> (where <tt>xxx</tt> is the relationship property
name, in this case, <tt>players</tt>) fetches the corresponding PlayerRecords using
a second query (not by using a join). The <tt>with_xxx()</tt> accepts the same
arguments as other finder methods of TActiveRecord, e.g. <tt>with_players('age = ?', 35)</tt>.
</p>

<div class="note"><b class="note">Note:</b>
It is essential to understand that the related objects are fetched using additional
queries. The first query fetches the source object, e.g. the <tt>TeamRecord</tt> in the above example code.
A second query is used to fetch the corresponding related <tt>PlayerRecord</tt> objects.
The usage of the two query is similar to a single query using Left-Outer join with the
exception that null results on the right table
are not returned. The consequence of using two or more queries is that the aggregates
and other join conditions are not feasible using Active Records. For queries outside the
scope of Active Record the <a href="?page=Database.SqlMap">SqlMap Data Mapper</a> may be considered.
</div>

<div class="info"><b class="info">Info:</b>
The above <tt>with</tt> approach also works with implicitly declared related properties (introduced
in version 3.1.2). So what is the difference between the <tt>with</tt> approach and the lazy loading
approach? Lazy loading means we issue an SQL query if a related object is initially accessed and not ready,
while the <tt>with</tt> approach queries for the related objects once for all, no matter the related objects
are accessed or not. The lazy loading approach is very convenient since we do not need to explictly
load the related objects, while the <tt>with</tt> approach is more efficient if multiple records are
returned, each with some related objects.
</div>

<h3 id="142019">Has One Relationship</h3>
<p id="710030" class="block-content">The entity relationship between <tt>Players</tt> and <tt>Profiles</tt> is one to one. That is,
each <tt>PlayerRecord</tt> object <b>has one</b> <tt>ProfileRecord</tt> object (may be none or null).
A <b>has one</b> relationship is nearly identical to a <b>has many</b> relationship with the exception
that the related object is only one object (not a collection of objects).
</p>

<h3 id="142018">Belongs To Relationship</h3>
<p id="710025" class="block-content">The "has many" relationship in the above section defines a collection of foreign
objects. In particular, we have that a <tt>TeamRecord</tt> has many (zero or more)
<tt>PlayerRecord</tt> objects. We can also add a back pointer by adding a property
in the <tt>PlayerRecord</tt> class that links back to the <tt>TeamRecord</tt> object,
effectively making the association bidirectional.
We say that the <tt>$team</tt> property in <tt>PlayerRecord</tt> class <tt>belongs to</tt> a <tt>TeamRecord</tt> object.
The following code defines the complete <tt>PlayerRecord</tt> class with 3 relationships.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
class PlayerRecord extends TActiveRecord
{
    const TABLE='Players';
    public $player_id;
    public $age;
    public $team_name;

    public $team;              // this declaration is no longer needed since v3.1.2
    public $skills=array();    // this declaration is no longer needed since v3.1.2
    public $profile;           // this declaration is no longer needed since v3.1.2

    public static $RELATIONS=array
    (
        'team' => array(self::BELONGS_TO, 'TeamRecord', 'team_name'),
        'skills' => array(self::MANY_TO_MANY, 'SkillRecord', 'Player_Skills'),
        'profile' => array(self::HAS_ONE, 'ProfileRecord', 'player_id'),
    );

    public static function finder($className=__CLASS__)
    {
        return parent::finder($className);
    }
}
</com:TTextHighlighter>
<p id="710026" class="block-content">
The static <tt>$RELATIONS</tt> property of <tt>PlayerRecord</tt> defines that the
property <tt>$team</tt> <b>belongs to</b> a <tt>TeamRecord</tt>.
The <tt>$RELATIONS</tt> array also defines two other relationships that we
shall examine in later sections below.
In <tt>array(self::BELONGS_TO, 'TeamRecord', 'team_name')</tt>, the first element defines the
relationship type, in this case <strong><tt>self::BELONGS_TO</tt></strong>;
the second element is a string <tt>'TeamRecord'</tt> that corresponds to the
class name of the <tt>TeamRecord</tt> class; and the third element 'team_name' refers
to the foreign key of Players referencing Teams.
A player object with the corresponding team object may be fetched as follows.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
$players = PlayerRecord::finder()->with_team()->findAll();
</com:TTextHighlighter>

<p id="710027" class="block-content">
 The method <tt>with_xxx()</tt> (where <tt>xxx</tt> is the relationship property
 name, in this case, <tt>team</tt>) fetches the corresponding <tt>TeamRecords</tt> using
 a second query (not by using a join). The <tt>with_xxx()</tt> accepts the same
arguments as other finder methods of <tt>TActiveRecord</tt>, e.g.
<tt>with_team('location = ?', 'Madrid')</tt>.
</p>

<div class="tip"><b class="note">Tip:</b>
Additional relationships may be fetched by chaining the <tt>with_xxx()</tt> together as the following
example demonstrates.
<com:TTextHighlighter Language="php" CssClass="source block-content">
$players = PlayerRecord::finder()->with_team()->with_skills()->findAll();
</com:TTextHighlighter>
Each <tt>with_xxx()</tt> method will execute an additional SQL query. Every
<tt>with_xxx()</tt> accepts arguments similar to those in the <tt>findAll()</tt> method and is only
applied to that particular relationship query.
</div>

<p id="710028" class="block-content">The "belongs to" relationship of <tt>ProfileRecord</tt> class is defined similarly.</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
class ProfileRecord extends TActiveRecord
{
    const TABLE='Profiles';
    public $player_id;
    public $salary;

    public $player;  // this declaration is no longer needed since v3.1.2

    public static $RELATIONS=array
    (
        'player' => array(self::BELONGS_TO, 'PlayerRecord'),
    );

    public static function finder($className=__CLASS__)
    {
        return parent::finder($className);
    }
}
</com:TTextHighlighter>

<p id="710029" class="block-content">In essence, there exists a "<b>belongs to</b>" relationship for objects corresponding to
entities that has column which are foreign keys. In particular, we see that
the <tt>Profiles</tt> table has a foreign key constraint on the column <tt>player_id</tt>
that relates to the <tt>Players</tt> table's <tt>player_id</tt> column. Thus, the <tt>ProfileRecord</tt>
object has a property (<tt>$player</tt>) that <b>belongs to</b> a <tt>PlayerRecord</tt> object.
Similarly, the <tt>Players</tt> table has a foreign key constraint on the column <tt>team_name</tt> that relates to the
<tt>Teams</tt> table's <tt>name</tt> column.
Thus, the <tt>PlayerRecord</tt> object has a property (<tt>$team</tt>) that <b>belongs to</b> a
<tt>TeamRecord</tt> object.
</p>

<h3 id="142020">Parent Child Relationships</h3>
<p id="710031" class="block-content">A parent child relationship can be defined using a combination of <tt>has many</tt> and <tt>belongs to</tt>
relationship that refers to the same class. The following example shows a parent children relationship between
"categories" and a "parent category".
</p>

<com:TTextHighlighter Language="php" CssClass="source block-content">
class Category extends TActiveRecord
{
    public $cat_id;
    public $category_name;
    public $parent_cat_id;

    public $parent_category;            // this declaration is no longer needed since v3.1.2
    public $child_categories=array();   // this declaration is no longer needed since v3.1.2

    public static $RELATIONS=array
    (
        'parent_category' => array(self::BELONGS_TO, 'Category', 'parent_cat_id'),
        'child_categories' => array(self::HAS_MANY, 'Category', 'parent_cat_id'),
    );
}
</com:TTextHighlighter>

<h3 id="144007">Query Criteria for Related Objects</h3>
<p id="720012" class="block-content">
In the above, we show that an Active Record object can reference to its related objects by
declaring a static class member $RELATIONS which specifies a list of relations. Each relation
is specified as an array consisting of three elements: relation type, related AR class name,
and the foreign key(s). For example, we use <tt>array(self::HAS_MANY, 'PlayerRecord', 'team_name')</tt>
to specify the players in a team. There are two more optional elements that can be specified
in this array: query condition (the fourth element) and parameters (the fifth element).
They are used to control how to query for the related objects. For example, if we want to obtain
the players ordered by their age, we can specify <tt>array(self::HAS_MANY, 'PlayerRecord', 'team_name', 'ORDER BY age')</tt>.
If we want to obtain players whose age is smaller than 30, we could use
<tt>array(self::HAS_MANY, 'PlayerRecord', 'team_name', 'age<:age', array(':age'=>30))</tt>. In general,
these two additional elements are similar as the parameters passed to the <tt>find()</tt> method in AR.
</p>



<h2 id="142013">Association Table Mapping</h2>
<p id="710032" class="block-content">
Objects can handle multivalued fields quite easily by using collections as field values.
Relational databases don't have this feature and are constrained to single-valued fields only.
When you're mapping a one-to-many association you can handle this using <b>has many</b> relationships,
essentially using a foreign key for the single-valued end of the association.
But a many-to-many association can't do this because there is no single-valued end to
hold the foreign key.
</p>
<p id="710033" class="block-content">
The answer is the classic resolution that's been used by relational data people
for decades: create an extra table (an association table) to record the relationship.
The basic idea is using an association table to store the association. This table
has only the foreign key IDs for the two tables that are linked together, it has one
row for each pair of associated objects.
</p>
<p id="710034" class="block-content">
The association table has no corresponding in-memory object and its primary key is the
compound of the two primary keys of the tables that are associated.
In simple terms, to load data from the association table you perform two queries (in general, it may also be achieved using one query consisting of joins).
Consider loading the <tt>SkillRecord</tt> collection for a list <tt>PlayerRecord</tt> objects.
In this case, you do queries in two stages.
The first stage queries the <tt>Players</tt> table to find all the rows of the players you want.
The second stage finds the <tt>SkillRecord</tt> object for the related player ID for each row
in the <tt>Player_Skills</tt> association table using an inner join.
</p>

<p id="710035" class="block-content">The Prado Active Record design implements the two stage approach. For the
<tt>Players</tt>-<tt>Skills</tt> M-N (many-to-many) entity relationship, we
define a <b>many-to-many</b> relationship in the <tt>PlayerRecord</tt> class and
in addition we may define a <b>many-to-many</b> relationship in the <tt>SkillRecord</tt> class as well.
The following sample code defines the complete <tt>SkillRecord</tt> class with a
many-to-many relationship with the <tt>PlayerRecord</tt> class. (See the <tt>PlayerRecord</tt>
class definition above to the corresponding many-to-many relationship with the <tt>SkillRecord</tt> class.)
</p>

<com:TTextHighlighter Language="php" CssClass="source block-content">
class SkillRecord extends TActiveRecord
{
    const TABLE='Skills';
    public $skill_id;
    public $name;

    public $players=array();    // this declaration is no longer needed since v3.1.2

    public static $RELATIONS=array
    (
        'players' => array(self::MANY_TO_MANY, 'PlayerRecord', 'Player_Skills'),
    );

    public static function finder($className=__CLASS__)
    {
        return parent::finder($className);
    }
}
</com:TTextHighlighter>

<p id="710036" class="block-content">
The static <tt>$RELATIONS</tt> property of SkillRecord defines that the
property <tt>$players</tt> has many <tt>PlayerRecord</tt>s via an association table '<tt>Player_Skills</tt>'.
In <tt>array(self::MANY_TO_MANY, 'PlayerRecord', 'Player_Skills')</tt>, the first element defines the
relationship type, in this case <strong><tt>self::MANY_TO_MANY</tt></strong>,
the second element is a string <tt>'PlayerRecord'</tt> that corresponds to the
class name of the <tt>PlayerRecord</tt> class, and the third element is the name
of the association table name.
</p>

<div class="note"><b class="note">Note:</b>
Prior to version <b>3.1.2</b> (versions up to 3.1.1), the many-to-many relationship was
defined using <tt>self::HAS_MANY</tt>. For version <b>3.1.2</b> onwards, this must be changed
to <tt>self::MANY_TO_MANY</tt>. This can be done by searching for the <tt>HAS_MANY</tt> in your
source code and carfully changing the appropriate definitions.
</div>

<p id="710037" class="block-content">
A list of player objects with the corresponding collection of skill objects may be fetched as follows.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
$players = PlayerRecord::finder()->withSkills()->findAll();
</com:TTextHighlighter>
<p id="710038" class="block-content">
The method <tt>with_xxx()</tt> (where <tt>xxx</tt> is the relationship property
name, in this case, <tt>Skill</tt>) fetches the corresponding <tt>SkillRecords</tt> using
a second query (not by using a join). The <tt>with_xxx()</tt> accepts the same
arguments as other finder methods of <tt>TActiveRecord</tt>.
</p>

<h3 id="142021">Self Referenced Association Tables</h3>
<p id="710039" class="block-content">
For self referenced association tables, that is, the association points to the same
table. For example, consider the <tt>items</tt> table with M-N related
item via the <tt>related_items</tt> association table. The syntax in the following
example is valid for a PostgreSQL database. For other database, consult their respective documentation for
defining the foreign key constraints.
<com:TTextHighlighter Language="sql" CssClass="source block-content">
CREATE TABLE items
(
  "item_id" SERIAL,
  "name" VARCHAR(128) NOT NULL,
  PRIMARY KEY("item_id")
);
CREATE TABLE "related_items"
(
  "item_id" INTEGER NOT NULL,
  "related_item_id" INTEGER NOT NULL,
  CONSTRAINT "related_items_pkey" PRIMARY KEY("item_id", "related_item_id"),
  CONSTRAINT "related_items_item_id_fkey" FOREIGN KEY ("item_id")
    REFERENCES "items"("item_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT "related_items_related_item_id_fkey" FOREIGN KEY ("related_item_id")
    REFERENCES "items"("item_id")
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE
);
</com:TTextHighlighter>

<p id="710040" class="block-content">The association table name in third element of the relationship array may
contain the foreign table column names. The columns defined in the association
table must also be defined in the record class (e.g. the <tt>$related_item_id</tt> property
corresponds to the <tt>related_item_id</tt> column in the <tt>related_items</tt> table).
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
class Item extends TActiveRecord
{
    const TABLE="items";
    public $item_id;
    public $details;

    //additional foreign item id defined in the association table
    public $related_item_id;
    public $related_items=array();    // this declaration is no longer needed since v3.1.2

    public static $RELATIONS=array
    (
        'related_items' => array(self::MANY_TO_MANY,
            'Item', 'related_items.related_item_id'),
    );
}
</com:TTextHighlighter>
<div class="tip"><b class="note">Tip:</b>
Compound keys in the foreign table can
be specified as comma separated values between brackets. E.g.
<tt>'related_items.(id1,id2)'</tt>.
</div>

<!---
<h2 id="142014">Adding/Removing/Updating Related Objects</h2>

<p id="710041" class="block-content">Related objects can be simply inserted/updated by first adding those related objects to
the current source object (i.e. the object currently been worked on) and then call
the <tt>save()</tt> method on the source object. The related object's references
and the association reference (if required) will be added and/or updated.
For example, to add two new players to the team (assuming that 'Team A' exists), we can simply do the following.
</p>

<com:TTextHighlighter Language="php" CssClass="source block-content">
$team = TeamRecord::finder()->findByPk('Team A');
$team->players[] = new PlayerRecord(array('age'=>20));
$team->players[] = new PlayerRecord(array('age'=>25));
$team->save();
</com:TTextHighlighter>
<p id="710042" class="block-content">
Since the <tt>TeamRecord</tt> class contains a <b>has many</b> relationship with the <tt>PlayerRecord</tt>,
then saving a <tt>TeamRecord</tt> object will also update the corresponding foreign objects in <tt>$players</tt> array.
That is, the objects in <tt>$players</tt> are inserted/updated in the database and the
<tt>$team_name</tt> property of those objects will contain the foreign key value that corresponds to the <tt>$team</tt> object's primary key value.
</p>

<p id="710043" class="block-content">To delete a particular foreign object (or any Active Record object), simply call
the object's <tt>delete()</tt> method. You may setup the database table's foreign key constraints such that
when deleting a particular data in the database it will delete the referenced data as well (it may also be achieved using database
triggers). E.g. such as having a "<tt>ON DELETE CASCADE</tt>" constraint.
Deleting foreign objects by either setting the property value to null or removing the object from an array will <b>NOT</b>
remove the corresponding data in the database.
</p>

<p id="710044" class="block-content">To remove associations for the many-to-many relationships via an association table, an Active Record
that corresponds to the association table can be used. Then the association can be removed by calling the <tt>deleteByPk()</tt> method, for example:
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
PlayerSkillAssocation::finder()->deleteByPk(array('fk1','fk2'));
//where 'fk1' is the primary key value of a player
// and 'fk2' is the primary key value of a skill
</com:TTextHighlighter>
--->

<h2 id="142015">Lazy Loading Related Objects</h2>

<div class="note"><b class="note">Note:</b>
Implicitly declared related properties introduced in version 3.1.2 automatically have lazy
loading feature. Therefore, the lazy loading technique described in the following is no longer
needed in most of the cases, unless you want to manipulate the related objects through getter/setter.
</div>

<p id="710045" class="block-content">Using the <tt>with_xxx()</tt> methods will load the relationship record on demand. Retrieving the
related record using lazy loading (that is, only when those related objects are accessed) can be
achieved by using a feature of the <tt>TComponent</tt> that provides accessor methods. In particular,
we define a pair of getter and setter methods where the getter method will retrieve the relationship
conditionally. The following example illustrates that the <tt>PlayerRecord</tt> can retrieve its
<tt>$skills</tt> foreign objects conditionally.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
class PlayerRecord extends BaseFkRecord
{
    //... other properties and methods as before

    private $_skills; //change to private and default as null

    public function getSkills()
    {
        if($this->_skills===null && $this->player_id !==null)
        {
            //lazy load the skill records
            $this->setSkills($this->withSkills()->findByPk($this->player_id)->skills);
        }
        else if($this->_skills===null)
        {
            //create new TList;
            $this->setSkills(new TList());
        }

        return $this->_skills;
    }

    public function setSkills($value)
    {
        $this->_skills = $value instanceof TList ? $value : new TList($value);
    }
}
</com:TTextHighlighter>
<p id="710046" class="block-content">We first need to change the <tt>$skills=array()</tt> declaration to a private property
<tt>$_skills</tt> (notice the underscore) and set it to null instead. This allows us
to define the <tt>skills</tt> property using getter/setter methods
(see <a href="?page=Fundamentals.Components">Components</a> for details). The <tt>getSkills()</tt>
getter method for the <tt>skills</tt> property will lazy load the corresponding skills foreign record
when it is used as follows. Notice that we only do a lazy load when its <tt>$player_id</tt> is
not null (that is, when the record is already fetched from the database or player id was already set).
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
$player = PlayerRecord::finder()->findByPk(1);
var_dump($player->skills); //lazy load it on first access
var_dump($player->skills[0]); //already loaded skills property
$player->skills[] = new SkillRecord(); //add skill
</com:TTextHighlighter>

<p id="710047" class="block-content">The <tt>setSkills()</tt> ensures that the <tt>skills</tt> property will always be a TList.
Using a TList allows us to set the elements of the <tt>skills</tt> property as if they were
arrays. E.g. <tt>$player->skills[] = new SkillRecord()</tt>. If <tt>array</tt> was used, a PHP error
will be thrown.
</p>

<h2 id="144006">Column Mapping</h2>
<p id="720013" class="block-content">
Since v3.1.1, Active Record starts to support column mapping. Column mapping allows developers
to address columns in Active Record using a more consistent naming convention. In particular,
using column mapping, one can access a column using whatever name he likes, rather than limited by
the name defined in the database schema.
</p>
<p id="720014" class="block-content">
To use column mapping, declare a static array named <tt>COLUMN_MAPPING</tt> in the Active Record class.
The keys of the array are column names (called <i>physical column names</i>) as defined in the database
schema, while the values are corresponding property names (called <i>logical column names</i>) defined
in the Active Record class. The property names can be either public class member variable names or
component property names defined via getters/setters. If a physical column name happens to be the same
as the logical column name, they do not need to be listed in <tt>COLUMN_MAPPING</tt>.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content">
class UserRecord extends TActiveRecord
{
	const TABLE='users';
	public static $COLUMN_MAPPING=array
	(
		'user_id'=>'id',
		'email_address'=>'email',
		'first_name'=>'firstName',
		'last_name'=>'lastName',
	);
	public $id;
	public $username; // the physical and logical column names are the same
	public $email;
	public $firstName;
	public $lastName;
	//....
}
</com:TTextHighlighter>
<p id="720015" class="block-content">
With the above column mapping, we can address <tt>first_name</tt> using <tt>$userRecord->firstName</tt>
instead of <tt>$userRecord->first_name</tt>. This helps separation of logic and model.
</p>

<h2 id="138054">References</h2>
<ul id="u3" class="block-content">
    <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>,
    Addison Wesley, 2002.</li>
	<li>B. Venners with B. Eckel. <i><a href="http://www.artima.com/intv/abstract3.html">Inappropriate Abstractions - A Conversation with Anders Hejlsberg, Part VI.</a></i>
	Artima Developer, 2003.
	</li>

</ul>

<div class="last-modified">$Id$</div></com:TContent>