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

Answer 2

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.

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.

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.

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.