Problem with adding join tables

ff_development

Hello everyone,

After buying Editor our company has been working with datatables for an internal application. The data we're trying to show is selected from three different tables, namely 'countries', 'brands' and 'modules'. The row in 'brands' contains a foreign key 'country_id' and the one to one relationship between brands and modules is as follows: 'modules' has a 'brand_id' foreign key.

We were able to show the data with simply using the ->leftJoin() method as it was mentioned in the docs. However, when we try to add a new 'brand' and with it a row within the table 'modules', it gives a foreign key constraint error. This is as logic dictates, but if any of you could give us some input as to how we might be able to solve this problem it would be very appreciated.

This week and the last we've been trying to use the ->join() method, but without success. The error we're currently struggling with is:

sError: "Table selected fields (i.e. '{table}.{column}') in Join must be read only. Use set(false) for the field to disable writing."

allan

Hi,

Thanks for the details of the problem you are having. Could you show me the PHP you are using for where you have Editor to setup to insert into both brand and modules?

Editor will insert / update on the primary table first, and then on the joined table. So if you require the information to be in the joined table first, you would indeed get a foreign key error. We could change that by reordering the data slightly, but you wouldn't have the foreign key to be able to insert that into the primary table.

The other option is to invert the tables, so your primary table becomes the modules table and the brand is the joined table.

Regards,
Allan

ff_development

Hi Alan,

Thanks for your quick answer!

I've done what you said but I'm getting this error now when I try to add a new one:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (analytics.modules, CONSTRAINT modules_brands_id_brands_brand_id_restriction FOREIGN KEY (brand_id) REFERENCES brands (brands_id))

tangerine

Allan said:

Could you show me the PHP you are using for where you have Editor to setup to insert into both brand and modules?

ff_development

Here is what I have right now:

```
<?php
error_reporting(0);
error_reporting(E_ALL);
ini_set('display_errors', 1);

include("../php/editor/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

Editor::inst( $db, 'modules', 'modules_id')
    ->fields(
        Field::inst( 'modules.cl_tops_m' ),
        Field::inst( 'modules.cl_tops_f' ),
        Field::inst( 'modules.cl_bottoms_m' ),
        Field::inst( 'modules.cl_bottoms_f' ),
        Field::inst( 'modules.jc_m' ),
        Field::inst( 'modules.jc_f' ),
        Field::inst( 'modules.lb_tops_m' ),
        Field::inst( 'modules.lb_tops_f' ),
        Field::inst( 'modules.lb_bottoms_m' ),
        Field::inst( 'modules.lb_bottoms_f' ),
        Field::inst( 'modules.sa_tops_m' ),
        Field::inst( 'modules.sa_tops_f' ),
        Field::inst( 'modules.sa_bottoms_m' ),
        Field::inst( 'modules.sa_bottoms_f' ),
        Field::inst( 'modules.fs_tops_m' ),
        Field::inst( 'modules.fs_tops_f' ),
        Field::inst( 'modules.fs_bottoms_m' ),
        Field::inst( 'modules.fs_bottoms_f' ),
        Field::inst( 'modules.k_tops_m' ),
        Field::inst( 'modules.k_tops_f' ),
        Field::inst( 'modules.k_bottoms_m' ),
        Field::inst( 'modules.k_bottoms_f' ),
        Field::inst( 'brands.name' ),
        Field::inst( 'brands.company_name' ),
        Field::inst( 'brands.city' ),
        Field::inst( 'brands.website_url' ),
        Field::inst( 'brands.webshop_url' ),
        Field::inst( 'brands.address' ),
        Field::inst( 'brands.zip_code' ),
        Field::inst( 'brands.phone_number' ),
        Field::inst( 'brands.email' ),
        Field::inst( 'brands.last_controlled' ),
        Field::inst( 'brands.country_id' )
            ->options( 'countries', 'id', 'name' ),
        Field::inst( 'countries.name' )
    )
    ->leftJoin( 'brands', 'brands.brands_id', '=', 'modules.brand_id' )
    ->leftJoin( 'countries', 'brands.country_id', '=', 'countries.id' )
    ->process($_POST)
    ->json();

<?php > ``` ?>

allan

Just so I understand fully, your form is submitting information for both the brands table and modules table at the same time and expecting a new row to be inserted into both. Is that correct?

Typically I would expect individual database tables to be edited individually. The issue with inserting into both in the same form submission is that the new row id from the first inserted row (modules) isn't available available to the second (brand). Normally that second value would be selected by the end user.

What you probably would have to do in this case is use the Join class rather than leftJoin as you suggested before, as it does have access to the inserted row id. The error you were seeing before suggests that there was a misconfiguration in the Field instances in the Join class (they shouldn't have a . in their names).

Allan

ff_development

Hi Allan,

Your first assumption is correct. We are using a single form to enter a new brand with its corresponding row from the 'modules' table to avoid one giant table.

Your second paragraph describes the problem we've been having perfectly. That's exactly what we're having trouble with.

Your third paragraph seems to be on-point as well. Because our tables all have the column 'id', we tried using the following: modules.id. From what I understand, though, that isn't a viable way of doing this?

What would you recommend we do, exactly? Maybe we just need an example that would be applicable to our situation just so we can wrap our heads around this :)

