Two foreign keys as primary key

Phoffman

Hello,
I have a many to many relationship between 2 tables using one third table like this:

products (id, name, description, ...)
packagings (id, name, description, ...)
products_packagings (product_id, description_id) (to have it now simple I do not add other columns, like quantity, weight...)

I want to use datatable inside of product details edit page, in the section of packaging of the product. I have successfully created the datatable and created one record using the "New" button. However edit and remove do not work. I expect the reason is because the table do not have one id column.

var editor;
    $(document).ready(function () {
        editor = new $.fn.dataTable.Editor({
            ajax: {
              url: "/admin/editProduct/4/packaging/json",
              type: 'POST', 'headers': {'X-CSRF-TOKEN': '6BuKzqxW1ufcd61yj34I7jz9RZl6BYgbcoVzCCWd'}
            },
            table: '#product_packagingTable',
            idSrc: "id",
            fields: [{
              label: 'Product id',
              name: 'product_id',
              type: 'readonly'
            },
            {
              label: 'Packaging id',
              name: 'packaging_id',
            }]
          });

        $('#product_packagingTable').DataTable({
            ajax: {
            url: "/admin/editProduct/4/packaging/json",
            type: 'POST','headers': {'X-CSRF-TOKEN': '6BuKzqxW1ufcd61yj34I7jz9RZl6BYgbcoVzCCWd'}
            },
            columns: [
              {
                data: 'product_id',
              },
              {
                data: 'packaging_id',
              }],
            dom: 'Bfrtip',
            select: true,
            order: [],
            buttons: [
              {extend: 'create', editor: editor, formButtons: [
                'Save',{ label: 'Cancel', fn: function () { this.close(); } }]},
              {extend: 'edit', editor: editor, formButtons: [
                'Save',{ label: 'Cancel', fn: function () { this.close(); } }]},
              {extend: 'remove', editor: editor},
            ]
          });
    });

Can you help me what I shall do to have the edit and remove working?

rf1234

Interesting use case! But I wonder why you would want to have this? You have two id fields and no further information that the user can see. If you really want to have such a link table in focus of your Editor instance it must have an id field. I have similar use cases but those link tables always have additional attributes and hence aren't pure link tables. In these cases the tables always need their own ids.

Let's assume you had an Editor instance for "product" and you wanted to assign one or more "packaging"s to it and choose the "packaging" by its "name" and/or "description" from a drop down or select list.

You'll find this in this example: The example has a "user" and the "user" is being assigned one or more "permission"s. You do this with an Mjoin instance on the server side. You could use the exact same logic for your "product" and assign to it one or more "packaging"s.

https://editor.datatables.net/examples/advanced/joinArray.html

For your reference I have attached a copy of the example's data model.

allan

What are you using at the server-side? If its the PHP or .NET libraries you can use a compound key to get this working.

Allan

rf1234

I missed that new feature, Allan. Good to know!

Phoffman

Hello both,
I have php. I have some more fields in the connection table. But the main usage you have understood well. It is the same case like users and permitions. Only in my case I do not want to have the packagins list in one field inside of the datatable of the products (the example you are showing is giving a list of permisions listed and separated by coma in one field). In my case I attach to the relation of product and packaging some more data - like quantity, weight, person that has defined the packaging and so on...
The application loosk like this:

One datatable to maintain data about the product itself:
products (id, name, description, ...)
Then one datatatable to maintain all possible packagings, or lets call it packaging types.
packagings (id, name, description, ...)
And in one view related to some concrete product I want to have one datatable where I can define different packagings possible for the product.

This is a standard relation many to many. I found a scheme on internet very similar to my scheme...
https://i.stack.imgur.com/oSlSc.png

From what I see the compound key does exactly what I need. Unfortunatelly not yet for PHP.

So based on your responses I shall add one unique ID for the relational table as well, which is not a standard I thing, but technically is possible to do.

Thank you both,

rf1234

It also works for PHP. Checked it earlier today. The link Allan sent is for .NET, but if you take a look at the same page for PHP you'll find it there too. @ my mobile right now, can't send you the link ...

allan

I was guessing .NET from the URLs. This is the corresponding PHP documentation.

Allan