2

I've been experimenting with using SQL INSERTs and UPDATEs instead of using the Channel Entries API in my add-on. In one of my channels I am using a playa field which is simply connected to a separate channel (in this case, my contacts channel).

When using the Channel Entries API, I format the playa field like so:

$playa_field = Array('selections' => Array($entry_id));

This works perfectly every time. However, when you examine the output of this in the database, you get the following format:

[entry-id] [url-title] Full Name
e.g. [82] [joe-bloggs] Joe Bloggs

To create this format using the necessary variables I use the following code:

$formatted_contact = "[$entry_id] [".$meta['url_title']."] ".$e_data['contact_name'];

And embed it in the following query to insert a new item into my database:

$titleQ = $this->EE->db->query("INSERT INTO `exp_channel_titles` (`site_id`, `channel_id`, `author_id`, `title`, `url_title`, `status`, `entry_date`) VALUES ('1', '3', '".$meta['author_id']."', '".$touch_date.' '.$touch_type."', '".str_replace(' ', '-', $touch_date.' '.$touch_type)."', 'open', '$touch_date')");
$new_entry_id = $this->EE->db->insert_id();
echo 'new_entry_id is: '.$new_entry_id;
$entryQ = $this->EE->db->query("INSERT INTO `exp_channel_data` (`entry_id`, `site_id`, `channel_id`, `field_id_14`, `field_id_4`, `field_id_6`, `field_ft_6`, `field_id_48`, `field_id_49`, `field_id_53`, `field_id_52`) VALUES ('$new_entry_id', '1', '3', '$touch_date', '$touch_type', '$formatted_contact', 'none', '$touch_complete', '$touch_inactive', '$touch_comment', '$touch_source')");

All my code executes well enough from what I can see, however the playa field doesn't seem to get picked up along the way. If anyone can point out the potentially glaring formatting error it would be great! Please note, I am only looking at SQL as an alternative for the purposes of enhancing my system's speed and this will only be done in the background with little margin for error.

Cheers!

  • 1
    I'm not sure, but playa has its own mechanism to store data in its own tables. What is put in the channel_data is merely a basic abstract for search/display and convenience if i recall. – GDmac Jan 29 '13 at 0:33
  • Actually, does anyone know how to add to the exp_playa_relationships table using the channel_entries api? I've just created an entry and now need to add to the playa table but don't know how to get the newly created entry_id. – Jim Pannell Oct 8 '13 at 15:28
2

@GDmac, thank you for your response and great spot:

$relQ = $this->EE->db->query("INSERT INTO `exp_playa_relationships` (`parent_entry_id`, `parent_field_id`, `parent_col_id`, `parent_row_id`, `parent_var_id`, `child_entry_id`, `rel_order`) VALUES ('$new_entry_id', '6', 'NULL', 'NULL', 'NULL', '$entry_id', '0') ");

The above code is now called in my script and everything works out perfectly!

| improve this answer | |
  • 1
    Note that done this way - as opposed to using the Channel Entries API - Playa will not put it's concatenated data in your exp_channel_data field, so it won't be searchable, and other Playa functions may not work as expect either. Playa assumes that that concatenated data will be there. So either add that manually in your query, or use the Channel Entries API. – Derek Hogue Jan 29 '13 at 2:41
  • @DerekHogue, I am combining the above query with my two previously mentioned ones. So in this channel in particular, I have a single playa filed and the rest are either dates or standard inputs. I have about 6 or 7 fields that need to be filled out. So the $relQ query will manage the playa field after I have inserted data into both the exp_channel_titles and exp_channel_data tables manually. I would strongly advise others who are working with larger and more complex channels/fields against this method however. In my case the channel is tiny and the operation is not worth the excessive waiting. – Conor Higgins Jan 29 '13 at 9:43

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.