Re: DB_DataObject : table alias in joined queries

Doing this : <?php $cat = & DB_DataObject::factory('ent_categorie'); $fcat = & DB_DataObject::factory('ent_categorie'); $cat->selectAdd(); $cat->selectAdd('ent_categorie.*'); $cat->joinAdd($fcat,'right','fcat'); $cat->selectAs($fcat,'ent_categorie__%s'); ?> results in that : SELECT ent_categorie.* , ent_categorie.id as ent_categorie__id , ent_categorie.titre as ent_categorie__titre , ent_categorie.strip as ent_categorie__strip , ent_categorie.active as ent_categorie__active , ent_categorie.categorie_id as ent_categorie__categorie_id , ent_categorie.gauche as ent_categorie__gauche , ent_categorie.droite as ent_categorie__droite , ent_categorie.path as ent_categorie__path FROM ent_categorie LEFT JOIN entada.ent_categorie AS fcat ON fcat.id=ent_categorie.categorie_id This is still not good. The alias is used for the WHERE clause, not for the select fields. Le 22 août 06 à 20:01, arnaud sellenet a écrit :

I guess though the question is, why do you need to alias the base DataObject, when JoinAdd already provides aliasing for joined objects?

The reason was explained in a mail I've sent to the general list. Here is its content : Hi. I have a problem making a joined query using DB_DataObject to the table itself (tree structure). This is the php code to build the query : <?php $cat = & DB_DataObject::factory('ent_category'); $fcat = & DB_DataObject::factory('ent_category'); $cat->joinAdd($fcat); $cat->selectAs($fcat,'ent_category__%s'); ?> This is the resulting SQL : SELECT count(ent_category.id) as DATAOBJECT_NUM FROM ent_category INNER JOIN project.ent_category ON ent_category.category_id=project.ent_category.id Which makes a SQL error. And this is the query that would work : SELECT count(e1.id) as DATAOBJECT_NUM FROM ent_category e1 INNER JOIN project.ent_category ON e1.category_id=project.ent_category.id In my mind I would have written something like this : <?php $cat = & DB_DataObject::factory('ent_category'); $cat->setAlias('e1'); $fcat = & DB_DataObject::factory('ent_category'); $cat->joinAdd($fcat); $cat->selectAs($fcat,'ent_category__%s'); ?>

Regards Alan arnaud sellenet wrote:

This is a diff between the current release and a version with added table alias for use in joined queries. Anyone interested ? 255a256,261

    /**
     * The table alias
     **/
     var $_alias = '';

420c426

<             ' FROM ' . ($quoteIdentifiers ? $DB-

quoteIdentifier ($this->__table) : $this->__table) . " \n" .

---

            ' FROM ' . ($quoteIdentifiers ? $DB-quoteIdentifier ($this->__table) : $this->__table) . ' '. $this-_alias . " \n" .

1517c1523,1525

<         $table   = ($quoteIdentifiers ? $DB->quoteIdentifier($this- >__table) : $this->__table);

---

        $tablen = $this->_alias ? $this->_alias:$this->__table;
        $table   = ($quoteIdentifiers ? $DB->quoteIdentifier 
($tablen) : $tablen);
        $tablefrom = ($quoteIdentifiers ? ($DB->quoteIdentifier 
($this->__table).' '.$DB->quoteIdentifier($this->_alias)) : ($this- >__table.' '.$this->_alias));

1529c1537

<                 FROM $table {$t->_join} {$t->_query['condition']}");

---

                FROM $tablefrom  {$t->_join} {$t->_query 
['condition']}");

2427c2435 < ---

            $tablen = $this->_alias ? $this->_alias : $this-__table;

2429,2430c2437,2438

<                 ? ( $DB->quoteIdentifier($this->__table) . '.' .  $DB->quoteIdentifier($k) )
<                 : "{$this->__table}.{$k}";

---

                ? ( $DB->quoteIdentifier($tablen) . '.' . $DB- quoteIdentifier($k) )
                : "{$tablen}.{$k}";

3156c3164

<         $table = $this->__table;

---

        $table = $this->_alias?$this->_alias:$this->__table;

3282c3290,3298 < ---

    /**
     * creates an table alias in the query
     * @param   string  the alias to use
     * @return  null
     **/
     function setAlias($alias) {
         $this->_alias = $alias;
     }

--PEAR Development Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --PEAR Development Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php