推进:从别名连接表中选择列

I have the following two tables:

我有以下两张表:

  table_a:
    id_table_a: { type: integer, primaryKey: true, autoIncrement: true, required: true }
    name:       { type: varchar(255) }
    id_table_b: { type: integer, foreignTable: table_b, foreignReference: id_table_b }

  table_b: 
    id_table_b: { type: integer, primaryKey: true, autoIncrement: true, required: true }
    value_1:    { type: varchar(255) }
    value_2:    { type: integer }

and I would like to build SQL query using select method to skip hydration, also using aliases on joined tables:

我还想使用select方法构建SQL查询，以跳过水合，并在已连接表上使用别名:

 TableAQuery::create()
            ->useTableBQuery('a')
                // some filters methods
            ->endUse()
            ->useTableBQuery('b')
                // some filters methods
            ->endUse()
            ->select(array('a.value_1', 'b.value_2'))
            ->find();

Now here is the problem. Propel consantly keep changing a and b aliases to table_b generating incorrect SQL like this:

现在的问题是。不断地将a和b别名更改为table_b，生成这样的错误SQL:

SELECT table_b.value_1 AS "a.value_1", table_b.value_2 AS "b.value_2" FROM `table_a`
LEFT JOIN `table_b` `a` ON (table_a.id_table_b=a.id_table_b) 
LEFT JOIN `table_b` `b` ON (table_a.id_table_b=b.id_table_b)

instead of

而不是

SELECT a.value_1 AS value_1, b.value_2 AS value_2 FROM `table_a` 
LEFT JOIN `table_b` `a` ON (table_a.id_table_b=a.id_table_b) 
LEFT JOIN `table_b` `b` ON (table_a.id_table_b=b.id_table_b)

How can I deal with that? I use Propel 1.6.9

我怎么处理呢?我使用推动1.6.9

UPDATE

更新

I also checked propel 1.7.1, no difference.

我也检查了1。7.1，没有区别。

2 个解决方案

#1

Could you change it to this?

你能把它换成这个吗?

TableAQuery::create()
        ->useTableBQuery('a')
            // some filters methods
        ->endUse()
        ->useTableBQuery('b')
            // some filters methods
        ->endUse()
        //->select(array('a.value_1', 'b.value_2'))
        ->addAsColumn('a.value_1', 'a.value_1')
        ->addAsColumn('b.value_2', 'b.value_2')
        ->find();

Caveat: I'm not a Propel user. ~~I'm just wondering if the auto-generated useXXXQuery() is setting the table alias on the same relation both times, or something like that.~~

注意:我不是一个推送用户。我只是想知道自动生成的useXXXQuery()是否两次在相同的关系上设置表别名，或者类似的东西。

As in the query above, replace your select() with the two addAsColumn() statements. It's a bit of a hack, but I think it achieves your desired result.

如上面的查询所示，将select()替换为两个addAsColumn()语句。这是一个小技巧，但我认为它达到了你想要的结果。

I just spent a while reading the Propel source code, and I've concluded that Propel 1 is not built to use different aliases on the same table joined multiple times, as you attempt. In ModelCriteria.php, the use of $column->getFullyQualifiedName ensures that the full name (table.column) is used in the select for joined tables, regardless of alias. (See https://github.com/propelorm/Propel/blob/7ddb0956b699343d33ce0c94043fa5970cc719c1/runtime/lib/query/ModelCriteria.php#L2082.) I expect this is indeed a bug.

我刚花了一段时间阅读了Propel源代码，我得出的结论是，在您尝试的过程中，在同一个表上多次连接的不同别名并不是为构建Propel 1而构建的。在ModelCriteria。在php中，使用$column->getFullyQualifiedName确保在选择已连接表时使用全名(table.column)，而不考虑别名。(见https://github.com/propelorm/Propel/blob/7ddb0956b699343d33ce0c94043fa5970cc719c1/runtime/lib/query/ModelCriteria.php L2082)。我想这确实是个错误。

If the addAsColumn() method is used instead of select(), Propel will use your literal SQL expression, whether it's an alias and column or anything else. That said, I'm not sure that's its intended use.

如果使用的是addAsColumn()方法而不是select()，那么Propel将使用字面SQL表达式，无论它是别名、列还是其他内容。也就是说，我不确定这是它的预期用途。

#2

I do a little reserch after I read jchamberlain's comment about where caluse under his answer and I came to conclusion that

在我读了jchamberlain关于caluse在他的回答下的评论之后，我做了一些研究，我得出了结论

you should always use UpperCamelCase style when you type column names in propel functions, even though it sometimes works fine if you don't use this style
在boost函数中键入列名时，应该始终使用大写camelcase样式，即使如果不使用这种样式，有时也可以使用这种样式
solution depends on Propel version
解决方案取决于推进版本

Probably there is no solution for Propel <= 1.6.7 (or maybe raw SQL query is the only solution), because no matter how much I try I always ends up with Cannot fetch ColumnMap for undefined column: ID_TABLE_B Propel exception.

可能还没有boost <= 1.6.7的解决方案(或者可能原始SQL查询是唯一的解决方案)，因为无论我尝试了多少次，最终都无法获取未定义列ID_TABLE_B驱策的ColumnMap。

For Propel >= 1.6.8 this will work:

对于推进>= 1.6.8这将起作用:

If you need array with aliased columns as result

如果结果需要具有别名列的数组

TableAQuery::create()
  ->useTableBQuery('a')
      // some filters methods
  ->endUse()
  ->useTableBQuery('b')
      // some filters methods
  ->endUse()
  ->select(array('a.Value1', 'b.Value2')) // notice a.value_1 or b.value_2 won't work
  ->find();

If you need objects with virtual columns from aliased columns

如果需要使用来自别名列的虚拟列的对象

TableAQuery::create()
  ->useTableBQuery('a')
      // some filters methods
  ->endUse()
  ->useTableBQuery('b')
      // some filters methods
  ->endUse()
  ->withColumn('a.Value1') // notice a.value_1 won't work
  ->withColumn('b.Value2') // notice b.value_2 won't work
  ->find();

#1