2

I have a site that want to link blog posts to their author pages. Authors have their own entries in a staff channel. Currently they've just used a plain text select field which has had the options manually populated.

What they want to do is use a Playa field instead. So they want to dynamically update the Playa field based on the values of the plain select field.

So at the moment they've got:

{blog_channel}: {author_custom_field} = "Author Name" {staff_channel}: {title} = "Author Name" {url_title} = "author-name"

I need to write some SQL that will look at the value of the author_custom_field, check it against the staff_channel to see if there's any open entries, and if there is, update a new author_playa_custom_field with those values.

That will involve updating the relevant field_id column in exp_channel_data with values like [2256] [author-name] Author Name but also update exp_playa_relationships with parent_entry_id, parent_field_id and child_entry_id.

Has anyone done something similar before?

1

I came up with what I needed if anyone's interested:

UPDATE exp_channel_data cd

INNER JOIN exp_channel_titles ct
  ON ct.title = cd.field_id_14
  AND ct.channel_id = 8 
  AND ct.status = 'open'

SET cd.field_id_56 = CONCAT('[',ct.entry_id,'] [',ct.url_title,'] ',ct.title)

WHERE cd.channel_id = 2

followed by

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)

SELECT cd.entry_id, 56, null, null, null, ct.entry_id, 0 

FROM exp_channel_data cd

INNER JOIN exp_channel_titles ct
  ON ct.title = cd.field_id_14
  AND ct.channel_id = 8 
  AND ct.status = 'open'

WHERE cd.channel_id = 2

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.