What is best approach to save "quantities" for an checkbox?

hanssonrickard

So.
I have an site where we should be able to set an order quantity for each language an product is to be produced.
As of today i have an separate table with the languages available for the specific customer (different languages avaialble for different customers) and they shows up perfectly fine as checkboxes for the logged in user in the Editor window.

But how can i also include an extra "quantity field" for each checkbox and save that info in an good way using Datables Editor?

I was first thinking about mjoin, but i am unsure of how i can save both the connection between the product and the language (checkbox) AND a quantity value?

Any suggestions?

This is how my php code looks today

/*
 * Editor server script for DB table products
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "DataTables.php" );

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


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'products', 'productID' )
    ->fields(
        Field::inst( 'products.productID' ),
        Field::inst( 'products.productName' )
            ->set( false ),
        Field::inst( 'products.productGroupID' )
            ->options( Options::inst()
                ->table( 'productgroups' )
                ->value( 'productGroupID' )
                ->label( 'name' )
                ->where( function ($q) {
                    $q->where('productgroups.customerID', $_COOKIE['customer'], '=' );
                })
            ),
        Field::inst( 'productgroups.name' ),
        Field::inst( 'products.customerID' )
            ->options( Options::inst()
                ->table( 'customers' )
                ->value( 'customerID' )
                ->label( 'name' )
                ->where( function ($q) {
                    $q->where('customers.customerID', $_COOKIE['customer'], '=' );
                })
            ),
        Field::inst( 'customers.name' ),
        Field::inst( 'products.campaignID' )
            ->options( Options::inst()
                ->table( 'campaigns' )
                ->value( 'campaignID' )
                ->label( 'name' )
                ->where( function ($q) {
                    $q->where('campaigns.customerID', $_COOKIE['customer'], '=' );
                })
            ),
        Field::inst( 'campaigns.name' ),
        Field::inst( 'products.countryID' )
            ->options( Options::inst()
                ->table( 'countries' )
                ->value( 'countryID' )
                ->label( 'countryLong' )
                ->where( function ($q) {
                    $q->where('countries.customerID', $_COOKIE['customer'], '=' );
                })
            ),
        Field::inst( 'countries.countryLong' ),
        Field::inst( 'products.processID' )
            ->options( Options::inst()
                ->table( 'printprocess' )
                ->value( 'processID' )
                ->label( 'processName' )
            ),
        Field::inst( 'printprocess.processName' ),
        Field::inst( 'products.ready' )
    )
    ->leftJoin( 'productgroups', 'productgroups.productGroupID', '=', 'products.productGroupID' )
    ->leftJoin( 'customers', 'customers.customerID', '=', 'products.customerID' )
    ->leftJoin( 'campaigns', 'campaigns.campaignID', '=', 'products.campaignID' )
    ->leftJoin( 'countries', 'countries.countryID', '=', 'products.countryID' )
    ->leftJoin( 'printprocess', 'printprocess.processID', '=', 'products.processID' )
    ->where('products.customerID', $_COOKIE['customer'], '=' )
    ->process( $_POST )
    ->json();

This is how my javascript looks like (only editor part):

        editor = new $.fn.dataTable.Editor( {
            ajax: 'vendor/editor/php/table.incoming.php',
            table: '#incoming',
            template: '#customForm',
            fields: [
                {
                    "label" : "ProduktID",
                    "name" : "products.productID",
                },
                {
                    "label": "Produktgrupp:",
                    "name": "products.productGroupID",
                    "type": "select"
                },
                {
                    "label": "Kund:",
                    "name": "products.customerID",
                    "type": "select"
                },
                {
                    "label": "Kampanj:",
                    "name": "products.campaignID",
                    "type": "select"
                },
                {
                    "label": "Tryckprocess:",
                    "name": "products.processID",
                    "type": "select"
                },
                {
                    "label": "Land/Språk:",
                    "name": "products.countryID",
                    "type": "checkbox",
                    "separator" : "|"
                },
                {
                    "label": "Klar:",
                    "name": "products.ready",
                    "type": "checkbox",
                    "options": [
                        { "label": "", "value": 1 }
                    ],
                    "separator": '',
                    "unselectedValue": 0
                }
            ]
        } );

Don´t mind the separator set to |, it is just an test, could be standard comma or anything else.

Can i save the values as some kind of array in my table instead, with the checkbox value and the amount value as array?

allan

Yeah - that's a tricky one with Editor at the moment. For all it can do MJoin, it doesn't really handle the case of setting multiple fields at the same time in the joined table. It is possible with a custom field plug-in which will accept multiple values, but that's a bit of a pain to set up.

Is it really an MJoin that you need here, or would a left join do? That would make it possible since the libraries can write to the joined tables for multiple fields. Can you show me a screenshot of your page so I can visually see how it fits together?

Thanks,
Allan

hanssonrickard

Ok, i have attached an screenshot of the view. As of now i only have checkboxes for the languages that is available for the selected customer (each customer can have their own language list, so the list is not fixed), that one was easy to fix using joins and also easy to save them as an array or as an string with selected separator.

If it helps, there is not any need for an checkbox to enable or disable the visibility of the amounts/aquantity fields (text fields), it could be an text-field only (no checbox needed as it comlpicates it) for each country instead, where the label for the field should be the language name (countryLong) and the value should be the quantity entered by user.

But if i do not need any checkboxes, i still need some way to dynamically create one text field per language available for this customer/product to show up.

My mysql tables looks like this for the moment:

TABLENAME customer:
| customerID | name |

TABLENAME countries:
| countryID | countryLong | countryShort | customerID |

TABLENAME products:
| productID | productName | campaignID | customerID | countryID |

and in my first scenario, where i thought i could use an MJoin i had also made up an link table that looks like this

TABLENAME productQualtity:
| id | productID | countryID | quantity |

hanssonrickard

Allan, have you possible had the chance to have a look at the info i gave above?

Wondering, if using an left join, i guess i should still have/use the tables i have setup, including the ProductQuantity table?

Would i need some kind of custom field also to show each country as an text field, or how am i supposed to show each country available for the particular customer as an text field, which values are then saved to the left joined table?

allan

Sorry for the delay in getting back to you. I'll read it in detail and do so shortly.

Allan

allan

Are you able to mock the screen up a little to show how you want it to look with the quantities fields? I think I get it and understand, but I'd just like to check.

There isn't a built in way to do what you are looking for (multiple values for a joined table), but it is something that might be possible with a custom field type plug-in (I've not actually tried it, so I'm not certain - I'd need to experiment a little!).

Regards,
Allan

hanssonrickard

Sure.
Here is an mockup screenshot, you see the added list of countries in the bottom of the screenshot (Norge = Norway, Sverige = Sweden).
Basically, each country defined for the selected customer should show up as an text field, with the longName (or shortname) as label, being able to fill in an value (quantity). The list is dynamic and should be fetched from the"countries" table:

TABLENAME countries:

| countryID | countryLong | countryShort | customerID |
---------------------------------------------------------------
| 1              | Sverige         | sv-SE            | 3                  |
| 2              | Sverige         | sv-SE            | 1                  |
| 3              | Norge           | no-BK            | 3                  |

In sample data above, there are two countries filled in for customer with id "3".
Swedish and Norwegian, so these two should show up for this customer in particular.

I guess the values we fill in for each country would then be had to be saved into the
table named "productQuantity, here is mockup of how that data should be (i guess):

TABLENAME productQualtity:

| id | productID | countryID | quantity |
-------------------------------------------
|1   |245            | 1               |25           |
|2   |245            | 3               |25           |
|3   |1034          | 2               |0             |
|4   |1132          | 9               |254         |

Default value, if user does not fill in any should be "0".

Does this make sense?
Do you see any other way of doing it in?
Would you like some sample data from the "customers" and "products" tables as well?

allan

Super - thanks. I'm fairly certain that a custom field type plug-in could be used for this. It would accept and return an array of objects - e.g.:

[
  { countryID: 1, quantity: 25 },
  { countryID: 2, quantity: 0 },
  ...
]

The one issue I see is that the way an MJoin works in Editor's server-side libraries is that it will delete the rows and then add them again - it doesn't just do an update for the link table (as that could be a fairly complex diff). The upshot is that the ids would change, but Editor itself doesn't use the id's of the link table anyway.

Regards,
Allan

hanssonrickard

Ok.

I don´t think the id´s are of use anyway for me, even if calling on this table from other pages that does not involve an editor instance. The important info should be the productID, countryID and the value (quantity).

As of now i am fairly sure this is above my head/knowledge.
How to proceed?

Allan, would it be possible to buy support tokens from you to get some help with how to do such an custom field plug-in and how to write the mjoin so that it works?

Or is this an ticket that is of interest for so many more people that it might be better to write an solution in this thread?

allan

How urgently do you need it?

Allan

hanssonrickard

I would say quite urgently as i have been trying to find an solution for it for some while now and this one is "blocking" the rest of the pages to be developed because we need to know how the data will be presented/edited and saved in the DB.

hanssonrickard

Could an solution be to use left/right joins to get an products list where each product is listed several times, one per available language?

So that same product appear three times if there are three languages available.
When opening editor we would then see the quantitiy field from the productquantity table.

Of course it would mean we would have to make some kind of left + right jon where we join the "products" with "countries" and "productquantity" tables so that we get multiple lines for each product, each line corresponding an country and having the quantity field.

It is not as nice, but maybe an possible workaround? Would it be possible to have such an join setup where the data that is edited is saved to both products and productquantity tables?

allan

I'll look into this this evening and let you know what I find. I'm not sure about the workaround as it would probably require two Editor instances, and you can't show two at the same time at the moment. MJoin is probably the best option atm, but I'll let you know.

Allan

allan

I think I have got a way to do this. It won't work using the Mjoin with a link table, but we could use what is effectively the link table as a direct access Mjoin. The only downside I can see is that we might need a little extra code to get the list of options to be shown in the field - but that would be a single query.

Sorry to stall again - but let me get back to you on this when I've put some code together for an actual working case.

Allan

hanssonrickard

Sounds promising.
Thanks so far.

allan

Okay - here we go! On the server-side use an Mjoin like this (its possibly you might need to change the pkey name for the product table, or even the product table name):

    ->join(
        Mjoin::inst( 'productQualtity' )
            ->link( 'product.id', 'productQualtity.productID' )
            ->fields(
                Field::inst( 'productID' ),
                Field::inst( 'countryID' ),
                Field::inst( 'quantity' )
            )
    )

On the client-side the Editor configuration would look like this:

            {
                label: 'Quantities:',
                name: 'productQualtity[]', // Note the `[]` that's important to tell Editor its an array!
                type: 'mQuantities',
                metaProp: 'countryID', // matches the field name for the country
                inputProp: 'quantity' // matches the field name for the quantity
            }

You'll need to tell the plug-in information about the countries - I've added a options method to allow you to pass that information in - e.g.:

    editor.field( 'productQualtity[]' ).options( [
        { countryID: 1, label: 'UK' },
        { countryID: 2, label: 'France' },
        { countryID: 3, label: 'Germany' },
        { countryID: 4, label: 'Ireland' }
    ] );

An Ajax call to get that data would probably be best.

You'll also need to include this Editor plug-in code on the page:

(function ($, DataTable) {

var mQuantities = {
    create: function ( conf ) {
        conf._container = $('<div/>');

        return conf._container;
    },
 
    get: function ( conf ) {
        return $('input', conf._container).map( function () {
            var item = {};
            item[ conf.metaProp ] = $(this).data('prop');
            item[ conf.inputProp ] = $(this).val();

            return item;
        } ).toArray();
    },
 
    set: function ( conf, val ) {
        mQuantities._draw( conf );

        $.each( val, function ( i, v ) {
            console.log( v );
            $('input[data-prop='+v[conf.metaProp]+']', conf._container)
                .val( v[conf.inputProp] );
        } );
    },

    options: function ( conf, opts ) {
        conf._opts = opts;
    },

    _draw: function ( conf ) {
        var container = conf._container.empty();

        $.each( conf._opts, function ( i, opt ) {
            container.append(
                '<div class="mQuantities-group">'+
                    '<span>'+opt.label+'</span>'+
                    '<input data-prop="'+opt[conf.metaProp]+'" value="0" />'+
                '</div>'
            );
        } );
    }
};

if ( ! DataTable.ext.editorFields ) {
    DataTable.ext.editorFields = {};
}
DataTable.ext.editorFields.mQuantities = mQuantities;
 
})(jQuery, jQuery.fn.dataTable);

You'll probably want to style it a bit to make the alignment nice!

This is how it renders in the test I've just been using:

Phew

hanssonrickard

Thanks.
I will test it on monday.

hanssonrickard

I have done those edits now and i am also doing an ajax call to get the correct countries list for the editor to show up and all looks fine on the client side.

But when trying to save that info from editor i get an server side error saying:

Undefined variable: key in /var/www/html/campaigntool/vendor/editor/php/Database/Query.php on line 661

I guess i need an "Field::inst" also that is connected to the Mjoined table?
I am unsure how that "Field::inst()" should look.

allan

Can you show me your PHP code please?

Allan

hanssonrickard

I removed the old "Field::inst()" i had becuase i didn´t know what to use with the mjoin.

Here is how it looks right now with your new stuff in it:

<?php

/*
 * Editor server script for DB table products
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "DataTables.php" );

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


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'products', 'productID' )
    ->fields(
        Field::inst( 'products.productID' ),
        Field::inst( 'products.productName' )
            ->set( false ),
        Field::inst( 'products.productGroupID' )
            ->options( Options::inst()
                ->table( 'productgroups' )
                ->value( 'productGroupID' )
                ->label( 'name' )
                ->where( function ($q) {
                    $q->where('productgroups.customerID', $_COOKIE['customer'], '=' );
                })
            ),
        Field::inst( 'productgroups.name' ),
        Field::inst( 'products.customerID' )
            ->options( Options::inst()
                ->table( 'customers' )
                ->value( 'customerID' )
                ->label( 'name' )
                ->where( function ($q) {
                    $q->where('customers.customerID', $_COOKIE['customer'], '=' );
                })
            ),
        Field::inst( 'customers.name' ),
        Field::inst( 'products.campaignID' )
            ->options( Options::inst()
                ->table( 'campaigns' )
                ->value( 'campaignID' )
                ->label( 'name' )
                ->where( function ($q) {
                    $q->where('campaigns.customerID', $_COOKIE['customer'], '=' );
                })
            ),
        Field::inst( 'campaigns.name' ),
        Field::inst( 'products.processID' )
            ->options( Options::inst()
                ->table( 'printprocess' )
                ->value( 'processID' )
                ->label( 'processName' )
            ),
        Field::inst( 'printprocess.processName' ),
        Field::inst( 'products.doublePage' ),
        Field::inst( 'products.ready' )
    )
    ->leftJoin( 'productgroups', 'productgroups.productGroupID', '=', 'products.productGroupID' )
    ->leftJoin( 'customers', 'customers.customerID', '=', 'products.customerID' )
    ->leftJoin( 'campaigns', 'campaigns.campaignID', '=', 'products.campaignID' )
    ->leftJoin( 'printprocess', 'printprocess.processID', '=', 'products.processID' )
    ->join(
        Mjoin::inst( 'productquantity' )
            ->link( 'products.productID', 'productquantity.productID' )
            ->fields(
                Field::inst( 'productID' ),
                Field::inst( 'countryID' ),
                Field::inst( 'quantity' )
            )
    )
    ->where('products.customerID', $_COOKIE['customer'], '=' )
    ->where('products.ready', 0)
    ->process( $_POST )
    ->json();

This is how the field::inst looked like before the changes, when i had the countries showing up as checkboxes only (and no mjoin table at all, just asving cheked countries in a column for the product):

Field::inst( 'products.countryID' )
            ->options( Options::inst()
                ->table( 'countries' )
                ->value( 'countryID' )
                ->label( 'countryLong' )
                ->where( function ($q) {
                    $q->where('countries.customerID', $_COOKIE['customer'], '=' );
                })
            ),
Field::inst( 'countries.countryLong' ),

Here is the client side stuff, with fields definition and Ajax to get the countries:

editor = new $.fn.dataTable.Editor( {
            ajax: 'vendor/editor/php/table.incoming.php',
            table: '#incoming',
            template: '#customForm',
            fields: [
                {
                    label : "ProduktID",
                    name : "products.productID"
                },
                {
                    label : "",
                    name : "products.productName",
                    type: "display"
                },
                {
                    label: "Produktgrupp:",
                    name: "products.productGroupID",
                    type: "select"
                },
                {
                    label: "Kund:",
                    name: "products.customerID",
                    type: "select"
                },
                {
                    label: "Kampanj:",
                    name: "products.campaignID",
                    type: "select"
                },
                {
                    label: "Tryckprocess:",
                    name: "products.processID",
                    type: "select"
                },
                {
                    label: 'Antal:',
                    name: 'productquantity[]', // Note the `[]` that's important to tell Editor its an array!
                    type: 'mQuantities',
                    metaProp: 'countryID', // matches the field name for the country
                    inputProp: 'quantity' // matches the field name for the quantity
                },
                {
                    label: "Dubbelsidig:",
                    name: "products.doublePage",
                    type: "checkbox",
                    options: [
                        { "label": "", "value": 1 }
                    ],
                    separator: '',
                    unselectedValue: 0
                },
                {
                    label: "Färdigtaggad:",
                    name: "products.ready",
                    type: "checkbox",
                    options: [
                        { "label": "", "value": 1 }
                    ],
                    separator: '',
                    unselectedValue: 0
                }
            ]
        } );
        
        $.ajax({
            type: 'POST',
            url: 'api/countries.php',
            dataType: 'json',
            success: function (countries) {
                console.log(countries);
                editor.field( 'productquantity[]' ).options(countries);
            }
        });

hanssonrickard

If i pre-populate my link table with some data, that data is properly shown for the product.
The only problem seems to be the Save function to save new values or updated values.

Attached is an screenshot of the sample data i manually filled in into the database and it shows up properly for the product. But i cannot save, then i get that error message in apache log from php.

hanssonrickard

This is the error if seen from inside developer tools in Chrome. If that helps anything?

<br />
<b>Notice</b>:  Undefined variable: key in <b>/var/www/html/campaigntool/vendor/editor/php/Database/Query.php</b> on line <b>661</b><br />
{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1","data":[],"ipOpts":[],"cancelled":[]}

hanssonrickard

And here is the post data when trying to edit, that gives error server-side:

action:edit
data[row_5578410][products][productID]:5578410
data[row_5578410][products][productName]:xyz.pdf
data[row_5578410][products][productGroupID]:1
data[row_5578410][products][customerID]:3
data[row_5578410][products][campaignID]:1
data[row_5578410][products][processID]:3
data[row_5578410][products][doublePage]:1
data[row_5578410][products][ready]:0
data[row_5578410][productquantity][0][countryID]:1
data[row_5578410][productquantity][0][quantity]:250
data[row_5578410][productquantity][1][countryID]:2
data[row_5578410][productquantity][1][quantity]:22
data[row_5578410][productquantity-many-count]:2

allan

Sorry - bug in the current release that I've got fixed already which I why I didn't see it. It will be in the next release.

Until then, in the Query.php file, jump to line 661 and replace with:

if ( is_callable($inOut) && is_object($inOut) ) {

Allan

hanssonrickard

Thanks.
Now it works as expected.
I have marked both your solution and the bugfix as correct answers.

allan

Excellent - good to hear that does the business. Thanks for letting me know.

Allan

hanssonrickard

A side question, is this saved info also possible to show in an column in the Table?

allan

Yes, the best way for doing that would be to use a renderer. That will let you write a little for loop to spin over the data and format it out as needed.

Allan

hanssonrickard

Thanks.
Will do that.
I have used renderar in other places.