Joined tables using MIGXdb

I’m migrating a legacy site, that has some one-to-many relations, looking for a way to get MIGX working.

The field author contains author ID’s (for more it’s comma delimited for now, no problem separating into a relation table).

What would be the best approach to get MIGX working for multi-select, and rendering of these values? I’ve tried using listbox with a @SELECT query, but I’ve never used it for multi-select before.

For displaying it in the table (not in the editing popup), I guess I’d have to use a custom renderer? While editing, is there a way to quickly add new authors, without having to have a separate MIGX tab?

I don’t exactly understand what you are trying to do.
Do you use a MIGXdb-TV or a custom manager page (CMP)?
Do these authors get assigned to a MODX resource or to another custom class?

Where do you have this MIGX tab?

You can use the same @SELECT binding with the TV type “listbox-multiple” to be able to select multiple values.

Yes, for displaying the names of the authors (and not just their IDs) in a grid column, you probably have to use the this.renderChunk renderer and create a custom snippet to query the data.

Let me break it down a bit.

I am using MIGX management (created MIGX Configs, configured columns and formtabs).
My XML schema contains two objects - translations and authors. I want to use MIGX management, to allow editing of this database in the backend.

One translation can have multiple authors - the foreign keys are currently comma separated.

I want to achieve, that when a user views the data, all authors names are printed, instead of ID’s - so customRendered here.
Editing must be supported as well. The user should be able to select one or more authors - so changing TV to listbox-multiple should help here as well.

I guess this will save them, separated by |? Can I somehow change this delimiter to a comma? Does this mean, I do not need to restructure data to introduce a relation table, since it’s a many-to-manyrelationship?

You don’t necessarily need the junction table for the many-to-many relationship.

But such a table can be helpful. For example, if you want to query all the translations that a specific author did. You’ll get a better performance with a junction table, than with comma-separated values and FIND_IN_SET. Especially if there is a lot of data.

You probably have to create custom “fields” and “update” processors for this.
Or use the “aftergetfields” and “beforesave” (or maybe “aftersave”) hooks.

1 Like

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.