Merge multiple relationship fields into a single multi-relationship field

Question

Has anyone looked into merging several pre-2.6 relationship fields into a 2.6+ multiple relationship field? For example, if I have a book store where each book can have multiple authors and currently have book_author, book_author2, and book_author3. This is clearly ugly and horrible, so I would like to just have book_authors, which would contain the related authors from any of the original three fields that was set.

I'm sure it could be done with enough SQL magicry, but I'm hoping someone has already figured out a good system for it.

Answer 1

If you look within the table structure for the relationship, the relationship data is stored within database table exp_relationships so only we need to update this table for the field_id. The parent_id and child_id are already in same format required for multi-relationship.

You need to follow these steps: 1) Make the "book_author" as multi-relations by checking "Allow multiple relations?" from the field settings.

2) book_author2 and book_author3 are no longer needed in relationship so exp_relationships database table need to be updated accordingly by SQL:

UPDATE exp_relationships LEFT JOIN exp_channel_titles 
ON exp_relationships.parent_id=exp_channel_titles.entry_id
SET exp_relationships.field_id=[book_author FIELD ID]
WHERE exp_channel_titles.channel_id=[CHANNEL ID]

You might update the [book_author FIELD ID] and [CHANNEL ID] according to your preference.

Please Note: I didn't try it yet but after seeing all the table structure for multi-relationship, it would work perfectly.

Once everything worked perfectly you can delete fields book_author2 and book_author3.

Answer 2

I think you would want to look at Matrix + Playa add-ons. You could add a Matrix field book_authors where each row is a Playa field type related to the author channel entries.

Or if each author is not a channel entry but instead more of a list, you could use Low Variables field type as a multi-select field (of author info created in a LV variable group) in your Matrix rows.