0

I'm trying to fetch a channel entry's data from within a plugin. I was surprised that ExpressionEngine's Channel Entries API includes functions for creating, updating, deleting, and checking whether an entry exists, but no function to get a channel entry. So I'm trying to use Codeigniter's db->get() function to fetch this data.

First, I tried just fetching the channel data:

$query = $EE->db
  ->select('*')
  ->from('exp_channel_titles')
  ->join('exp_channel_data',
    'exp_channel_data.entry_id = exp_channel_titles.entry_id')
  ->where('exp_channel_titles.entry_id' => 1)
  ->get();
$result = $query->result_array();
var_dump($result[0]);

This returns all of the channel entry's data except relationship data, unfortunately. My custom channel field with an ID of 106 is a relationship field, and I can set and update it through the ExpressionEngine CMS just fine, but when fetching the channel_data in this way the field is blank and its field type is (strangely) set to "xhtml":

["field_id_106"]=> string(0) "" ["field_ft_106"]=> string(5) "xhtml"

Next I considered that maybe I need to join the exp_relationships table to this query by adding a second join:

->join('exp_relationships',
  'exp_relationships.parent_id = exp_channel_titles.entry_id',
  'left outer')

But this still returns a blank string of fieldtype "xhtml" for my relationship field_id_106 in the channel data returned. So, any thoughts on how to fetch relationship data along with the rest of the channel data using Codeigniter in ExpressionEngine?

0

The best way to get that data is in two separate queries. Because of the way the data is stored in the database, it can not be joined in the way I think you are thinking about it.

Say in our title table we have something like:

    entry_id    title
    1           entry 1
    2           entry 2
    3           entry 3
    4           entry 4
    5           entry 5

In our relationships table

    parent_id   child_id
    1           2
    3           4
    3           5

If you try and do a join, it will not work due to entry_id 3 having two matches.

entry_id    title       child_id    
1           entry 1     2
2           entry 2
3           entry 3     4 and 5 (you can't join two rows on a single row)
4           entry 4
5           entry 5

If you are proficient with mysql you might could get away with using mysql's group_concat function (although this has a default limit of 1024 characters).

All that being said, I think the best way to do this is to get your channel data like normal, then run a separate query on the relationships table.

p.s. If you absolutely must do it in a single query, you are going to have to do regular php and mysql see this link

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.