Symphony.

I've just posted an article of sorts on the github wiki about my investigations into relationships in Symphony.

While there is still a long way to go before a new useable solution is found, it tackles the inherent issue that I see in everyone's day to day usage of relating sections, so I would suggest everyone should read it.

If anyone has any questions or comments, can you add them here.

Admins, can you sticky this post please for now?

Thanks for writing this up designermonkey. It was a good read Dr. I think you're overview is fairly accurate and I agree with your opinion that relationships need to be dealt with at the core level.

I think your interim solution of providing different views within the parent would help significantly and is something I have considered doing in the past for projects. I think this is a good start in terms of also understanding the problem better in coming up with an appropriate solution.

My thinking all along has been that fields should not dictate the relationships between entries across different sections. Fields should only provide interfaces to manage the relationships. The relationships themselves, however, should be derived from a section's settings.

Thanks for sticking this Lewis.

Progress is being made on a roadmap for how to handle this moving forward. It was taken offline as to be honest, it was going round in circles for years, and getting nowhere fast.

After a couple more discussions to come between myself and some key people, I think we will be making a start on actually fixing this, so it shouldn't be long now... Hopefully.

After these discussions, and when we have the 'plan' in place, I'll post the plan on github for all to see where we will be heading with this.

I already know there will be some sneers from deep core php programmers, but we will all have to remember that to implement relationships as some ideas have stated would need a full rewrite, which will not happen I'm afraid.

Anyway, I'm babbling now.

John (or Nils), can you please elaborate on what exactly is meant with: I don't attach an article to my comment, I add my comment to an article?

For me (and my clients) I found comments to be one of the things they do understand, and get right on the first try. When you think about it, saving files to a folder from an editor you get the exact same behaviour: first you create the folder, then you attach the file to it.

The thing where it seems to break for me is when an entry has required children (an article, with a cover photo, for example). Now, because the article is required to have a cover photo, the relation has to be reversed, and the workflow is completely backwards: create cover first, then create article. When the cover is not there yet, you are not able to save the article (or you have to use a dummy cover, create the real cover, go back to the article, select new cover, save. Buh).

If the new field can deal with this: hooray!

Create the parent. From the parent index view, click to go through to the child section (filtered if using the link in the parent index view), then create the child.

That's the correct way of doing it.

I think there can be multiple scenarios, ie. there is no single one correct way to do it.

Hey, this isn't the agony aunt column I was expecting... ;)

Interesting read. Out of interest in your article you've mentioned about one-to-one and one-to-many relationships but how does it/should it work with many-to-many relationships? In a relational database sense this is normally handled with a pivot table to keep track of the relationship status. Is this what you refer to when you mention the association table? Is this proposed or currently present?

