ManyToMany... with Extra Fields on the Join Table?

Found it. Remove the ID column from the entity. Add the #[ORM\Id]annotations to both of the columns you want to make up the new primary key.

Hey Jim-Smm,

Glad you found the solution yourself! And thanks for sharing it with others :)

Cheers!

Also the generated code for removeQuestionTag() seems like it's going to fail hard by trying to setQuestion() to null. In fact I'm seeing this behavior. Would it be better to use a soft delete/removal field and use a data transformer to handle the messy details?

Hey @Aaron Kincer!

A few good questions here :).

> Given the introduction of an intermediary entity does this necessarily move at least part of the logic of adding a Tag into the controller or other service?

Probably, yes. That's probably how I would do it. Somewhere - either controller or a service - would probably have extra logic to help create this intermediate entity. Wrapping that up in a service is a nice way of doing that. However, in theory, you could still have a Question::addTag() method. Inside that method you would create the intermediate entity and set everything. To get this to save, you would probably need to do a "cascade persist" so that when you persist Question, that persists the QuestionTag... an that persists the Tag. This stuff usually makes me uncomfortable.

> Also the generated code for removeQuestionTag() seems like it's going to fail hard by trying to setQuestion() to null. In fact I'm seeing this behavior. Would it be better to use a soft delete/removal field and use a data transformer to handle the messy details?

Hmm, good catch. I would probably use orphan removal for this. This would go on the Question.questionTags property (if I'm thinking straight at the moment). It basically says "if a QuestionTag gets orphaned from a Question (which would happen when setting QuestionTag.question = null) delete it". In general, soft delete... is something I try to avoid - I did it a lot earlier in my career and it can cause weird things to happen.

Let me know if this... make sense :).

Cheers!

Strike that first paragraph. Turned out to be a bad idea that I got from some random post that seemed like a good idea at the time.

Hey @Aaron!

Sorry for the slow reply - complicated week over here ;).

About "being able to rollback changes/deletions". I'm not sure to be honest. The reason is that "real life" turns out to be a lot more complex. So, yes, you could implement a soft delete type of thing. But then, you are really only able to revert deletes, not updates. I have seen things where you setup a database that actually versions all of your changes, but that adds a LOT of complexity. And then, what if changing and Article from "Unpublish to published" actually triggers 3 other non-database changes (e.g. it changes something in a CRM... and also dispatches a Messenger message that tweaks a file in some way). Even if you could revert the data in the database, these types of things would not automatically be reversed.

So, in theory having a system where you can roll back any changes is possible, but it's a complex problem. This is something... I "think" event sourcing kind of addresses. This is a programming practice (that I don't have a lot of experience with) where every change you make in your system is done with an event. And so, you could then "replay" every event in history if you wanted to, to arrive at today's "state" in your app. I don't think event sourcing would automatically give you a "rollback" functionality, but in theory, it would make it easier to do. Think of the "undo" on any app you use: that app is keeping track of all of the "events" that have happened. And so, when you "undo", it knows what the last "action" was and has some logic for figuring how to "invert" that action.

Btw, if your goal is more "to be able to undo and get back old data IF something ever went wrong", but not necessarily make this a simple, automatic process, then doing a lot more logging or event sourcing (which is, in some ways, a very fancy logging system), would be the way to go. The critical question to ask is: "Am I planning for a routine occurrence in my app? Like, where a normal admin user might be able to rollback any number of states in the database at any time?" Or is it more "In case something goes wrong in an unforeseen way, we want to have the data we need to be able to have some programmers fix things". Let that guide you. The answer might be mostly the latter... but with a few areas where you DO want an admin user to be able to "undo" things (e.g. unsend this invoice). In that case, log a lot to cover the 2nd case... then write custom code to handle being able to unsend an invoice.

Cheers!

Thanks! I'll spend some time thinking it through thoroughly. I _think_ that what I'm trying to accomplish could in fact be achieved through hefty logging. And the changes should be pretty light on the data.

Yes it makes sense. I moved the logic to my DTO DataTransformer where all the data action takes place. So far my controllers on this app don't even use the EM. Well except for the registration controller at least for now.

What have you found as the generally better solution in deletes when the nature of the data is such that full historical fidelity with the ability to roll back changes/deletions if needed is desired? Loggable? If so does that scale? That's what I'm trying to solve and why I asked about soft deletes.

Thank you so much for the insight.

Yo Kevin!

Thanks :). I hope it was useful!

> How common is it to add extra columns to a pivot table? What are some real world scenarios where you would reach for this

I think it IS quite common... but I would also bet that you might have projects where this rarely happens and projects where this *constantly* happens. I think the example we gave here is a pretty good one, but let me use a different (but similar) example. Suppose you have a way for two Users to become "friends" or "connected". If you wanted to store when they were connected, that would be an extra field. Or, if you wanted to store who *initiated* the connection (e.g. originalRequestedBy), that would be another situation.

And there are even MORE situations where you are technically doing this, but you never even think about it! Imagine an Order entity (as in "product orders"). Each "Order" can have many Products and each Product can be included in many Orders. But actually, when you model this, you likely have Order OneToMany to OrderItem ManyToOne to Product, where OrderItem includes (at the very least) a "quantity" column. Even though it may not feel like it, this is yet another example of a "pivot table" with extra columns... even if you don't think of it that way :).

Cheers!

Awesome! Thank you for the quick reply. That was very helpful!

Hey wLcDesigns,

We're happy to hear it was helpful for you! ManyToMany complex relations might be tricky sometimes. Thank you for your feedback ;)

Cheers!