Propel: selecting columns from aliased join tables

Question

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:

 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:

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

UPDATE

I also checked propel 1.7.1, no difference.

Answer 1

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

• 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
• 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.

For Propel >= 1.6.8 this will work:

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