Re: Defining Custom Association Queries - merging results fails

After considerable digging, I'm basically at the final root of this
problem.

finderQuery is correctly getting the records, but they are failing to
actually be merged with the User model. This blog post (http://
www.visuallizard.com/wil/blog/210/) answered many of my questions, and
some of the info there wouldn't be bad to add to the cakePHP
documentation.

After digging into the libs/model/datasources/dbo_source.php, I can
see exactly where my relation is failing. In function
__mergeAssociation, it fails right at the final conditional before it
should merge each $merge[$row] into $data (line 994). My $merge array
looks like this:

array(
[0] => Array
(
[0] => Array
(
[id] => 001
[name] => Person_1
)
)
[1] => Array
(
[0] => Array
(
[id] => 002
[name] => Person_1
)
)
[2] => Array
(
[0] => Array
(
[id] => 003
[name] => Person_1
)
)

It seems cake wants each row as an associative array, and mine are all
numerical, for whatever reason. ($merge[n]['Salespeople'] instead of
$merge[n][0]). I'm sure there is just some setting somewhere in my
model probably that I'm missing, that would give each record a key
instead of just 0. Does anyone know what I'm doing wrong? Here's the
updated code I have for the $hasMany attribute that is causing all of
this to run:

var $hasMany = array('Salesperson' => array(
'className'=>'Salesperson',
'fields'=>array
('Salesperson.id','Salesperson.name','Salesperson.email','Salesperson.phone','Salesperson.job_title'),
'foreignKey' => false,
'finderQuery' => 'SELECT [Salesperson].*
FROM [nav_salespeople] AS [Salesperson]
INNER JOIN [users] AS [User] ON
RTRIM(REPLACE(LEFT([Salesperson].[email], CHARINDEX(\'@
\', [Salesperson].[email])), \'@\', \' \')) = [User].[username]
WHERE [User].[id] IN ({$__cakeID__$})'));

Shouldn't the fact that I have className set, put a key in that merge
array? Or am I missing something?

On Mar 24, 1:29 pm, George <geo...@hakumei.net> wrote:
> I have two tables I want to link together, but the association is a
> bit more complicated than it normally would be. One of the tables
> (users) is built specifically for cake, another is a table from a read-
> only database (salespeople). User.username = Salespeople.email (or xxx
> in x...@site.com).
>
> Users can have multiple Salespeople
> Salespeople will have one User
>
> So I have Users hasMany Salesperson
> Salespeople belongTo User
>
> In the hasMany association, you can assign the finderQuery condition
> to have a custom association. So I set that to get the all the
> Salespeople records with an email (minus @site.com) that matches
> User.username.
>
> The belongsTo association doesn't seem to provide the finderQuery
> condition, according to the documentation. I was looking around in the
> api to see if it really did but was just left out of the documentation
> but I couldn't find it. Furthermore, I'm getting errors with it in, so
> I assume it isn't allowed in a belongsTo association.
>
> So my question is has anyone done something similar or can help point
> me in the right direction? There really weren't any examples of using
> finderQuery in the documentation either, so I sort of guessed on the
> format the query itself should take.
>
> For completeness, my finderQuery in the User model is (using MS SQL
> Server):
> SELECT s.*
> FROM users u
> INNER JOIN salespeople s ON rtrim(replace(left(s.email, charindex('@',
> s.email)), '@', ' ')) = u.username
> where u.id = $this->id
>
> That would be the correct format for finderQuery to work, correct?
>
> Thanks for any help anyone can provide.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
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?hl=en
-~----------~----~----~----~------~----~------~--~---