how to use union in doctrine query symfony 1.4?

Question

I want to have query like this in mysql:

select a.x, a.y, a.foo, sum(a.bar) as bar_sum from mytable a groupby a.x union select a.x, a.y, a.bar, sum(a.foo) as foo_sum from mytable a groupby a.y

this format is true? and how I can convert it to doctrine query in symfony?

thanks a lot

Answer 1

I believe that UNION is not supported using DQL (doctrine query language). But it looks like someone was able to get around the issue, using a nasty heredoc.

Or, you could just add a custom method in an object peer class and write the query in native sql.

-- Edit --

Use the following as an example (untested):

// Assuming you have the default doctrine setup, where non-object classes are
//  appended with Table (your object being Foo)
class FooTable
{
    public static function getFooBarUnion()
    {
        $sql = "select a.x, a.y, a.foo, sum(a.bar) as bar_sum from mytable a groupby a.x union select a.x, a.y, a.bar, sum(a.foo) as foo_sum from mytable a groupby a.y";

        $results = Doctrine_Query::create()->query($sql);

        return $results;
    }
}