I have been doing a lot of work with ExpressionEngine over the last couple of years, and have logged more time in the database and with the PHP code than in the Control Panel (that includes time spent editing templates). I have the found the ASCII art of Pinky and the Brain. Anyway, if you know anything about ExpressionEngine, you know that it is built on top of an open-source framework called CodeIgniter that is released by the same company as ExpressionEngine (EllisLabs).

CodeIgniter has a database abstraction layer called ActiveRecord. This abstraction is supposed to allow CI-based sites to switch between database engines very easily. ExpressionEngine continues to support only MySQL, but developers are encouraged to utilize CI’s ActiveRecord class.

Tonight I am hacking Structure in the name of building an accurate sitemap. Another add-on has been interacting with it and caused some entries to get tied to the wrong parents (it corrects the entries as they come out of the Structure tags). I have tried several approaches to retrieve the correct URLs. That other add-on has class functions which could correct this but they are “private” and I want this to continue to work if upgrades are made. Copying those functions seems like a bad idea. My current approach is to start off with the entries themselves and then to work up Structure’s exp_structure and exp_structure_listings tables.

The cleanest way to write this in PHP involves merging the data from those two tables. ExpressionEngine uses MySQL, so a UNION would fit the bill perfectly.

Unfortunately, if you glance over the ActiveRecord docs or code, there is no support for the UNION instruction. I did a quick search for the proper way to use UNION with ActiveRecord and turned up a post on StackOverflow. Someone by the name of Souvik recommended doing exactly what I was already thinking, and that is to use one of ActiveRecord’s “private” class functions that is accessible. It looked like the cleanest hybrid for ActiveRecord usage:

$this->EE->db->select('ct.entry_id AS parent_id, ct.url_title, s.entry_id');

$this->EE->db->from('structure s');
$this->EE->db->join('channel_titles ct', 'ct.entry_id = s.parent_id');
$this->EE->db->where_in('s.entry_id', $tracker);
$this->EE->db->where('s.site_id', $this->EE->config->config['site_id']);
$first = $this->EE->db->_compile_select();
$this->EE->db->_reset_select();
$this->EE->db->select('ct.entry_id AS parent_id, ct.url_title, sl.entry_id');
$this->EE->db->from('structure_listings sl');
$this->EE->db->join('channel_titles ct', 'ct.entry_id = sl.parent_id');
$this->EE->db->where_in('sl.entry_id', $tracker);
$this->EE->db->where('sl.site_id', $this->EE->config->config['site_id']);
$second = $this->EE->db->_compile_select();
$this->EE->db->_reset_select();

$query = $this->EE->db->query('('.$first.') UNION ('.$second.')');

Now, if we lived in a perfect world, we would have a good solution. Since we do not, MySQL throws this error:

Error in query: Syntax error near 'JOIN `exp_channel_titles` ct ON `ct`.`entry_id` = `sl`.`parent_id` WHERE `sl`.`' at line 7

It turns out that ActiveRecord wraps the FROM clause for both queries in parenthesis and MySQL dislikes this when combined with a UNION. The fix is to send $first and $second through a simple string replacement:

$first = preg_replace('/FROM \(([^\)]+)\)/', 'FROM $1', $first);
$second = preg_replace('/FROM \(([^\)]+)\)/', 'FROM $1', $second);

And that should get you on your way. Happy hacking.