I’ve been using Expression Engine for a couple of weeks now, mostly for work projects. It’s been good to see how a group of people have thought of abstracting data for almost any application. It will be interesting to see if it can solve many of the problems I’ve run into when developing extremely custom applications.

So, one problem that we’ve come across is a way to relate several tables (or weblogs as EE calls them) together. An example would be like so: doctors, their specialties, and the locations of their practice. A likely scenario is a user wants to find out what locations provide a certain specialty. Thus, you have to use the doctors as the bridge of the data.

In Expression Engine you can use a related field to tie two tables of data together. For instance, I can tie a Doctor to a certain specialty using a related field. But, creating a bridge between the doctors’ specialties and all the locations is not possible without a little custom programming.

Also, throw in the mix that doctors can have offices at several locations, and you’ve got a hairy set of data. (We’re using the multi-relationship plug-in from here).

So, let’s get to the code.

<?php
global $DB;
$addresses = $DB->query("SELECT * FROM `exp_weblog_data` WHERE weblog_id = 6");
foreach($addresses->result as $address){
	$doctors = $DB->query("SELECT * FROM `exp_weblog_data` WHERE weblog_id = 3");
	foreach($doctors->result as $doc){
		$rel_field_ids = array(2);
		$rel_field_ids_new = '';
		$rel_field_ids[0] = $doc['field_id_34'];
		$rel_field_ids[1] = ereg_replace("\r",",",$doc['field_id_35']);
		$rel_field_ids_new = "'" . implode("','",$rel_field_ids) . "'";
		$specialties = $DB->query("SELECT * FROM exp_relationships WHERE rel_id IN (" . $rel_field_ids_new . ")");
		foreach($specialties->result as $specialty){
			if($specialty['rel_child_id'] == $address['entry_id']){
				$specs[] = $specialty['rel_parent_id'];
			}
		}
	}

	if(sizeof($specs) > 0) {
		$specialties_ids = $DB->query("SELECT field_id_13 FROM exp_weblog_data WHERE entry_id IN(" . implode($specs,',') . ")");
		foreach($specialties_ids->result as $s_id){
			$related_spec = $DB->query("SELECT * FROM exp_relationships WHERE rel_id = " . $s_id['field_id_13']);
			$related_spec_title = $DB->query("SELECT title FROM exp_weblog_titles WHERE entry_id = " . $related_spec->row['rel_child_id']);
			if(!in_array($related_spec_title->row['title'],$titles_array)){ $titles_array[] = $related_spec_title->row['title']; }
		}
	}
	implode($titles_array,"','")
}
?>

So, in a nutshell the code above will compare the locations with the specialties using the exp_relationships table and the doctor as the bridge.

I had to think about it a bit, but you kinda get the idea. I hope this helps someone out there struggling with the same issue.

Happy coding.

Note: When copying this code make sure you replace the curly quotes.