Doctrine form not working when table alias used

[imonteiro]

Original author: [email protected] (March 21, 2010 19:43:23)

Hi,

if source is defined as
$ab = Doctrine_Query::create()->from("Model_Addressbook");

form is displayed, but when source is defined with alias as

$ab = Doctrine_Query::create()->from("Model_Addressbook u");

form display fails with stack trace
#0 C:...\Doctrine\Doctrine\Table.php(249): Doctrine_Table->initDefinition()
#1 C:...\library\iVa\Doctrine\Table.php(16):

Doctrine_Table->__construct('u', Object(Doctrine_Connection_Mysql), true)
#2 C:...\Doctrine\Doctrine\Connection.php(1126):

iVa_Doctrine_Table->__construct('u', Object(Doctrine_Connection_Mysql), true)
#3 C:...\Doctrine\Doctrine\Query.php(1924): Doctrine_Connection->getTable('u')
#4 C:...\Doctrine\Doctrine\Query.php(1722): Doctrine_Query->loadRoot('u', 'u')
#5 C:...\Doctrine\Doctrine\Query\Where.php(76): Doctrine_Query->load('u',

false)
#6 C:...\Doctrine\Doctrine\Query\Condition.php(92):

Doctrine_Query_Where->load('u.id = ?')
#7 C:...\Doctrine\Doctrine\Query\Abstract.php(2077):

Doctrine_Query_Condition->parse('u.id = ?')
#8 C:...\Doctrine\Doctrine\Query.php(1156):

Doctrine_Query_Abstract->_processDqlQueryPart('where', Array)
#9 C:...\Doctrine\Doctrine\Query.php(1122):

Doctrine_Query->buildSqlQuery(true)
#10 C:...\Doctrine\Doctrine\Query\Abstract.php(958):

Doctrine_Query->getSqlQuery(Array)
#11 C:...\Doctrine\Doctrine\Query\Abstract.php(1026):

Doctrine_Query_Abstract->_execute(Array)
#12 C:...\Doctrine\Doctrine\Query.php(267):

Doctrine_Query_Abstract->execute(Array, 3)
#13 C:...\Bvb\Grid\Source\Doctrine.php(150):

Doctrine_Query->fetchArray(Array, 5)
#14 C:...\Bvb\Grid\Deploy\Table.php(330):

Bvb_Grid_Source_Doctrine->getRecord('addressbook', Array)
#15 C:...\Bvb\Grid\Deploy\Table.php(1230):

Bvb_Grid_Deploy_Table->_processForm()
#16

C:...\application\modules\default\controllers\IndexController.php(123):
Bvb_Grid_Deploy_Table->deploy()

As I figured out it is the following (at #14 above):

1. primary key is defined as "u.id"
2. table is defined as "addressbook" // no 'u'
3. getTable() receives
   a. table -> addressbook
   b. condition -> u.id = 11

and here it fails.

Please insert the appropriate values;
Zend Framework version: 1.10.1
ZFDatgrid Version (Bvb_Grid::getVersion()): 0.6
Operating system:
PHP Version:
Database Server and version: MySQL 5.1.39
Source Adatapter: Doctrine

Please provide any additional information below.

Original issue: http://code.google.com/p/zfdatagrid/issues/detail?id=225

[imonteiro]

From [email protected] on March 22, 2010 12:48:22
Hi,

I tried this simple query

$ab = Doctrine_Core::getTable('Model_Addressbook')
->createQuery('ab')
->innerJoin('ab.Users usr')
->innerJoin('ab.Countries cntr');

(Users and Countries are referenced as FK - userID and countryID in Addressbook)

and the result is:

1. Grid displays, but with "mixed" results.

All three tables have a field 'name', but displayed is only 'name' from the last one
joined table, in this case 'Countries'.

If I change the order of innerJoins
->innerJoin('ab.Countries cntr');
->innerJoin('ab.Users usr')
Than the name from 'Users' is displayed.

1. I tried this
   $x1 = $ab->fetchOne()->toArray();
   and received an array with all the fields, and Users and Countries as subarrays in it.

Since there is no "describe table" in non executed queries and thus quite hard to get
all the neccesary info, do you think it would be performance acceptable to execute
some similar query (restricted to one row by, for example, LIMIT), than the query is
fully parsed and all info about it is present, so building the data for the grid
should/might be easier and more straightforward.

1. It is not possible to add form to this grid. Exception
   "You don't have your primary key in your query. So it's not possible to perform CRUD
   operations. Change your select object to include your Primary Key: ab.id"

Of course, PK is present and it is standard 'id'. And it is included in results.

One of the solution might probably be to specify ALL the fields in the select part,
as the message says, but, as it is not possible to use '*' in select part, that might
not be acceptable. Some tables have over 50 fields, and than combine them with
several FK tables with each a few dozen fields. And for every select...

There must be some much simpler solution.

[imonteiro]

From [email protected] on April 05, 2010 15:44:14
Confirmed this as an issue and will look into fixing it. Thank you.

[imonteiro]

From [email protected] on December 01, 2010 22:27:41
Hi solomonjames,

is there an update for this issue, because this is dring me nuts,
not be able to use Doctrine completly.
I tried an workaround, but I didn´t get it working. Any ideas?

Thanks for the support

tronga

[imonteiro]

From [email protected] on October 07, 2011 12:12:31
Hi All,

Even I am having same issue is there any updates for that issue.

Thanks for the support.

Ashish

[imonteiro]

From [email protected] on October 07, 2011 13:46:13
Hi,

I kinda solved this problem by writing my own Doctrine.php, based on solomonjames version. Step by step. :-)