Thank you very much for your input so far.

allan

we tried using the following: modules.id. From what I understand, though, that isn't a viable way of doing this?

That is only on the client-side, but on the server-side in the Join you would just use Field::inst( 'id' ). The one-to-many documentation might be of some help.

If you have problems with that, could you show me the PHP you are using for it?

Interestingly this is the first use case I've found for using the object Join type...! The .NET libraries don't provide that option and it was included only in the PHP libraries because the leftJoin method wasn't introduced until v1.3.

One for me to think about moving forward!

Regards,
Allan

ff_development

Hi again Allan,

We've been trying to follow your instructions, but keep hitting the same error. After we changed our server-side code to the following:

Editor::inst( $db, 'modules' )
        ->field(
            Field::inst( 'modules.cl_tops_m' ),
            Field::inst( 'modules.cl_tops_f' ),
            Field::inst( 'modules.cl_bottoms_m' ),
            Field::inst( 'modules.cl_bottoms_f' ),
            Field::inst( 'modules.jc_m' ),
            Field::inst( 'modules.jc_f' ),
            Field::inst( 'modules.lb_tops_m' ),
            Field::inst( 'modules.lb_tops_f' ),
            Field::inst( 'modules.lb_bottoms_m' ),
            Field::inst( 'modules.lb_bottoms_f' ),
            Field::inst( 'modules.sa_tops_m' ),
            Field::inst( 'modules.sa_tops_f' ),
            Field::inst( 'modules.sa_bottoms_m' ),
            Field::inst( 'modules.sa_bottoms_f' ),
            Field::inst( 'modules.fs_tops_m' ),
            Field::inst( 'modules.fs_tops_f' ),
            Field::inst( 'modules.fs_bottoms_m' ),
            Field::inst( 'modules.fs_bottoms_f' ),
            Field::inst( 'modules.k_tops_m' ),
            Field::inst( 'modules.k_tops_f' ),
            Field::inst( 'modules.k_bottoms_m' ),
            Field::inst( 'modules.k_bottoms_f' )
        )
        ->join(
            Join::inst( 'brands', 'array' )
                ->join( 'brand_id', 'brands_id' )
                ->field(
                    Field::inst( 'name as brand_name' ),
                    Field::inst( 'company_name' ),
                    Field::inst( 'city' ),
                    Field::inst( 'website_url' ),
                    Field::inst( 'webshop_url' ),
                    Field::inst( 'address' ),
                    Field::inst( 'zip_code' ),
                    Field::inst( 'phone_number' ),
                    Field::inst( 'email' ),
                    Field::inst( 'last_controlled' ),
                    Field::inst( 'country_id' )
                )
        )
        ->process($_POST)
        ->json();

and our client-side datatables code to this:

columns: [
    { data: "brand_name" },            // 0
    { data: "brands.company_name" },    // 1
    { data: "countries.name" },         // 2
    { data: "brands.city" },            // 3
    { data: "brands.website_url" },     // 4
    { data: "brands.webshop_url" },     // 5
    { data: "brands.address" },         // 6
    { data: "brands.zip_code" },        // 7
    { data: "brands.phone_number" },    // 8
    { data: "brands.email" },           // 9
    { data: "brands.last_controlled" }, //10
    { data: "modules.cl_tops_m" },      //11
    { data: "modules.cl_tops_f" },      //12
    { data: "modules.cl_bottoms_m" },   //13
    { data: "modules.cl_bottoms_f" },   //14
    { data: "modules.jc_m" },           //15
    { data: "modules.jc_f" },           //16
    { data: "modules.lb_tops_m" },      //17
    { data: "modules.lb_tops_f" },      //18
    { data: "modules.lb_bottoms_m" },   //19
    { data: "modules.lb_bottoms_f" },   //20
    { data: "modules.sa_tops_m" },      //21
    { data: "modules.sa_tops_f" },      //22
    { data: "modules.sa_bottoms_m" },   //23
    { data: "modules.sa_bottoms_f" },   //24
    { data: "modules.fs_tops_m" },      //25
    { data: "modules.fs_tops_f" },      //26
    { data: "modules.fs_bottoms_m" },   //27
    { data: "modules.fs_bottoms_f" },   //28
    { data: "modules.k_tops_m" },       //29
    { data: "modules.k_tops_f" },       //30
    { data: "modules.k_bottoms_m" },    //31
    { data: "modules.k_bottoms_f" }     //32
],

We keep getting "Datatables warning: unknown field 'brand_name'." Do you have any insight that might be able to help with this further?

We're amazingly grateful to your help in this matter, I do not know if we could have gotten this far on our own.

Regards,

allan

Join::inst( 'brands', 'array' )

Are you expecting it to be one-to-one, or one-to-many?

• If one-to-one, change the array to object
• If one-to-many, change your DataTables initialisation - for example brands.city would be brands[].city

Also { data: "brand_name" }, should refer to the joined data - brands.brand_name for example, or just brands.name if you remove the alias (again, taking into account the above about one-to-one or one-to-many).

