fleaphp Development Guide-7. Data Table Association

Development Guide | data

A data Table association is a logical relationship between records in two or more data tables.

For example:

• Every citizen has an ID number.
• Each author has written many (0-n) books, and each book has multiple (1-n) authors
• Each article has multiple (0-n) reviews
• Every comment belongs to an article

Currently, fleaphp supports four types of data table associations, respectively:

• Has_one: each record in the current table has up to one (0–1) associated record
• Has_many: each record in the current table has more than one (0-n) associated record
• Many_to_many: each record in the current table is associated with multiple (0-N) records of other tables
• belongs_to: each record in the current table belongs to a record in another table

In fleaphp, you can define a number of different associations for each table data entry, for example:

<?phpload_class('FLEA_Db_TableDataGateway');class Model_ProductClass extends FLEA_Db_TableDataGateway{    var $tableName = 'product_class';    var $primaryKey = 'pclass_id';    var $hasMany = array(        array(            'tableClass' => 'Model_Permissions',            'foreignKey' => 'pclass_id',            'mappingName' => 'permissions',        ),        array(            'tableClass' => 'Model_Products',            'foreignKey' => 'pclass_id',            'mappingName' => 'products',            'enabled' => false,        ),    );    var $hasOne = array(        array(            'tableClass' => 'Model_ProductClassAdverts',            'foreignKey' => 'pclass_id',            'mappingName' => 'advert',        )    );}?>

Terms

Before you go into the details of these four associations, learn some of the terms that will be used later.

• Association: An association is a relationship in which a relationship belongs to a data table. For example, the users table might have one or more associations.
• primary table: for an association, the data table that owns the association is the primary table. For example, the posts table defines a Many_to_many association. So here (the Association), the posts is the primary table.
• Association table: in an association, an association table is another table other than the main table.
• foreign key: in the database principle, the foreign key meaning is very complex. However, in the Database association function in the fleaphp framework, the foreign key refers to a field in a record that is used to correlate another record. For example, the user_id field in the Profile table is the field used to associate the users table. This user_id field is a foreign key.
• Intermediate Table: in the Many_to_many Association, in addition to the primary and associated tables, another table is needed to hold the correlation between the records of the two tables. This table is called an intermediate table.

After understanding these terms, let's look at the detailed explanations for each of the associations.

Has_one One-to-one Association

Has_one is a very simple association relationship. Indicates that one record has another record. The two records are in two data tables, respectively.

Example

In an information management system, the users table is used to store basic information about user accounts, such as user names, passwords, and so on. The Profiles table is used to store the user's personal information, such as home address, postal code, and so on.

Because each user (a record in a users table) has a corresponding personal information (a record in a profiles table). Therefore, we can define a Has_one association for the users table.

Obviously, the records of the users table have a record of the Profiles table. Therefore, when a record in the users table is deleted, the associated record in the Profiles table owned by the deleted record is also automatically deleted.

Table Definition

In the Has_one Association, the foreign key is required to be placed in the association table.

A simplified version of the table definition for the above example is as follows:

Users table:

• USER_ID primary key field
• Username

Profiles table:

• PROFILE_ID PRIMARY key field
• Address
• Postcode
• user_id foreign key field

The corresponding MySQL code is as follows:

CREATE TABLE `users` (    `user_id` INT NOT NULL AUTO_INCREMENT ,    `username` VARCHAR( 32 ) NOT NULL ,    PRIMARY KEY ( `user_id` ));CREATE TABLE `profiles` (    `profile_id` INT NOT NULL AUTO_INCREMENT ,    `address` VARCHAR( 128 ) NOT NULL ,    `postcode` VARCHAR( 8 ) NOT NULL ,    `user_id` INT NOT NULL ,    PRIMARY KEY ( `profile_id` ));

The corresponding Flea_db_tabledatagateway inheritance class is defined as follows:

<?phpload_class('FLEA_Db_TableDataGateway');class Users extends FLEA_Db_TableDataGateway{    var $tableName = 'users';    var $primaryKey = 'user_id';    var $hasOne = array(        'tableClass' => 'Profiles',        'foreignKey' => 'user_id',        'mappingName' => 'profile',    );}class Profiles extends FLEA_Db_TableDataGateway{    var $tableName = 'profiles';    var $primaryKey = 'profile_id';}?>

Demo Code

<?php// 首先插入一条 users 记录$modelUsers =& new Users();$newUserId = $modelUsers->create(    array('username' => 'dualface'));// 接下来，再插入一条 profiles 记录$modelProfiles =& new Profiles();$modelProfiles->create(    array(        'address' => 'SiChuan ZiGong',        'postcode' => '643000',        'user_id' => $newUserId    ));// OK，我们现在尝试读取一条 users 记录，看看会得到什么结果$user = $modelUsers->find($newUserId);dump($user);?>

The result is interesting, the extra ' profile ' field is exactly what we just inserted into the Profiles table:

Array(    [user_id] => 1    [username] => dualface    [ref___id] => 1    [profile] => Array        (            [profile_id] => 1            [address] => SiChuan ZiGong            [postcode] => 643000            [user_id] => 1            [ref___id] => 1        ))

Description

In the example above, there is a $HASONE member variable in theUsers class. The variable is an array:

var $hasOne = array(    'tableClass' => 'Profiles',    'foreignKey' => 'user_id',    'mappingName' => 'profile',);

$hasOne member variables are used to specify Has_one associations for a table database portal.

In the associated definition,tableclass Specifies the table data entry class name for the associated table,ForeignKey Specifies the foreign key field name, and the mappingName Specifies what field is used to map the data of the associated table in the query results of the primary table.