I'm using v1.3 of the Propel PHP framework for an application, and I can't find a way to select from a derived table using the Criteria
object. Part of the SQL I want is:
SELECT unioned_table.foo, quux.stuff, baz.more_stuff...
FROM
quux
INNER JOIN
(SELECT foo, bar FROM table1
UNION
SELECT foo, bar FROM table2
) AS unioned_table
ON quux.field = unioned_table.foo
INNER JOIN baz
ON baz.blah = unioned_table.bar
INNER JOIN ...
WHERE conditions...
The actual SQL is more complex than this, but that just includes further joins.
I've tried to use Criteria::addAlias()
, though unfortunately that tries to SQL-escape the table definition. I eventually gave up on trying to write the SQL in that manner, and created a view (called unisoned_table
in this example).
I next tried to add joins to this table:
$c->addSelectColumn('unioned_table.foo');
$c->addSelectColumn(QuuxPeer::STUFF);
$c->addSelectColumn(BazPeer::MORE_STUFF);
// ...
$c->addJoin(QuuxPeer::FIELD, 'unioned_table.foo', Criteria::INNER_JOIN);
$c->addJoin(BazPeer::BLAH, 'unioned_table.bar', Criteria::INNER_JOIN);
// ...
$c->add(QuuxPeer::STUFF, $someval);
// ...
Sadly this results in the view being joined twice -- once as an inner join with qux
, and once as a cross join with baz
. Bizarrely, if I remove the baz
SELECT column, then the cross join disappears.
Does anyone have any suggestions as to how I can do this? I can't use just bare custom SQL, as this Criteria
may need to be modified (columns replaced, extra conditions added, etc) or used in a doCount()
call.