I agree that from a workflow perspective it needs to work both ways in that you can, for example, attache an image to an article but whilst I'm writing the article I'd also like the ability to upload an image to the images/assets channel (example only). In comparison with a similar system in ExpressionEngine this is how the approach is handled. (Similar in the sense that it's resource oriented in that everything belongs in a channel and channels are related to one another).

My thinking all along has been that fields should not dictate the relationships between entries across different sections. Fields should only provide interfaces to manage the relationships. The relationships themselves, however, should be derived from a section's settings.

I'm not so sure. I'd consider a section to just be a resource, a collection of entries of a particular type. I wouldn't want to apply settings to this collection of data in regards to relationships to other sections as this may change. I'd only want a way to pull in or push to another section from a particular field.

@John, that's the way I deal with comments, yes (attaching the comment to the article), and I (and my clients) find that very straight-forward, as the order makes sense (before you can post a commen to an article, the article must exist. Duh!).

However, in the example I mentioned the link has to be reversed, because that's the only way of forcing a child/parent entry to be set, which makes the whole thing a bit more difficult: if you create an article, the author must exist - but what if it isn't and you only find out halfway through posting the article?

Sure, I could tell my editors: you must always create new articles by going to authors, then clicking on the number of articles he/she has created, then create a new one. But then the same holds for category, and cover photo/video...

Previously I'd use the SSM for this, but it'd be great if the new field you are working on would allow for this. Any thoughts?

I think there are two distinct problems we need to tackle:

• First, we need to define a logic how to store entry/section associations and how to manage them. This has to be mainly solved on the PHP and MySQL side and we have – as John pointed out – a more or less working solution in the Symphony codebase.
• Secondly, we need to create interfaces from two perspectives: the child and the parent. Ideally, these interfaces should work seamlessly independent of where we store the entry association (in the parent or the child): so the users can start from the parent or from the child. This is where things get complicated.

For me, the most important conclusion from John's research is that we need to keep these two problems separated. Even if I might think it's more logical to manage the association in the parent, it makes sense to store it in the child entry.

So the UI needs to handle associations multidirectional while the SQL storage can be one-directional. If the UI is good, the users won't have to think about association directions and if the UI is great, the users won't actually notice that there are parents and children. Nevertheless, that logic exists under the hood.

PS: Just to write this down: Select Box Link stores the links in the children while Subsection Manage stores the links in the parent. This is what makes the two extensions incompatible at the moment. The idea is to agree on an unified storage concept (the SBL one in our case) and add association display and editing from the parent as additional feature (the concept known from SSM).

Just to write this down: Select Box Link stores the links in the children while Subsection Manage stores the links in the parent.

To clarify, if you have your SBL in the Comments section which points to your Articles section (meaning that to associate Comments with Articles, the Article entry first has to exist), the relationship will be saved to the section_associations table like so:

• parent_section_id - The ID of the Articles section
• parent_section_field - The ID of the field in the Articles section whose value is displayed in the Comments UI
• child_section_id - The ID of the Comments section
• child_section_field - The ID of the SBL in the Comments section
• hide_association - Boolean on whether to show the related number of Comments in the publish index of the Articles section

One might argue that the storing of this information doesn't prevent a field from doing anything in a UI sense. Thoughts?

Edit

Thinking about this again after rereading, I can see some room for improvement. If you are in the Articles section, while you can query the section_associations table to find out if there is any relationships that involve this section, it would be difficult to build a UI to show the related Comments entries as you would not know what values to use to construct the UI (ie. there is no equivalent to the parent_section_field for each Comment).

SSM circumvented this by using an iframe to embed the entire Comment entry inside the parent (Article), but then it lacked the UI in the opposite direction, creating a Comment and attaching it to an Article.

It is also true that the data is only stored one way, so when a Comment is saved, the SBL field will save the current entry_id of the Comment, and the entry id of the Article it is related to (relation_id). To find out what Comments relate to a particular Article's entry section, one would have to query the section_associations table to retrieve the child_field_id of any relationship's that involve the Article's section_id, and then query the field(s) to find out if the current entry_id exists (relation_id in the SBL data table). If it does, return all the entry_id's of those Comment entries to build the Entry objects.

That's probably going to shed some light for a lot of people :)

One might argue that the storing of this information doesn't prevent a field from doing anything in a UI sense.

That's what I was trying to say. Thanks!

@brendo: I agree. I have never seen the necessity to change the SBL field's database storage logic.

In my biggest project, I have built my own admin interface in the frontend which does a lot of cool things with relations — but I am using the SBL field exclusively to handle those relations internally. Later I have tried and re-created parts of the frontend's UI in the Symphony backend using HTML Panel Fields. But this is not really easy... Here is an example of entries from an image section attached to an article entry:

Using the links in the HTML panel, the author can create new images, edit images etc. He will do this in the image section (i.e. the image section's edit page), where he will find another HTML Panel Field with a link back to the article section.

So in my eyes (and I have said this many times) only the GUI is the SBL's problem.

Attachments:

Bildschirmfoto 2013-01-07 um 15.33.51.png

So in my eyes (and I have said this many times) only the GUI is the SBL's problem.

There is one additional flaw in my eyes: XML output. As well as we are missing child references in parent's section UI, we are missing inline child output in the XML.

I know that Symphony advocates using output parameters to connect data but I think nested output of child entries is easier to use as it reduces template complexity (but I acknowledge that it makes things more complicated on the PHP/MySQL side, especially when thinking about performance).

One might argue that the storing of this information doesn't prevent a field from doing anything in a UI sense. Thoughts?

Agreed. Allows for loose coupling and data normalisation (no redundancy). The database layer seems to be the easy part.

Thinking about this again after rereading, I can see some room for improvement. If you are in the Articles section, while you can query the sectionassociations table to find out if there is any relationships that involve this section, it would be difficult to build a UI to show the related Comments entries as you would not know what values to use to construct the UI (ie. there is no equivalent to the parentsection_field for each Comment).

I'm not entirely sure if I understand on this bit. Would it not be possible to store a certain amount of relationship information with the field itself? So you add a field to each section—articles and comments to follow the flow—but when adding the field to each section you define it's relationship by selecting which section/field it relates to? Then you could return all of these results with the field and allow for selection—possibly multiple?

One might argue that the storing of this information doesn't prevent a field from doing anything in a UI sense. Thoughts?

I see that one advantage of Subsection Manage over SBL is sortable entries.

I don't know if it's just an UI concern, but it would be great to have sortable feature in mind.

I even imagine the possibility of sorting different sections entries in one consolidated area, like Storify does...

So in my eyes (and I have said this many times) only the GUI is the SBL's problem.

Agreed. For me, the ideal solution would be the core's SBL with an extendable UI (similar to what's offered by SBL+, but might need better implementation) and SSM's XML output.

Look what happens in my absence from the topic! :D

The field type I've been working on isn't really a field, but a reference to any incoming associations to the section it is added in, so when it's in an articles section for instance, it will display a UI for the comments, if the comments association was selected when the pseudo-field was added. It all works apart from how to display it, where I fall down.

Since prototyping this work, and in continuous discussion with Nils, I had the thought that it should just be a core feature, like the reference in the index table is, so the SBL might change slightly to accomodate this somehow.

Back to what Brendan edited, I've done all this already, and it isn't difficult at all, but as stated, it's the UI that's the issue.

Back to what Ian (@ijy) asked re many-to-many, we want to concentrate on getting one-to-one done first, as currently, a many to many can be done with an intermediary section, although not elegant from a usability perspective, it can be done. All the use cases I have ever come across so far in Symphony have never required that scenario, so it can be shelved for now.

I'm not entirely sure if I understand on this bit. Would it not be possible to store a certain amount of relationship information with the field itself?

To be perfectly honest, after all my looking and playing, all the info stored in the SBL is is the entry > entry IDs, the association data is held in the section_associations table, where it should be. The data is where it all should be IMO.

I have a proposal of work to be done to begin this and see how we go. Just waiting for Brendan to give it the once over from a core perspective as he is the core lead, and we can then start to discuss that. The keyword we have at present is: Drawer ;o)

Start thinking on this guys...

@ijy: When it comes to many-to-many relationships, there is often no need for an intermediate column, as that is already created in the background for the one-to-many relation. For example, imagine the classic books and authors sections, if you choose the author on the book entry, and you enable allow the selection of multiple entries, you have effectively created a many-to-many relation.

Each book can now have multiple authors, and an author can have multiple books.

Drawer

AWSM.

Each book can now have multiple authors, and an author can have multiple books.

That's a good scenario, and I'm ashamed to have not realised that.

I shall go now and flog myself in my shame.

flog