程序员人生 网站导航

Zend Framework教程-Zend_Db-数据库操作5-多关系的数据库操作翻译

栏目:ZendFramework时间:2013-12-14 21:51:53

Introduction概述

Tables have relationships to each other in a relational database. An entity in one table can be linked to one or more entities in another table by using referential integrity constraints defined in the database schema.

The Zend_Db_Table_Row class has methods for querying related rows in other tables.


在关系型数据库中,表与表之间也会存在关联关系。一个表中的实体可以关联另一个表中的一个或者多个实体。可以使用Zend_Db_Table_Row提供的方法进行查询操作。


dependent table :与父表相关的表被称为从属表 

RowSets : 记录集,行结果集

Row Object : Row对象,行对象

rule key :  规则键 (或者不做翻译)

rule: 规则 (或者不做翻译)

Defining Relationships定义关系

Define classes for each of your tables, extending the abstract classZend_Db_Table_Abstract, as described inthis chapter. Also seethis chapter for a description of the example database for which the following example code is designed.

Below are the PHP class definitions for these tables:


可以通过继承抽象类Zend_Db_Table_Abstract为每一个表建立一个对应的类。下面是本节用到的相关表以及对应的PHP类


class Accounts extends Zend_Db_Table_Abstract{    protected $_name            = 'accounts';    protected $_dependentTables = array('Bugs');} class Products extends Zend_Db_Table_Abstract{    protected $_name            = 'products';    protected $_dependentTables = array('BugsProducts');} class Bugs extends Zend_Db_Table_Abstract{    protected $_name            = 'bugs';     protected $_dependentTables = array('BugsProducts');     protected $_referenceMap    = array(        'Reporter' => array(            'columns'           => 'reported_by',            'refTableClass'     => 'Accounts',            'refColumns'        => 'account_name'        ),        'Engineer' => array(            'columns'           => 'assigned_to',            'refTableClass'     => 'Accounts',            'refColumns'        => 'account_name'        ),        'Verifier' => array(            'columns'           => array('verified_by'),            'refTableClass'     => 'Accounts',            'refColumns'        => array('account_name')        )    );} class BugsProducts extends Zend_Db_Table_Abstract{    protected $_name = 'bugs_products';     protected $_referenceMap    = array(        'Bug' => array(            'columns'           => array('bug_id'),            'refTableClass'     => 'Bugs',            'refColumns'        => array('bug_id')        ),        'Product' => array(            'columns'           => array('product_id'),            'refTableClass'     => 'Products',            'refColumns'        => array('product_id')        )    ); }


If you use Zend_Db_Table to emulate cascadingUPDATE andDELETE operations, declare the$_dependentTables array in the class for the parent table. List the class name for each dependent table. Use the class name, not the physical name of theSQL table.

如果要通过Zend_Db_Table 进行UPDATE 和DELETE 操作。你需要在类的父类中声明$_dependentTables数组。列出每个从属表的对应的类名。使用类名而不是数据表的真实名称

Note: Skip declaration of  $_dependentTables if you use referential integrity constraints in theRDBMS server to implement cascading operations. Seethis chapter for more information. 

注意:$_dependentTables声明的具体方法在后续会有详细介绍

Declare the $_referenceMap array in the class for each dependent table. This is an associative array of reference "rules". A reference rule identifies which table is the parent table in the relationship, and also lists which columns in the dependent table reference which columns in the parent table.

在类中声明一个$_referenceMap数组。数组中包含每一个从属表。这个关联数组称之为 引用规则( reference   " rules")。一个引用规则描述了和附表的关联关系以及依赖父表的哪些列。


The rule key is a string used as an index to the $_referenceMap array. This rule key is used to identify each reference relationship. Choose a descriptive name for this rule key. It's best to use a string that can be part of aPHP method name, as you will see later.

$_referenceMap array的索引是可以随意,但是通常是一个可以标识引用关系的名称,可以使用一个PHP方法名称的一部分。具体你会在后面看到。


In the example PHP code above, the rule keys in the Bugs table class are:'Reporter','Engineer','Verifier', and'Product'.

例如:Bugs 中$_referenceMap数组的key的名称为'Reporter', 'Engineer', 'Verifier', and 'Product'.


