Doctrine: merge 2 requests in 1

Question

I would like to merge these 2 requests in 1 but I have no clue on how to do this. Any idea ?

$productsCount = Doctrine::getTable('Product')
            ->createQuery('p')
            ->where('p.store_id = ?', $store_id)
            ->andWhere('p.collection = ?', $this->product->getCollection())
            ->andWhere('p.image_path IS NOT NULL')
            ->count();

$productsCollection = Doctrine::getTable('Product')
            ->createQuery('p')
            ->where('p.store_id = ?', $store_id)
            ->andWhere('p.collection = ?', $this->product->getCollection())
            ->andWhere('p.status_id = ?', Product::_ONLINE)
            ->andWhere('p.id<>?', $this->product_id)
            ->offset(rand(0, $productsCount - 1))
            ->execute();

Doctrine: 1.2
Symfony: 1.4
PHP: 5.3

What do you want at the end? These 2 queries returns different information, how do you want to merge them? — j0k, Nov 10 '12 at 12:09
The 2 queries use the same table and the first is used by the second so I thought it might be better to do all this stuff in one to optimize this. But perhaps I was wrong ? — MaximeBernard, Nov 10 '12 at 16:16

score 1 · Accepted Answer · edited Nov 10 '12 at 12:07

You can use subquery, because your query is not identical. Here DQL: Doctrine Query Language some example. And here is pseudocode, I do not know if it will work at once.

$q = Doctrine_Query::create()
            ->from('Product p')
            ->select('id, sum(id) as sumEntries') 
            ->addSelect('(SELECT id, name) // and else fields that you need
                        FROM Product a
                        WHERE (
                        a.store_id  = '.$store_id.' 
                        AND  
                        a.collection = '.$this->product->getCollection().'
                        AND
                        a.id<>= '.$this->product_id.' 
                        )
                        OFFSET '.rand(0, $productsCount - 1).') // I am not sure in this line
                        as resultSubquery')

            ->where('p.store_id = ?', $store_id)
            ->andWhere('p.collection = ?', $this->product->getCollection())
            ->andWhere('p.image_path IS NOT NULL')


  $result =  $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY); //This greatly speeds up query

You get an array in $result. Do var_dump() and check its contents. I'm not sure that this code will work at once, but I advise you to move in this direction.

p.s: I recommend you this interesting presentation about Doctrine query optimization: Doctrine 1.2 Optimization

Oh I see ! So you have to inject pure SQL inside Doctrine query ? I thought it might be better optimized to request products and count at the same time. — MaximeBernard, Nov 10 '12 at 16:15
Another way to do this I do not know.This method is described in the documentation, and it works, I think it's better than doing two requests :-) — denys281, Nov 10 '12 at 21:25
Aren't you opening yourself up to a SQL injection attack when creating a query like this? Queries should be parameterized. — oalbrecht, Aug 22 '17 at 16:31

Doctrine: merge 2 requests in 1

1 Answers1