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 <[email protected]> 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 <[email protected]> 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 <[email protected]> wrote:
>>
>>> On Thu, May 24, 2012 at 3:14 PM, hill180 <[email protected]> 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
>>> [email protected] 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
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