The value of each rule entry in the $_referenceMap array is also an associative array. The elements of this rule entry are described below:


  • columns => A string or an array of strings naming the foreign key column names in the dependent table.

    It's common for this to be a single column, but some tables have multi-column keys.

  • refTableClass => The class name of the parent table. Use the class name, not the physical name of theSQL table.

    It's common for a dependent table to have only one reference to its parent table, but some tables have multiple references to the same parent table. In the example database, there is one reference from thebugs table to the products table, but three references from thebugs table to theaccounts table. Put each reference in a separate entry in the$_referenceMap array.

  • refColumns => A string or an array of strings naming the primary key column names in the parent table.

    It's common for this to be a single column, but some tables have multi-column keys. If the reference uses a multi-column key, the order of columns in the'columns' entry must match the order of columns in the'refColumns' entry.

    It is optional to specify this element. If you don't specify the refColumns, the columns reported as the primary key columns of the parent table are used by default.

  • onDelete => The rule for an action to execute if a row is deleted in the parent table. Seethis chapter for more information.

  • onUpdate => The rule for an action to execute if values in primary key columns are updated in the parent table. Seethis chapter for more information.


$_referenceMap数组的值是一个关联数组。各个元素的功能描述如下:


columns :是一个字符串或者数组。内容是从属表的外键名称。通常是指定的一列,但是也可以是表中多个列

refTableClass :父表的类的名称。使用对应的类名,而不是数据表名。

 通常,一个表和父表只有一个引用关系。但是有的表和父表之间会有多个引用关系

 
示例数据库中,从bugs 表到products 表的存在一个引用关系,从bugs 表到accounts 表存在三个引用关系。 $_referenceMap 数组需要存放他们之间的所有的引用关系

refColumns :一个字符串或者数组。是在父表的主键的列名列表

通常,是表的一列。但是有些表会有多列。如果引用关系使用了多列,这些列的实体必须在'refColumns' 中指定,并且顺序一致。

选项是可选的。如果不指定refColumns, 默认为父表的主键

onDelete :如果要在父表中执行删除行的操作。需要设置参数。详情可以参考后续章节

onUpdate :如果要更新父表的主键,需要设置此选项。详情可以参考后续章节

Fetching a Dependent Rowset获取行结果集


If you have a Row object as the result of a query on a parent table, you can fetch rows from dependent tables that reference the current row. Use the method:

如果把父表的查询结果作为行对象。你还可以从当前行引用的从属表中获取数据   ,使用如下方法:

      $row->findDependentRowset($table, [$rule]);

This method returns a Zend_Db_Table_Rowset_Abstract object, containing a set of rows from the dependent table$table that refer to the row identified by the $row  object.

The first argument $table can be a string that specifies the dependent table by its class name. You can also specify the dependent table by using an object of that table class.

此方法返回一个Zend_Db_Table_Rowset_Abstract 对象。 从 引用被$row 对象表示的行的从属表$table中获取行结果集


第一个参数$table可以是一个指定的从属表的类名字符串。你也可以使用该表类的一个对象作为从属表。


The second argument $rule is optional. It is a string that names the rule key in the$_referenceMap array of the dependent table class.

 If you don't specify a rule, the first rule in the array that references the parent table is used. If you need to use a rule other than the first, you need to specify the key.

In the example code above, the rule key is not specified, so the rule used by default is the first one that matches the parent table. This is the rule'Reporter'.


 第二个参数$rule 是可选的。通常采用$_referenceMap数组指定的rule key。 如果不指定rule,会使用父表的第一个rule。如果你不想只用第一个,你需要指定指定的key。


Example #1 Fetching a Dependent Rowset获取一个行结果集
 

This example shows getting a Row object from the table Accounts, and finding the Bugs reported by that account.

从Accounts表获取行对象,然后通过该帐户报告的Bugs 。

  1. $accountsTable = new Accounts();
  2. $accountsRowset = $accountsTable->find(1234);
  3. $user1234 = $accountsRowset->current();
  4.  
  5. $bugsReportedByUser = $user1234->findDependentRowset('Bugs');

上面的示例代码,没有指定索引,默认采用父表第一个匹配的列。这里是‘’Reporter‘’


Example #2 Fetching a Dependent Rowset By a Specific Rule通过指定的rule来获取依赖行结果集


