Re: Model Associations

Solved:  But I don't know why.  This but this code puts the data where I want it, and without the index error:

 

 

'Contact' => array(
     'className'=>'CONTACT',
     'foreignKey' => false,
     'finderQuery' => 'SELECT Account.AccountID as Contact__ACCOUNT_ID, contact.contact_full_name as Contact__CONTACT_FULL_NAME FROM CONTACTS AS contact LEFT
     JOIN Accounts AS Account ON contact.account_id=Account.account WHERE
     Account.AccountID = {$__cakeID__$}'
   )

On Thu, May 24, 2012 at 9:38 AM, hill180 <hill180@gmail.com> wrote:

Ok.

Sorry for the last email.

 

it made no sense:

 

What I was trying to say is that I am getting

Notice (8): Undefined index: Contacts

 

I copied the SQL Dumb (from the finderquery) in MSSQL Manager and it works.

 

Just doing a debug($results) which is the results of the find query.

 

Also it gets me 3 undefined Index errors, which matches the records for this account.

Thank you.

 

On Thu, May 24, 2012 at 10:33 AM, hill180 <hill180@gmail.com> wrote:

Thank you so much.  I think I am 99% there.

 

Get the following error three times, (that is actually how many contacts are in the DB for the customer I was looking up. 

 

debug($results);

 

Notice (8): Undefined index: Contacts [CORE\Cake\Model\Datasource\DboSource.php, line 1412]

Code Context

                        }                      } else {                          $this->_mergeAssociation($row, $fetch, $association, $type, $selfJoin);

$data = array(  	'TblAccount' => array(  	),  	'Address' => array(  	),  	'Contacts' => array(  	)  )  $merge = array(  	(int) 0 => array(  	),  	(int) 1 => array(  	),  	(int) 2 => array(  	)  )  $association = 'Contacts'  $type = 'hasMany'  $selfJoin = false  $i = (int) 1  $row = array(  	(int) 0 => array(  	)  )  $insert = array()

DboSource::_mergeAssociation() - CORE\Cake\Model\Datasource\DboSource.php, line 1412  DboSource::queryAssociation() - CORE\Cake\Model\Datasource\DboSource.php, line 1274  DboSource::read() - CORE\Cake\Model\Datasource\DboSource.php, line 1087  Sqlserver::read() - CORE\Cake\Model\Datasource\Database\Sqlserver.php, line 600  Model::find() - CORE\Cake\Model\Model.php, line 2698  TblAccountsController::view() - APP\Controller\TblAccountsController.php, line 28  ReflectionMethod::invokeArgs() - [internal], line ??  Controller::invokeAction() - CORE\Cake\Controller\Controller.php, line 485  Dispatcher::_invoke() - CORE\Cake\Routing\Dispatcher.php, line 103  Dispatcher::dispatch() - CORE\Cake\Routing\Dispatcher.php, line 85  [main] - APP\webroot\index.php, line 92

 

 

 

 

 

 

Co

 

I copied the SQL Dumb into MS SQL query and it gave me the records I needed.

 

Thanks!

 

 

On Thu, May 24, 2012 at 9:38 AM, Mike Griffin <griffinm@gmail.com> wrote:

On Thu, May 24, 2012 at 3:14 PM, hill180 <hill180@gmail.com> wrote:
> Yes.
>
> As a disclaimer, I didn't write the original schema.
>
> Account_ID is for the systems account number (primary key)
> Account is for the logical account number (used by the company)
>
> The Account Address table use the Account_ID as the foreign key, but the
> Contact Table uses the Account Number as the foreign key.
>
> SQLDUMP:
>
> SELECT [CONTACT_FULL_NAME] AS [Contact__CONTACT_FULL_NAME],
> [Contact].[CONTACT_FIRST_NAME] AS [Contact__CONTACT_FIRST_NAME] FROM
> [CONTACTS] AS [Contact] WHERE [Contact].[Account_ID] = [Accounts].[Account]
>
>
> error:
>
> Error: SQLSTATE[42000]: [Microsoft][SQL Server Native Client
> 11.0][SQL Server]The multi-part identifier "Accounts.Account" could not be
> bound.
>

Right so.

You can do it this way instead. Remove the conditions array from the
model association and put this in:
'finderQuery' => 'SELECT Contact.name FROM contacts AS Contact LEFT
JOIN accounts AS Account ON Contact.account_id=Account.account WHERE
Account.AccountID = {$__cakeID__$}'

You can edit the SELECT query to return whatever values you want but
leave in the {$__cakeID__$} bit as it is the value of the primary key.

I hope this makes sense.

Mike.

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
 
 
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php