It worked with zfDatagrid version that was actual at that time.

I'm not working in PHP any more, but maybe someone can use this. Feel free to use it as you wish. As I remember, it solved a lot of my issues.

Best regards and happy PHP-ing,

vlatko basic

[imonteiro]

From [email protected] on February 23, 2012 09:50:02
hi,
I use Doctrine as data source for my grid , when i define a table alias in my doctrine query , i can't do the crud operation ( only delete and update, insert work fine). i have a error message as this
---------------------- error message --------------
Message: Couldn't find class ct
Stack trace:

#0 C:\Program Files (x86)\Zend\Apache2\htdocs\Principasol\library\Doctrine\Table.php(249): Doctrine_Table->initDefinition()
#1 C:\Program Files (x86)\Zend\Apache2\htdocs\Principasol\library\Doctrine\Connection.php(1126): Doctrine_Table->__construct('ct', Object(Doctrine_Connection_Mysql), true)
#2 C:\Program Files (x86)\Zend\Apache2\htdocs\Principasol\library\Doctrine\Query.php(1924): Doctrine_Connection->getTable('ct')
#3 C:\Program Files (x86)\Zend\Apache2\htdocs\Principasol\library\Doctrine\Query.php(1722): Doctrine_Query->loadRoot('ct', 'ct')

#4 C:\Program Files (x86)\Zend\Apache2\htdocs\Principasol\library\Doctrine\Query\Where.php(76): Doctrine_Query->load('ct', false)

I use the latest version of zfdatagrid (0.8) , zendF(1.11)
my select query is like :
$select=Doctrine_Query::create()->select('ct.ctc_prenom,ct.ctc_nom,ct.ctc_service,ct.ctc_fonction,ct.ctc_numerotelephone,ct.ctc_email,to.ogn_nom ')->from('TContact ct')->leftJoin('ct.TOrganisation to ');

to.ogn_nom is foreigen key of (ogn_id =>TOrganisation) in TContact

my Doctrine.php code is the same in svn

Thanks for the support.

[imonteiro]

From [email protected] on February 24, 2012 20:39:24
Thanks for the support.

[imonteiro]

From [email protected] on March 05, 2012 10:18:58
My solution

public function getRecord($table, array $condition)
{
$tableModel = $this->_getModelFromTable($table);

    $query = Doctrine_Query::create()->from($tableModel);
       $query->removeDqlQueryPart('limit')
          ->removeDqlQueryPart('offset')
          ->removeDqlQueryPart('where');
    foreach ($condition as $field => $value) {
        $field=explode('.', $field);
        $field=trim($field[1]);

        $query->addWhere($field . ' = ?', $value);

    }

    $results = $query->fetchArray(array(), Doctrine::HYDRATE_SCALAR);

    $newResults =$results;// $this->_cleanQueryResults($results);

    if (count($newResults) == 1) {
        return $newResults[0];
    }

    return $newResults;
}

[imonteiro]

From [email protected] on March 05, 2012 11:05:59
My solution