This example shows getting a Row object from the table Accounts, and finding the Bugs assigned to be fixed by the user of that account. The rule key string that corresponds to this reference relationship in this example is'Engineer'.

此举例中,是从Accounts表中获取行对象,然后从Bugs 表中获取指定帐户已经修复的bug。 rule key 字符串对应的是这个引用关系的 'Engineer'.

  1. $accountsTable = new Accounts();
  2. $accountsRowset = $accountsTable->find(1234);
  3. $user1234 = $accountsRowset->current();
  4.  
  5. $bugsAssignedToUser = $user1234->findDependentRowset('Bugs','Engineer');


You can also add criteria, ordering and limits to your relationships using the parent row's select object.

你可以添加其它处理条件,进行排序或者限制从父表获取查询结果集数目


Example #3 Fetching a Dependent Rowset using a Zend_Db_Table_Select 使用Zend_Db_Table_Select 获取依赖的行结果集


This example shows getting a Row object from the table Accounts, and finding the Bugs assigned to be fixed by the user of that account, limited only to 3 rows and ordered by name.

此举例是从Accounts表中获取行结果集,然后获取用户已修复Bugs 。按照名称排序并且仅仅取3条记录

  1. $accountsTable = new Accounts();
  2. $accountsRowset = $accountsTable->find(1234);
  3. $user1234 = $accountsRowset->current();
  4. $select = $accountsTable->select()->order('name ASC')
  5.                                   ->limit(3);
  6.  
  7. $bugsAssignedToUser = $user1234->findDependentRowset('Bugs',
  8.                                                      'Engineer',
  9.                                                      $select);


Alternatively, you can query rows from a dependent table using a special mechanism called a "magic method".Zend_Db_Table_Row_Abstract invokes the method: 

findDependentRowset('<TableClass>', '<Rule>') if you invoke a method on the Row object matching either of the following patterns:


另外,你可以使用Zend_Db_Table_Row_Abstract提供的魔术方法 从从属表查询行结果集合。 

如果你想使用魔术方法获取行结果集,可以使用:findDependentRowset('<TableClass>', '<Rule>')


  • $row->find<TableClass>()

  • $row->find<TableClass>By<Rule>()


In the patterns above, <TableClass> and <Rule> are strings that correspond to the class name of the dependent table, and the dependent table's rule key that references the parent table.

Note: Some application frameworks, such as Ruby on Rails, use a mechanism called "inflection" to allow the spelling of identifiers to change depending on usage. For simplicity,Zend_Db_Table_Row does not provide any inflection mechanism. The table identity and the rule key named in the method call must match the spelling of the class and rule key exactly.


在上面的模式中,<TableClass>和<Rule>是从属表对应的类名字符串和 从属表的父表引用 rule key 

注意:在一些框架中,例如 Ruby on Rails,使用“inflection”机制,标识符的拼写视情况而定。


 为简单起见,Zend_Db_Table_Row对象不提供任何inflection 机制。表的标识和rule key的名称在方法调用的名字命名的规则必须拼写完全匹配。


Example #4 Fetching Dependent Rowsets using the Magic Method使用魔术方法获取依赖行结果集


This example shows finding dependent Rowsets equivalent to those in the previous examples. In this case, the application uses the magic method invocation instead of specifying the table and rule as strings.

举例中显示的 依赖行结果集相当于前一个例子的结果集。 在这种情况下,应用程序会采用 魔术方法调用代替指定 table 名和rule key

  1. $accountsTable = new Accounts();
  2. $accountsRowset = $accountsTable->find(1234);
  3. $user1234 = $accountsRowset->current();
  4.  
  5. // Use the default reference rule
  6. $bugsReportedBy = $user1234->findBugs();
  7.  
  8. // Specify the reference rule
  9. $bugsAssignedTo = $user1234->findBugsByEngineer();


Fetching a Parent Row

If you have a Row object as the result of a query on a dependent table, you can fetch the row in the parent to which the dependent row refers. Use the method:

如果有一个从属表的查询结果作为 Row对象,你可以从父表中对应的行获取相应的行结果集。使用如下方法:

  1. $row->findParentRow($table,[$rule]);

There always should be exactly one row in the parent table referenced by a dependent row, therefore this method returns a Row object, not a Rowset object.

The first argument $table can be a string that specifies the parent table by its class name. You can also specify the parent table by using an object of that table class.


