CODEWITHSUNDEEP
phpmysqlsymfony-1.4doctrine-1.2
Velemir
Velemir

Reputation: 143

Making query from subquery

I am trying to pass following sql query to Doctrine:

SELECT id, name, count(*) FROM (SELECT r.id, r.name, f.timestamp FROM `food_log` as f inner join recipe as r on f.recipe_id = r.id WHERE f.user_id = 6 and timestamp > "2016-09-01" and recipe_id is not null group by f.timestamp, r.id) a GROUP BY a.id ORDER BY count(*) DESC

It should return me recipes with amount of how many times particular user was using single recipe from selected timestamp.

Now I am trying to do this with Doctrine 1.2 and Symfony 1.4, however I do not know how to make query from subquery, I was trying to do something like this

        $subQuery = Doctrine_Query::create()
        ->select('r.id, r.name, f.timestamp')
        ->from('FoodLog f')
        ->innerJoin('f.Recipe r')
        ->where('f.user_id = ?', $userId)
        ->andWhere('timestamp > ?', "2016-09-01")
        ->andWhere('f.recipe_id is not null')
        ->andWhere('r.is_premium = ?', $premium)
        ->groupBy('f.timestamp, r.id')
        ->getSqlQuery();

    $query = Doctrine_Query::create()
    ->select('id, name, count(*)')
    ->from($subQuery)
    ->groupBy('id')
    ->orderBy('count(*) DESC');

    return $query->fetchArray();

Anybody know where I am wrong with it ? Thanks a lot for any response !

Upvotes: 0

Views: 111

Answers (1)

moote
moote

Reputation: 126

Basically, you can't do nested queries in this version of Doctrine. I'd recommend using a raw SQL query via the doctrine connector:

$sql = 'SELECT id, name, count(*) FROM (SELECT r.id, r.name, f.timestamp FROM `food_log` as f inner join recipe as r on f.recipe_id = r.id WHERE f.user_id = 6 and timestamp > "2016-09-01" and recipe_id is not null group by f.timestamp, r.id) a GROUP BY a.id ORDER BY count(*) 
DESC';
$conn = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $conn->execute($sql);

foreach($result as $data){
    // do something
}

As you're not hydrating objects, you should find this works well.

Upvotes: 2

Related Questions