Allan

ff_development

Hi Allan! Thanks for your support so far.

Prepare, because this will be a long question, though probably also the final one before we start getting the results we want.

As of now, this is our php:

<?php
    error_reporting(0);
    error_reporting(E_ALL);
    ini_set('display_errors', 1);


    include("../php/editor/DataTables.php" );

    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Join,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate;

    Editor::inst( $db, 'modules')
        ->field(
            Field::inst( 'id'),
            Field::inst( 'cl_tops_m' ),
            Field::inst( 'cl_tops_f' ),
            Field::inst( 'cl_bottoms_m' ),
            Field::inst( 'cl_bottoms_f' ),
            Field::inst( 'jc_m' ),
            Field::inst( 'jc_f' ),
            Field::inst( 'lb_tops_m' ),
            Field::inst( 'lb_tops_f' ),
            Field::inst( 'lb_bottoms_m' ),
            Field::inst( 'lb_bottoms_f' ),
            Field::inst( 'sa_tops_m' ),
            Field::inst( 'sa_tops_f' ),
            Field::inst( 'sa_bottoms_m' ),
            Field::inst( 'sa_bottoms_f' ),
            Field::inst( 'fs_tops_m' ),
            Field::inst( 'fs_tops_f' ),
            Field::inst( 'fs_bottoms_m' ),
            Field::inst( 'fs_bottoms_f' ),
            Field::inst( 'k_tops_m' ),
            Field::inst( 'k_tops_f' ),
            Field::inst( 'k_bottoms_m' ),
            Field::inst( 'k_bottoms_f' )
        )
        
        ->join(

            Join::inst('brands', 'object')
                ->join( 'id', 'module_id')
                ->field(
                    Field::inst( 'name' ),
                    Field::inst( 'company_name' ),
                    Field::inst( 'city' ),
                    Field::inst( 'website_url' ),
                    Field::inst( 'webshop_url' ),
                    Field::inst( 'address' ),
                    Field::inst( 'zip_code' ),
                    Field::inst( 'phone_number' ),
                    Field::inst( 'email' ),
                    Field::inst( 'last_controlled' ),
                    Field::inst( 'country_id')
                        ->validator( 'Validate::required' )
                        ->options( 'countries', 'id', 'name' )
                )
        )
        ->process($_POST)
        ->json();

And this is our javascript:

columns: [
                { data: "id"    },                  //-1
                { data: "brands.name" },            // 0
                { data: "brands.company_name" },    // 1
                { data: "brands.country_id" },      // 2
                { data: "brands.city" },            // 3
                { data: "brands.website_url" },     // 4
                { data: "brands.webshop_url" },     // 5
                { data: "brands.address" },         // 6
                { data: "brands.zip_code" },        // 7
                { data: "brands.phone_number" },    // 8
                { data: "brands.email" },           // 9
                { data: "brands.last_controlled" }, //10
                { data: "cl_tops_m" },              //11
                { data: "cl_tops_f" },              //12
                { data: "cl_bottoms_m" },           //13
                { data: "cl_bottoms_f" },           //14
                { data: "jc_m" },                   //15
                { data: "jc_f" },                   //16
                { data: "lb_tops_m" },              //17
                { data: "lb_tops_f" },              //18
                { data: "lb_bottoms_m" },           //19
                { data: "lb_bottoms_f" },           //20
                { data: "sa_tops_m" },              //21
                { data: "sa_tops_f" },              //22
                { data: "sa_bottoms_m" },           //23
                { data: "sa_bottoms_f" },           //24
                { data: "fs_tops_m" },              //25
                { data: "fs_tops_f" },              //26
                { data: "fs_bottoms_m" },           //27
                { data: "fs_bottoms_f" },           //28
                { data: "k_tops_m" },               //29
                { data: "k_tops_f" },               //30
                { data: "k_bottoms_m" },            //31
                { data: "k_bottoms_f" },            //32
            ],

I'm glad to announce that adding and editing records works. But the bad news is that deleting them doesn't work. When we select a row and click "delete", only the row in the table 'brands' gets deleted and the row in 'modules' just stays there. This causes "empty rows" in datatables.

They look like this: http://i.imgur.com/IpjN8yG.jpg

Also, we haven't been able to get the actual country name in the "country" table. It just shows the ID.

I think once we figure out what to do here this thread could be very helpful to people in the future.

Thanks again! Hope to hear from you soon.

Edit: it should also be mentioned that the error we got before "Datatables warning: unknown field 'brand_name'." was gone after we simply started the columns object within the initialization of DataTables with an item from 'modules'. We then hide it with responsive datatables' "never" class and who knows, we might need to use the id some day :).

ff_development

Just a little update:

We got the delete to work. It turned out to be an error that came with version 1.4.1. I will mark this comment as the answer, but if you're reading this in the future and are actually looking for the answer to this question, you'll have no choice but to read the entire thread.

allan

Hi,

Gosh, sorry I lost track of this thread! Excellent to hear that the update to the latest version now has everything working the way it should!

Regards,
Allan