这里具体的行是 在父表中被引用的依赖行, 因此,该方法返回一个 Row 对象, 而不是一个 Rowset 对象.


第一个参数 $table是父表对应的类的类名字符串。也可以使用表对应类的对象作为父表。


Example #5 Fetching the Parent Row

This example shows getting a Row object from the table Bugs (for example one of those bugs with status 'NEW'), and finding the row in the Accounts table for the user who reported the bug.

举例中说明了从Bugs 表获取(所有状态为 'NEW' 的bugs)行对象。然后从Accounts 表中获取报告bug的用户

  1. $bugsTable = new Bugs();
  2. $bugsRowset = $bugsTable->fetchAll(array('bug_status = ?' => 'NEW'));
  3. $bug1 = $bugsRowset->current();
  4.  
  5. $reporter = $bug1->findParentRow('Accounts');


The second argument $rule is optional. It is a string that names the rule key in the$_referenceMap array of the dependent table class. If you don't specify a rule, the first rule in the array that references the parent table is used. If you need to use a rule other than the first, you need to specify the key.

In the example above, the rule key is not specified, so the rule used by default is the first one that matches the parent table. This is the rule'Reporter'.


第二个参数 $rule 是可选的。 rule key是一个名称字符串,在$_referenceMap是从属表的类的类名数组。如果你不指定rule。默认会采用父表的第一个相匹配的rule 。如果你不想使用父表中第一个相匹配的key。你需要自己指定。在上面的例子中,  没有指定rule key, 默认情况下使用的rule 是父表中第一个相匹配的。这里的rule是'Reporter“。


Example #6 Fetching a Parent Row By a Specific Rule

This example shows getting a Row object from the table Bugs, and finding the account for the engineer assigned to fix that bug. The rule key string that corresponds to this reference relationship in this example is'Engineer'.

从Bugs表中获取行对象。然后从account 表中获取已修复bug的工程师。 rule key 对应的引用关系是'Engineer'

  1. $bugsTable = new Bugs();
  2. $bugsRowset = $bugsTable->fetchAll(array('bug_status = ?','NEW'));
  3. $bug1 = $bugsRowset->current();
  4.  
  5. $engineer = $bug1->findParentRow('Accounts','Engineer');

Alternatively, you can query rows from a parent table using a "magic method".Zend_Db_Table_Row_Abstract invokes the method:

另外,可以采用魔术方法从父表中查询行。Zend_Db_Table_Row_Abstract 调用如下函数:

  findParentRow('<TableClass>', '<Rule>') 

if you invoke a method on the Row object matching either of the following patterns:

你还可以采用行对象提供的如下方法:

  • $row->findParent<TableClass>([Zend_Db_Table_Select $select])

  • $row->findParent<TableClass>By<Rule>([Zend_Db_Table_Select $select])

In the patterns above, <TableClass> and <Rule> are strings that correspond to the class name of the parent table, and the dependent table's rule key that references the parent table.

Note: The table identity and the rule key named in the method call must match the spelling of the class and rule key exactly.

在上面的模式中,<TableClass>和<Rule>是对应的父表类名,和父表的的rule key。
 注:表标识, rule key 名称必须和调用的方法的类名和rule key匹配


Example #7 Fetching the Parent Row using the Magic Method

This example shows finding parent Rows equivalent to those in the previous examples. In this case, the application uses the magic method invocation instead of specifying the table and rule as strings.

例子中返回的的父类的行结果 。在这种情况下, 应用程序使用魔术方法调用来代替指定表名和rule 

  1. $bugsTable = new Bugs();
  2. $bugsRowset = $bugsTable->fetchAll(array('bug_status = ?','NEW'));
  3. $bug1 = $bugsRowset->current();
  4.  
  5. // Use the default reference rule
  6. $reporter = $bug1->findParentAccounts();
  7.  
  8. // Specify the reference rule
  9. $engineer = $bug1->findParentAccountsByEngineer();


Fetching a Rowset via a Many-to-many Relationship 通过多对多的关系获取行结果集


If you have a Row object as the result of a query on one table in a many-to-many relationship (for purposes of the example, call this the "origin" table), you can fetch corresponding rows in the other table (call this the "destination" table) via an intersection table. Use the method:

