DQL recipes
I had to start covering various Doctrine snippets for future reference. This post will be updated with more of them as they appear.
First of all a link to Francois comparison article is in order. It features a lot of simpler DQL examples.
The CRUD article also features a more complex example.
Note that $this->q refers to a Doctrine_Query object and $this->t to a Doctrine_Table object.
function getProducts(){
$prod = $this->q->select('c.*, p.*, COUNT(p.id) AS num_prod')
->from('Cart c')->leftJoin('c.Prod p')->where('c.id = 1')
->groupBy('p.id')->fetchArray();
}Here we retrieve all the products in a cart (with id 1 in this case). We also count them and store the count in a custom num_prod column.
function deleteProduct(){
$this->q->from('Cart_list')
->where('prod_id = ? AND cart_id = ?')
->fetchOne(array($_POST['id'], 1))->delete();
}This one will delete a product from the cart with id 1 and product id in the $_POST[‘id’] variable. We work with an intermediary table called Cart_list which is keeping track of which products are in which carts.
function getSearchQuery($dql, $arr){
$dql .= empty($dql) ? 'l.code = ?' : 'AND l.code = ?';
$arr[] = $_SESSION['lang'];
return $this->q->select('this.*, t.*')->from('Product this, this.Trans t, t.Lang l')
->where($dql, $arr)->groupBy('t.id');
}The shop is multilingual and in this case we are attaching the German translation to each result. Each product has its description translated to an arbitrary amount of languages. The translation (Trans) table contains the translation as well as a reference index (hasOne) to the product and the translation in question. That is why we can automatically get it with no more extra information than the language code (in this case de in $_SESSION[‘lang’]). In other words, the joins are performed implicitly.
In this case the $dql will already contain some arbitrary DQL used to generate the result. We simply add the language stuff on top of it through the l.code = $_SESSION[‘lang’] clause. For more info on the context the above function exists in see the pagination tutorial.
function getRandom(){
$stamp = strtotime("-5 year");
$date = date('Y-m-d', $stamp);
$res = $this->q->select('this.*, t.*, RANDOM() rand')
->from('Product this, this.Trans t, t.Lang l')
->where('l.code = ? AND created_at > ?', array($_SESSION['lang'], $date))
->groupBy('t.id')->orderby('rand')->limit(2)->fetchArray();
}This is similar to the prior example in that we do the translating here too. On top of that we get a random result of all the products that have been added during the prior 5 years and limit the result to a list of two products. Note the use of created_at, this is one of the fields that are automatically generated if you set $this->actAs(‘Timestampable’); in the model.
$trans = $this->getT('Translation')
->findByDql('prod_id = ? AND lang_id = ?', array($_GET['id'], $_GET['lang_id']))
->getFirst();This is an example of Doctrine::Table()->findByDql(). We get the first translation for the product and language in question, in this case there can only be one though, however we still get a collection of records, hence getFirst().
$products = $this->getQ()->orderBy('created_at DESC')
->limit(50)->execute()->toArray();This is an example of Doctrine::Table()->createQuery() which getQ() is a shortcut for here.
$letters = Common::loadCtrl('Newsletter')->fetchMe()
->where('me.id IN (SELECT id FROM Sendlist)')->execute();Simple example of sub select query, fetchMe is an alias for Doctrine::Table::createQuery(‘me’).