Visual Lizard

Working on a CakePHP application, I found I needed to associate two models, but not in the regular way. In most cases, I could make a hasMany relationship by putting an appropriately-named foreignKey in the other datatable. So, if a Model named "Post" hasMany "Comment", then you just needed to add a "post_id" field to the "Comment" datatable and populate it correctly.

However, I came across what was technically a hasAndBelongsToMany relationship between two models. One was "Pulldown", and each row represented a select pulldown field. The other was "Option", and each row was an option in the select pulldown fields.

Pulldown datatable

Option datatable

Since each Pulldown would use multiple Options, and in most cases, each set of Options would be used in more than one Pulldown, the regular CakePHP way would be to create a "pulldowns_options" lookup datatable... but for my application, managing that lookup datatable was incredible cumbersome. There had to be another way of linking this data.

The finderQuery option seemed to be exactly what I was looking for. I could define the relationship as a hasMany, and then write my own SQL to get the exact Option rows I wanted for each Pulldown rows. I easily set up some extra fields...

Pulldown datatable

Option datatable

Perfect! Now I could group my Option rows into "sets", and just define which set I would like to use for each Pulldown. Now, when defining my hasMany relationship in my Model, I could set the finderQuery variable...

Pulldown model class

class Pulldown extends AppModel {

	var $hasMany = array(
		’Option’	=> array(
			’className’		=> ’Option’,
			’finderQuery’	=> ’SELECT Option.* FROM options AS Option WHERE Option.set="sex"’
		)
	);
	
}

Wait, another problem. This will work if I only ever want specifically my "sex" set of Option rows. What I needed was some sort of placeholder or variable that I could put into my finderQuery SQL, something like this...

Pulldown model class

class Pulldown extends AppModel {

	var $hasMany = array(
		’Option’	=> array(
			’className’		=> ’Option’,
			’finderQuery’	=> "SELECT Option.* FROM options AS Option WHERE Option.set=$PulldownUseSet"
		)
	);
	
}

So I did some googling, and found that there is a variable available for this purpose! It was called {$__cakeID__$} and could just be put into the SQL. The down side was that it represented the class model’s ID value per row. So in my case, it would be equal to Pulldown’s "id" value. That’s not helpful at all, I could just do a regular hasMany in that case!

Ah, but no panic! The finderQuery allows me to write any amount of SQL to get exactly what I want. There’s no reason I couldn’t write the SQL and use the {$__cakeID__$} placeholder to find the current Pulldown row and join it to my Option datatable. So, I changed the relationship to this...

Pulldown model class

class Pulldown extends AppModel {

	var $hasMany = array(
		’Option’	=> array(
			’className’		=> ’Option’,
			’finderQuery’	=> ’SELECT Option.* FROM pulldowns, options AS Option WHERE pulldowns.id={$__cakeID__$} AND pulldowns.use_set=Option.set’
		)
	);
	
}

So a quick test and yes, the CakePHP debug was showing that the SQL was running exactly as expected. So now, my "What is your gender?" Pulldown row was doing another SQL call, and getting three rows from the Option model without any errors. Excellent. Except when I went to look at my select pulldowns, I still had no options. What gives?

More poking and I discovered that, although the SQL was being run correctly, and the three rows were being found correctly, the results were not being put into my data result. The association was there, but it was always empty! Why? It had the rows, it even said so! Why wasn’t it available in my Model find() result?

More googling and digging in the cakePHP lib files, and I found that, even when rows for the association is found, CakePHP still checks to see if the Option foreignKey matched the Pulldown id field. If it didn’t match (which it couldn’t, since I did not have a pulldown_id field in my Option datatable), it wouldn’t add it to the returned data. Argh! Are you kidding me? If that field was in there like that, then I wouldn’t need the finderQuery!

Luckily, my co-worker Dwayne calmed me down and told me how to correct this. By just specifying the foreignKey in the association as "false", then that check would not happen...

Pulldown model class

class Pulldown extends AppModel {

	var $hasMany = array(
		’Option’	=> array(
			’className’	=> ’Option’,
			’foreignKey’	=> false,
			’finderQuery’	=> ’SELECT Option.* FROM pulldowns, options AS Option WHERE pulldowns.id={$__cakeID__$} AND pulldowns.use_set=Option.set’
		)
	);
	
}

Huzzah, at last! This works as I expected! For each Pulldown row, I can specify a hasMany relation using SQL that I specify. The important thing is that foreignKey must be specified, and it must be false, not null or 0. Hope this helps out anyone else trying to do the same! :)