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