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

问题描述

我有以下两个表:

  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 }

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

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

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

现在是问题所在. Propel不断将a和b别名更改为table_b，从而生成如下错误的SQL:

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

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)

代替

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)

我该如何处理?我使用Propel 1.6.9

How can I deal with that? I use Propel 1.6.9

更新

我还检查了推进器1.7.1，没什么区别.

I also checked propel 1.7.1, no difference.

推荐答案

您可以将其更改为此吗?

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();

注意:我不是Propel用户.

Caveat: I'm not a Propel user.

与上面的查询一样，用两个addAsColumn()语句替换select().这有点骇人听闻，但我认为它可以达到您想要的结果.

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.

我花了一段时间阅读Propel源代码，并得出结论，在尝试多次连接同一张表的过程中，Propel 1并不是为使用不同别名而构建的.在ModelCriteria.php中，使用$column->getFullyQualifiedName可以确保在选择联接表时使用全名(table.column)，而不管别名如何. (请参见 https://github.com/propelorm /Propel/blob/7ddb0956b699343d33ce0c94043fa5970cc719c1/runtime/lib/query/ModelCriteria.php#L2082 .)我希望这确实是一个错误.

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.

如果使用addAsColumn()方法而不是select()，则Propel将使用您的文字SQL表达式，无论它是别名，列还是其他.就是说，我不确定这是它的预期用途.

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.

这篇关于推进:从别名联接表中选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！