Add support for many-to-many relationships

[nanawel]

There's currently no option to edit a many-to-many relationship using a form (let's say the classical product<=>category link).

The select form component is limited to a single choice. It would be handy to also have a multiselect... with all the hard work to update the relations in the database kept under the hood 😉

[lovasoa]

You can already use checkboxes for that. Or doesn't that work for you?

I'll add a clear example with a description in https://sql.ophir.dev/documentation.sql?component=form#component

[nanawel]

You're right, I found I could theoretically use the checkbox type for that.

Unfortunately, I still have 2 problems with this:

• the checkboxes must be dynamic (each one is an entry of a table) and I can't figure out how to use the dynamic component for this use case (also for the POST + save process)
• if I'm not mistaken there's no way to set the checked attribute on the generated input.

Thanks for your help so far 👍

[lovasoa]

I don't understand the problem with checkboxes being dynamic.

Is there something preventing you from doing

SELECT 
    category_name as label, 
    'categories[]' as name, 
    'checkbox' as type
FROM categories 

?

[nanawel]

Well, it might be my fault but imagine the following case:

• 3 categories: A, B and C.
• A product P1 that belongs to A.
• A product P2 that belongs to B.

Id' like to display a form to edit a product, so for example when opening P1 I would need to display all 3 categories but only A checked.
When submitting the form, if the user has left A checked but has also checked C, it means that I need to save a new "P1-C" row in the product-category relation table, while preserving the existing "P1-A" row.
And so on for every possible cases (adding or removing relations).

Also, unrelated but as I'm thinking about it: there should be a way to give a title to the group of checkboxes since they are related and there is no parent element to hold this data (adding a text component ends the form).

[lovasoa]

Can you first delete the existing categories, than add the updated ones ?

[lovasoa]

I added an example of how to handle many-to-many relationships with forms to the repo: https://github.com/lovasoa/SQLpage/tree/main/examples/modeling%20a%20many%20to%20many%20relationship%20with%20a%20form

It won't work with SQLPage 0.7 because I had to fix an issue related to connections. It will be in v0.8, to be released next week.

[nanawel]

Thanks! 🎉