Tag: cakephp

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.

15
Sep

What’s happening to me?!

I did some work on a CakePHP site earlier today, and I pretty much hated every minute of it. Somehow, I’ve gone from the kind of person who does everything in PHP, to one of those fanatical Ruby on Rails developers. There’s only one thing for it: I must re-write everything I’ve ever done in RoR! OK, maybe I won’t go that far. Yet…