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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---