Join to different database?

MickB

How can I join across to another databases?

I want to display the part_no, but the id is stored in this database (restricted_products table).

My Products table is in another database and contains the product details including id and part_no.

So, my restricted_products tables just contains:

part_no, linked_part_no
1,2
3,4
5,6

Both of these columns are ids but I want the Datatable to display part_nos.
The part_nos are stored in another db, in the Products table:

id, part_no
1, WH CP10

I don't need to edit the records, I have my own code to create and just need a delete button on the actual table.

Thanks,

Mick

 table: "#generic_table",

    idSrc: 'restricted_products.id',

    fields: [{

                    label: 'Product Code:',
                    name: 'part_no'},{
                    
                    label: 'Linked Product:',
                    name: 'linked_part_no'}
                    
                                        
                     ]

        });




        //setup the Datatable
        table = $('#generic_table').DataTable({ //took the var off to intentionally make it global

            "ajax": {
                "url": "/restricted_productsAjax",
                headers: {
                    'X-CSRF-TOKEN': '8zu0zDnxNrVBvbjifWUVgf0V7cqCq7h27hDpFsYv'
                },
                "type": "POST",

                "data": function(d){
                    d.table = "restricted_products";
                                    },

            },
            "columns": [
                {
                    data: null,

                    defaultContent: '',
                    className: 'select-checkbox',
                    orderable: false
                },

                
      { 'data': 'part_no'},
      { 'data': 'linked_part_no'},
      
    


            ],
            order: [1, 'asc'],
            dom: "Bfrtip",
            responsive: false,//true wont let me hide columns
            "scrollY": "600px",
            "scrollCollapse": true,
            
            "paging": false,
            select: {
                style: 'single',
                selector: 'td:first-child',
                blurable: true

            },

            buttons: [[
                    { extend: 'remove', editor: editor }    ]
                ],


            


        });

Tom (DataTables)

Hi

In order to join multiple tables you would just need to specify the database as well as the table name in Field::inst

Editor::inst( $db, 'db1.table1' )
    ->field(
        Field::inst( 'db1.table1.field1' ),
        Field::inst( 'db2.table1.field1 )
    )

Your left join would also have to reflect db1 and db2. You can read more using the documenation

Thanks

Tom