处理Propel对象的计算字段的最佳方法是什么?
What is the best way of working with calculated fields of Propel objects?
说我有一个对象"Customer",它有一个对应的表"customers",并且每一列都对应于我对象的一个属性.我想做的是:在视图A而不是视图B和C上使用该属性时,将计算出的属性已完成订单数"添加到我的对象中.
Say I have an object "Customer" that has a corresponding table "customers" and each column corresponds to an attribute of my object. What I would like to do is: add a calculated attribute "Number of completed orders" to my object when using it on View A but not on Views B and C.
计算出的属性是通过ID链接到我的客户"对象的订单"对象的COUNT().
The calculated attribute is a COUNT() of "Order" objects linked to my "Customer" object via ID.
我现在要做的是首先选择所有Customer对象,然后为所有对象迭代计算Orders,但是我认为在单个查询中进行操作将提高性能.但是我无法正确地水化"我的Propel对象,因为它不包含计算字段的定义.
What I can do now is to first select all Customer objects, then iteratively count Orders for all of them, but I'd think doing it in a single query would improve performance. But I cannot properly "hydrate" my Propel object since it does not contain the definition of the calculated field(s).
您将如何处理?
推荐答案有几种选择.首先,是在数据库中创建一个视图,该视图将为您完成计数,类似于我在此处的答案.我是针对当前的Symfony项目执行此操作的,在该项目中,给定表的只读属性实际上比表本身宽得多.这是我的建议,因为分组列(max(),count()等)始终是只读的.
There are several choices. First, is to create a view in your DB that will do the counts for you, similar to my answer here. I do this for a current Symfony project I work on where the read-only attributes for a given table are actually much, much wider than the table itself. This is my recommendation since grouping columns (max(), count(), etc) are read-only anyway.
其他选项实际上是将此功能构建到模型中.您绝对可以自己进行保湿,但是有点复杂.这是粗糙的步骤
The other options are to actually build this functionality into your model. You absolutely CAN do this hydration yourself, but it's a bit complicated. Here's the rough steps
但是,这并没有比您正在谈论的要好得多.您仍然需要 N + 1个查询来检索单个记录集.但是,您可以在第3步中发挥创意,使 N 是计算的列数,而不是返回的行数.
However, this isn't much better than what you're talking about already. You'll still need N + 1 queries to retrieve a single record set. However, you can get creative in step #3 so that N is the number of calculated columns, not the number of rows returned.
另一种选择是在 Table Peer类上创建自定义选择方法.
Another option is to create a custom selection method on your TablePeer class.
这是这种方法的一个例子
Here's an example of this approach
<?php class TablePeer extends BaseTablePeer { public static function selectWithCalculatedColumns() { // Do our custom selection, still using propel's column data constants $sql = " SELECT " . implode( ', ', self::getFieldNames( BasePeer::TYPE_COLNAME ) ) . " , count(" . JoinedTablePeer::ID . ") AS calc_col FROM " . self::TABLE_NAME . " LEFT JOIN " . JoinedTablePeer::TABLE_NAME . " ON " . JoinedTablePeer::ID . " = " . self::FKEY_COLUMN ; // Get the result set $conn = Propel::getConnection(); $stmt = $conn->prepareStatement( $sql ); $rs = $stmt->executeQuery( array(), ResultSet::FETCHMODE_NUM ); // Create an empty rowset $rowset = array(); // Iterate over the result set while ( $rs->next() ) { // Create each row individually $row = new Table(); $startcol = $row->hydrate( $rs ); // Use our custom setter to populate the new column $row->setCalcCol( $row->get( $startcol ) ); $rowset[] = $row; } return $rowset; } }对于您的问题,可能还有其他解决方案,但是这些解决方案是我所不了解的.祝你好运!
There may be other solutions to your problem, but they are beyond my knowledge. Best of luck!
更多推荐
Symfony应用程序
发布评论