如果有一个行对象, 在一个多对多的关系的表中可以通过一个行对象查询   (这里指的是 "origin" 表),你可以通过一个交集表,从其它表(这里指的是 "destination" 表)  获取相应的行结果。使用下面的方法:

  1. $row->findManyToManyRowset($table,
  2.                            $intersectionTable,
  3.                            [$rule1,
  4.                                [$rule2,
  5.                                    [Zend_Db_Table_Select$select]
  6.                                ]
  7.                            ]);


This method returns a Zend_Db_Table_Rowset_Abstract containing rows from the table$table, satisfying the many-to-many relationship. The current Row object$row from the origin table is used to find rows in the intersection table, and that is joined to the destination table.


该方法返回一个 Zend_Db_Table_Rowset_Abstract, 既包含了从$table表中获取的满足多对多关系的行结果 。当前行对象 $row  是从原表 和对应关联的目标表中查找行结果集。


The first argument $table can be a string that specifies the destination table in the many-to-many relationship by its class name. You can also specify the destination table by using an object of that table class.


第一个参数 $table 可以是在多对多关系中指定的目标表对应的类名字符串。你也可以通过使用该表的类的对象指定目标表。


The second argument $intersectionTable can be a string that specifies the intersection table between the two tables in the many-to-many relationship by its class name. You can also specify the intersection table by using an object of that table class.


第二个参数$intersectionTable 指的是在多对多关系中两个之间的交集表对应的类名字符串。你也可以使用交叉表的对象。



Example #8 Fetching a Rowset with the Many-to-many Method 使用多对多关系提供的方法获取行结果集合


This example shows getting a Row object from the origin table Bugs, and finding rows from the destination table Products, representing products related to that bug.


  1. $bugsTable = new Bugs();
  2. $bugsRowset = $bugsTable->find(1234);
  3. $bug1234 = $bugsRowset->current();
  4.  
  5. $productsRowset = $bug1234->findManyToManyRowset('Products',
  6.                                                  'BugsProducts');


该例子说明了从原始表  Bugs 表获取行结果, 和从目标表Products中, 查找指定产品的相关bug的表中获取行集合


The third and fourth arguments $rule1 and$rule2 are optional. These are strings that name the rule keys in the$_referenceMap array of the intersection table.

第三和第四个参数$rule1和$rule2是可选的。   在交集表$_referenceMap数组中的rule key 作为名称


The $rule1 key names the rule for the relationship from the intersection table to the origin table. In this example, this is the relationship from BugsProducts  to Bugs.

$rule1 键名是对应的是从交集表到原始表对应的关系规则。 例如, 从 BugsProducts  到 Bugs 表中的关系.


The $rule2 key names the rule for the relationship from the intersection table to the destination table. In this example, this is the relationship from Bugs  to Products.

 $rule2  键名  the rule for the relationship 从 交集表到目标表的关系规则. 例如, 从 Bugs  到 Products 表的关系.


Similarly to the methods for finding parent and dependent rows, if you don't specify a rule, the method uses the first rule in the$_referenceMap array that matches the tables in the relationship. If you need to use a rule other than the first, you need to specify the key.

同样类似的方法查找父类和相关的行结果。如果你不特别指定规则。该方法会使用在匹配的表的$_referenceMap 数组中的关系的第一个规则。如果您想要使用其它的规则,你需要指定具体的key。


In the example code above, the rule key is not specified, so the rules used by default are the first ones that match. In this case,$rule1 is'Reporter' and$rule2 is 'Product'.

在上面的示例代码中,如果不指定规则。默认会采用第一个匹配的规则。在这种情况下,$rule1是Reporter, $rule2是Product


Example #9 Fetching a Rowset with the Many-to-many Method By a Specific Rule  通过指定的规则获取行结果集


This example shows geting a Row object from the origin table Bugs, and finding rows from the destination table Products, representing products related to that bug.

该例子是 从原始表 Bugs中获取行对象 ,和从目标表Products中查找指定产品的相关bug的结果集合 

  1. $bugsTable = new Bugs();
  2. $bugsRowset = $bugsTable->find(1234);
  3. $bug1234 = $bugsRowset->current();
  4.  
  5. $productsRowset = $bug1234->findManyToManyRowset('Products',
  6.                                                  'BugsProducts',
  7.                                                  'Bug');



