THE BLOG

31
Oct

Using CakePHP’s finderQuery Attribute For Complex Associations

I had some fun (“fun”) on a project the other day when I had to write a rather complex has-and-belongs-to-many association for a model in CakePHP. The basic scenario was this:

  • There are ‘Meetings’, which are associated with many People (and many People can be associated with many Meetings, but that’s not important here)
  • Each meeting has many ‘Conversations’, which currently are assumed to involve everyone in the Meeting

My task was to alter this model so as to cater for Conversations that only involve a subset of the People involved in the Meeting. For backwards compatibility, and to save on space (although admittedly the join table wouldn’t have exactly been huge), we decided to go with the following model:

  • We would create a ‘ConversationPerson’ model, which would obviously store the people involved in a conversation, but…
  • If a Conversation has no records in the join table, then the People involved in the Conversation is assumed to be all the people in the Meeting.

(Does that make sense?)

This is a (relatively) easy query to construct, so we decided to use a single query to define the association and then use the finderQuery parameter to tell Cake how to get the data. Our Conversation model then looked a bit like this:

<?php
class Conversation extends AppModel {
	...
	$hasAndBelongsToMany = array(
		'Person' => array(
			'finderQuery' =>
				'SELECT `ConversationPerson`.`id` AS `conversation_id`, 
						`Person`.* 
				 FROM 	`people` AS `Person`, 
						`meeting_people` AS `MeetingPerson`, 
						// Left join conversations with conversation_person
						// so we don't lose any conversations if there are 
						// no rows in conversation_people, and so we don't
						// get extra rows if there are
						`conversations` AS `Conversation` 
						LEFT JOIN 
						`conversation_people` AS `ConversationPerson` 
						ON 
						`Conversation`.`id` = `ConversationPerson`.`conversation_id`,
						// Count the number of associations in the
						// conversation_people table
						(SELECT COUNT(*) AS count 
						FROM `conversation_people` AS `CountConversationPeople` 
						WHERE `CountConversationPeople`.`conversation_id` = {$__cakeID__$}) 
						AS `Count`
						// This mimics the operation of an
						// if(count == 0){...}else{...}
						// If there are no associations in the conversation_person
						// table, grab the people from the meeting_person table
				 WHERE (`Count`.`count` = 0 AND 
						`Conversation`.`id` = {$__cakeID__$} AND 
						`Person`.`id` = `MeetingPerson`.`person_id` AND 
						`Conversation`.`meeting_id` = `MeetingPerson`.`meeting_id`)
						// If there are associations in the conversation_person
						// table, use those to define the people in the conversation
					OR (`Count`.`count` > 0 AND 
						`Conversation`.`id` = {$__cakeID__$} AND 
						`MeetingPerson`.`meeting_id` = `Conversation`.`meeting_id` AND 
						`MeetingPerson`.`person_id` = `ConversationPerson`.`person_id` AND 
						`ConversationPerson`.`person_id` = `Person`.`id`)'
		)
	);	
	...
}
?>	

This seemed alright, but Cake was throwing me error after error, and it took me a while to figure out what was going on. It turns out that when you perform a HABTM find in Cake, it expects to always see a join table. This is especially apparent when you select a group of rows, since it then uses the values in the join table to determine which row is associated with which model.

In the example above, Cake was expecting to always see two fields, one called ConversationPerson.conversation_id and another called ConversationPerson.person_id. Obviously, in the cases where the association exists in the join table, this is fine. However, no associations exist and we grab the data from the MeetingPerson table, Cake is unhappy. Unfortunately, Cake isn’t clever enough to look for those ids (conversation_id and person_id) in the other fields it returns, it explicitly wants them to come from a table called ConversationPeople.

Obviously, the solution is to use the query we created above to construct the join table, and then select from that. It is a bit frustrating that there isn’t a neater solution to this, but there we go. So, our Conversation model looked like this:

<?php
class Conversation extends AppModel {
	...
	$hasAndBelongsToMany = array(
		'Person' => array(
			'finderQuery' =>
				'SELECT `Person`.*, `ConversationPerson`.*
				FROM `people` AS `Person`,
					(SELECT `ConversationPerson`.`id` AS `conversation_id`, 
							`Person`.* 
					 FROM 	`people` AS `Person`, 
							`meeting_people` AS `MeetingPerson`, 
							`conversations` AS `Conversation` 
							LEFT JOIN 
							`conversation_people` AS `ConversationPerson` 
							ON 
							`Conversation`.`id` = `ConversationPerson`.`conversation_id`,
							(SELECT COUNT(*) AS count 
							FROM `conversation_people` AS `CountConversationPeople` 
							WHERE 
							`CountConversationPeople`.`conversation_id` = {$__cakeID__$}) 
							AS `Count`
					 WHERE (`Count`.`count` = 0 AND 
							`Conversation`.`id` = {$__cakeID__$} AND 
							`Person`.`id` = `MeetingPerson`.`person_id` AND 
							`Conversation`.`meeting_id` = `MeetingPerson`.`meeting_id`)
						OR (`Count`.`count` > 0 AND 
							`Conversation`.`id` = {$__cakeID__$} AND 
							`MeetingPerson`.`meeting_id` = `Conversation`.`meeting_id` AND 
							`MeetingPerson`.`person_id` = `ConversationPerson`.`person_id` 
							AND `ConversationPerson`.`person_id` = `Person`.`id`))
					AS `ConversationUser`
				WHERE `Person`.`id` = `ConversationUser`.`person_id`'
		)
	);	
	...
}
?>

In retrospect, I should have just bitten the bullet and populated the ConversationPerson table with the values from MeetingPerson (or perhaps gone one step further and inferred the people in the meeting from the people involved in Conversations in that meeting, but whatever), but in this case I think this was probably the nicest solution.

The example in this post is so incredibly edge-case that it will probably help absolutely no-one, but I thought I’d put it up anyway, since the Cake docs don’t mention requiring those particular fields when specifying a finderQuery.

24
Oct

An Interesting IE6 Bug (bleurgh)

I came across an interesting bug in IE6 the other day, and couldn’t find any documentation of it on the internet, so thought I’d share it with you all here (aren’t you lucky?).

I was working on a distributable widget, which has the functionality to grab some embed code so you can place it with pride on your own website. This embed code was generated dynamically by JavaScript based on some parameters the user entered, and was then placed in a textfield using the jQuery $().val(value) function. This worked fine in most browsers, but IE6 would throw a wobbly at this point in the form of an “Unspecified Error”. Incredibly helpful.

I found that the bug didn’t occur if I escape()‘d the value before I passed it into the textfield. It seems, therefore, that IE6 has an issue with strings that contain HTML content – presumably it was trying to parse the HTML when it was put in the textfield. The same behaviour was observed when using document.getElementById(…).innerHTML = value; as well.

The solution was to use the jQuery function $().text() – and this works cross browser. Presumably this only works for textfields – if you are setting the contents of normal text inputs then I’m not quite sure what you will observe. But then, you never are with IE6…