Join tables with array

lincolncbennett

Hi, I am trying to get pointed in the right direction with a project I am working on using datatables/editor. I have a basic table which displays a few columns of data for clients. eg. Name, Address, Date of Birth.

I need to add some complex data as an array to each client and use editor to create/edit this. For example for each client I need to add number of marriages, location of marriage, spouse details ect. which can be a number of rows of data for each client.

I want to store this data in a separate table in the database and I understand how to join tables but I am not sure how join arrays?

I just wanted to see if there was any examples or documentation on this before posting any code. any help in the right direction would be greatly appreciated.

rf1234

Since one client can have multiple client detail records you would need to model this using a link table between "client" and "detail". You could call it "client_has_detail" or whatever. The link table only contains the ids of "client" and "detail" like in the "post-it example" discussed in this thread:
https://datatables.net/forums/discussion/comment/170006/#Comment_170006

Afterwards you can use an Mjoin to display the child table records jointly with one parent record like in this example - and also to assign child records to a parent record using Editor:
https://editor.datatables.net/examples/advanced/joinArray.html

Take a look at column "Permissions". The relationship between "users" and "permissions" is the same as the relationship between "client" and "detail". And "user_permisson" is the same as "client_has_detail".

rf1234

You may either show your child records like in the example above: You just put them in a separate column like "Permissions" and display them as a comma separated string with or without line breaks.

But you could also show them as child rows like in this blog example which might be more appropriate for your use case:
https://datatables.net/blog/2019-01-11

lincolncbennett

Many thanks, I will check this out today and get back to you.