The db_object_child model works in conjunction with the db_object model. Before reviewing this help you should first make sure you are familiar with the basics of defining a db_object.  As explained in the db_object help, in addition to a primary file, a db_object may be related to any number of secondary (dependent) files. Each of these secondary files is managed by a class generated using the db_object_child model.

Many of the inputs for the db_object_child model are similar to those used by the db_object model so in this help we don't go into details on these common inputs. Instead, the focus of this help will be to describe the additional inputs that are specific to the db_object_child model.

Specification Fields Specific to db_object_child components

Collection Name

The collection name is used in error messages that refer to the collection of db_object rows managed by the db_object_child class. If you wish to translate this collection name into multiple languages it should be preceded by colon (:). The colon should be followed by the name of a field within a translation class. If you have not specified a translation class, this defaults to the name of the class itself. You can also override the class by using the format :translation_class->variable.

Combine Inserts

When this feature is checked, multiple table rows will be inserted using a single SQL insert statement. While this is more efficient than performing multiple consecutive insert operations, it could be problematic to utilize this feature. The main drawback of choosing this option is that PDO is not capable of returning all of the auto-generated keys created during a combined insert statement. Therefore, the db_object must assume that these auto-generated keys are sequential. While this is generally the case, it is not guaranteed.  This really only matters in situations where the user decides to perform an update immediately after performing the insert. In such a case, and if the auto-generated keys were not sequential, an SQL error might occur when the user performs the update.

In summary, this option should be chosen in situations where high performance is important or in cases where users are unlikely to perform an update on an object that was just inserted.

Also note that the database you are using must support the ability to insert multiple database rows in one statement. This is indicated using the combine_inserts value set by the database adapter class. The Combine Insert value set in the object parameter will be overruled in cases where the database does not support this feature.

Translate Headings

Select this option if the db_object_child field references are to be translated using a translation class. This is important for multi-lingual applications.

Defining Key Fields

When defining the columns to be used to link the db_object_child table to the primary table it is necessary to specify all of the columns that make up this key. In most cases, the secondary file will reference two key fields:

  1. The first key is used to link the secondary table to the primary table.
  2. The second key is used to make each row identifier unique and also determines the order of the rows returned in the object.

Consider the following portion of the db_object_child definition for film_actor:

Secondary table definition

Notice that the film_id includes the DB Column Parms primary:1 to link this column to the film_id of the film table. The actor_id includes primary:2, this is necessary to ensure that the combined key film_id + actor_id is unique.

Further notice that the first_name and last_name fields are indicated with a DB Data Type of "derived". This means that these fields are not actually on the film_actor table but are derived from other sources. If we refer to the update constraint, we can see that these values will automatically be looked up during the process of executing this constrain check. These additional lookups also take place whenever a db_object is displayed.

Finally, make note of the Auto Assign Field for last_update. This will cause this field to be automatically assigned whenever there is an update or insert of the film_actor record.

Populating the Child Key Values

In most cases, the primary key of the child table consists of one additional component beyond the primary key of its parent as depicted here:

Relationship between parent and child keys

When inserting the child table rows, the "prefix" components of the primary key are assigned automatically. These prefix components will exactly match the primary key value of the parent record. The suffix component(s) of the child row primary key must be populated by the generated db_object_child class. These suffix values must be unique, within a given prefix.

There are two common scenarios for assigning the suffix values as described next.

Child Suffix Components are Passed into the Object

With this scenario, the suffix component of the child table contains data values that are supplied by the user (or the caller of the object). Here we show two example of this type of child table:

Child rows where suffix is a data value

In this example, the primary key of the parent table is a column named film_id.

The first child table, named film_categories, implements the primary key film_id + category_id.

The second child table, named film_actors, implements the primary key film_id + actor_id.

In both of these examples, the suffix of these primary keys happen to be foreign keys defined on the category and actor tables, respectively. Since these values must be pre-defined, we assist the user in selecting these values. Since there are a relatively small number of film categories, we use a select field generated by the model select_db_column_values to supply this list. On the other hand, since the number of actors is quite large, we add a helproutine to the actor_id field to allow the user to select an actor from a popup browse page. Selecting an actor also populates the derived actor name values.

It should be noted that there is no requirement for the child table key suffix to be related to a foreign key. In some scenarios, the suffix value will be supplied by the user as arbitrary values. For example, suppose the secondary file is used to define a list if cities where the film was produced. It might be possible for the user to simply type in the city names, rather than having to predefine all possible cities so that these can be looked up. The framework automatically prevents duplicate primary key values from being stored.

Child Suffix Components as Relative Numbers

Another common scenario is when the suffix of the child key does not have any meaning, except perhaps to suggest an order for the child records. Consider the following form that is used to define shipment information for an order.

Shipment entry form

Since a single shipment can be broken down into any number of packages, we have defined packages as a child entity within the shipment object. In this case, each package does not have a natural unique identifier so we simply want to use a sequential integer value as the suffix component of the package primary key. Here we show the definition of the package table.

Package table definition

As we have learned, the invoice number on this child table will be populated automatically since this value must match the primary key of the parent (shipment) table. We can see that the weight, height, length and width will be populated by the user by entering these values into a flexgrid container, but how does the package_number get populated? Here we show a portion of the field definitions used in the db_object_child model which defines the package definiton:

Package number is automatically assigned the row number.

Notice that the package_number has been "auto assigned" the row_num property. This tells the GenHelm runtime associated with the database object to automatically populate this field using the occurrence of the package plus 1. We add 1 since the occurrences are numbered starting from zero, however we want the database table to be numbered starting from 1.

Accessing Child Information from a Parent

Referring back to the screen that shows the Shipment entry form you will notice that this form contains a some derived values including the number of packages and the total weight of the order. Since this information can be derived from the package data entered by the user we can make these values readonly and calculate these automatically. This is done be adding the following function to the parent object.

function before_update_or_insert($method) {
  $this->package_count = $this->packages->get_child_count(); 
  $this->total_weight = 0;
  if ($this->package_count > 0) {
    foreach ($this->packages->get_children() as $package) {
      $this->total_weight += $package->get_weight();    
Sample db_object_child definition
Sample db_object_child class