Alternatively, you can query rows from the destination table in a many-to-many relationship using a "magic method."Zend_Db_Table_Row_Abstract invokes the method:findManyToManyRowset('<TableClass>', '<IntersectionTableClass>', '<Rule1>', '<Rule2>')  if you invoke a method matching any of the following patterns:

另外,  你还可以使用魔术方法,从多对多关系中从目标表查询行结果。即可以调用Zend_Db_Table_Row_Abstract提供的方法indManyToManyRowset('<TableClass>', '<IntersectionTableClass>', '<Rule1>', '<Rule2>')。 
如果要使用此方法,可以按照如下规则使用:


  • $row->find<TableClass>Via<IntersectionTableClass> ([Zend_Db_Table_Select $select])

  • $row->find<TableClass>Via<IntersectionTableClass>By<Rule1> ([Zend_Db_Table_Select $select])

  • $row->find<TableClass>Via<IntersectionTableClass>By<Rule1>And<Rule2> ([Zend_Db_Table_Select $select])

In the patterns above, <TableClass> and <IntersectionTableClass> are strings that correspond to the class names of the destination table and the intersection table, respectively.<Rule1> and <Rule2> are strings that correspond to the rule keys in the intersection table that reference the origin table and the destination table, respectively.

在上面的规则中, <TableClass> 和 <IntersectionTableClass> 分别是目标表的相应的类名和交集表的类名。 <Rule1> and <Rule2>  相应的规则分别是在交集表 的原始表的引用 和 目标表的引用

Note: The table identities and the rule keys named in the method call must match the spelling of the class and rule key exactly.

   注意:在方法中使用的表的标识和  rule keys 的名称必须在拼写上严格与类名和rule key一致


Example #10  Fetching Rowsets using the Magic Many-to-many Method 采用魔术方法获取结果集


This example shows finding rows in the destination table of a many-to-many relationship representing products related to a given bug.

该例中从多对多的关系的目标表中查找结果集。即bug对应的的相关产品。

  1. $bugsTable = new Bugs();
  2. $bugsRowset = $bugsTable->find(1234);
  3. $bug1234 = $bugsRowset->current();
  4.  
  5. // Use the default reference rule
  6. $products = $bug1234->findProductsViaBugsProducts();
  7.  
  8. // Specify the reference rule
  9. $products = $bug1234->findProductsViaBugsProductsByBug();


Cascading Write Operations 级联写操作

Note: Declare DRI in the database: 在数据库中声明DRI:
Declaring cascading operations in Zend_Db_Table is intendedonly for RDBMS brands that do not support declarative referential integrity (DRI).
For example, if you use MySQL's or MariaDB's MyISAM storage engine, or SQLite, these solutions do not supportDRI. You may find it helpful to declare the cascading operations withZend_Db_Table.
If your RDBMS implements DRI and the ON DELETE and ON UPDATE clauses, you should declare these clauses in your database schema, instead of using the cascading feature inZend_Db_Table. Declaring cascading DRI rules in the RDBMS is better for database performance, consistency, and integrity.
Most importantly, do not declare cascading operations both in the RDBMS and in your Zend_Db_Table class.


在 Zend_Db_Table中声明的级联操作仅仅用于那些不支持声明引用完整性 (DRI)的RDBMS。

例如 , MySQL , MariaDB 的 MyISAM  存储引擎, 或者 SQLite, 这些不支持DRI。你可以 通过Zend_Db_Table 实现声明级联操作。

如要要实现  RDBMS 的  DRI 接口 和  ON DELETE 和 ON UPDATE 子句, 你应该在数据库结构设计中声明这些子句,而不是使用Zend_Db_Table提供的级联特性。

在RDBMS 中声明 级联 的DRI 规则 会有更高效 ,一致性,完整性, 

最重要的是不要同时使用Zend_Db_Table 和 RDBMS  提供的 声明级联操作


You can declare cascading operations to execute against a dependent table when you apply an UPDATE or a DELETE to a row in a parent table.


 当在父表中进行UPDATE 或者DELETE 时,可以对从属表声明级联操作。


Example #11 Example of a Cascading Delete  级联删除


This example shows deleting a row in the Products table, which is configured to automatically delete dependent rows in the Bugs table.

例如 在 Products 表中删除指定一行时会自动删除在B

------分隔线----------------------------
------分隔线----------------------------

最新技术推荐