Extending Mjoin

crush123

I have a number of one-to-many joins in my product database, for a site I am updating

For something like product groups, this approach works really well, i can assign a product to as many groups as I need.

I have followed the MJoin example and successfuly updated the datatable and editor on the page.

For product colours though, this doesn't quite work as I need

As an example, I have product colour options, so there is a tblproduct table (parent), tblproductcolour table (link) and refcolour (child) table

To complicate things a little, the tblproductcolour link table also contains a price field, as each product / colour combination can potentially have a unique price.

if, when editing a product, i add or remove a product colour from my options, not only is the row removed, but all of the existing prices are set to zero

is it possible to add the price field in my link table in my mjoin ?

i have a test page at http://test.forthwebsolutions.com/hutch4dmin/products.php

allan

Could you show me the PHP code and SQL schema you are using for this please? The key thing with Mjoin is that it doesn't do clever merging of rows - it will delete rows and then insert new ones rather than doing some kind of diff. Thus any meta information (including, I suspect in this case the price field) will be lost.

One way around this is to introduce a link table. That link table would link between the products and colours, and have only two columns - the primary key from each of the host tables. Then the delete and add can happen without problems.

Allan

crush123

the php code to generate the ajax json (extract)

// Build our Editor instance and process the data coming from _POST
  $data = Editor::inst( $db, 'tblproducts', 'ProductID' )//table name and PKey(defaults to ID)
->field(
    Field::inst( 'tblproducts.ProductID' ),
    Field::inst( 'tblproducts.ProductSKU' ),
    Field::inst( 'tblproducts.ProductName' ),
    Field::inst( 'tblproducts.ProductPrice' ),
    Field::inst( 'tblproducts.ProductSize' )
            ......
        )
    ->join(
    Mjoin::inst( 'tblcategories' )
        ->link( 'tblproducts.ProductID', 'tblproductcategories.ProductID' )
        ->link( 'tblcategories.CategoryID', 'tblproductcategories.CategoryID' )
        ->order( 'tblcategories.CategoryName asc' )
        ->fields(
            Field::inst( 'CategoryID' )
                ->options( 'tblcategories', 'CategoryID', 'CategoryName' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'CategoryName' )
        )
    )
    ->join(
    Mjoin::inst( 'refcolour' )
        ->link( 'tblproducts.ProductID', 'tblproductcolour.ProductID' )
        ->link( 'refcolour.ColourID', 'tblproductcolour.ColourID' )
        ->order( 'refcolour.ColourDescription asc' )
        ->fields(
            Field::inst( 'ColourID' )
                ->options( 'refcolour', 'ColourID', 'ColourDescription' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'ColourDescription' )
        )
    )

the categories uses a link table tblproductcategories and works perfectly

CREATE TABLE `tblproductcategories` (
  `ProductCategoryID` int(11) NOT NULL,
  `CategoryID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

as it has a autoincrement PK and only 2 other fields linking productid and categoryid, exactly as outlined in your comment

the colours link table also has a price field, so the price column in the link table is not visible to the editor

CREATE TABLE `tblproductcolour` (
  `ProductColourID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `ColourID` int(11) NOT NULL,
  `Price` float(9,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

allan

Thanks (and sorry for the delay)!

The issue is that the link table (tblproductcolour) should not include anything other than the link information. (i.e. ProductID and ColourID in this case). Because of the way Editor's MJoin works it will destroy the row as it is editing a row (MJoin doesn't actually do an edit at all - it always does a delete then create).

I think for this to work with the Editor libraries you'd need to introduce a link table between the tblproductcolour and tblproducts tables I'm afraid. But it wouldn't make declaring a product / colour / price all that easy.

Let me think about this one a bit...

Allan

crush123

Thanks Allan,

As a workaround, I have added insert and delete queries on the ajax/json source pages which fire on postCreate() and postRemove() to populate/de-populate the 'link' tables when a product is added or removed

Not quite as neat a solution, but the user gets a satisfactory experience and the data integrity seems to be assured ;-)

rw152

+1

Running into a similar problem

My linker table has more than 2 columns, it has two additional columns which would also need to be updated during the delete/insert sequence (one of the columns also stores a userID to track who inserted the row)

allan

I'm afraid I don't have a solution for this yet. It would really need some kind of diff to be done on the link table, but I feel that would be fought with potential errors which might end up corrupting data.

It is something I'm keen to address, but I've not figured out how to do it nicely yet.

Allan

rw152

+1, this would be helpful for me as well. I plan to just use postCreate functions as well, though of course this is not ideal

hanssonrickard

Crush123, would it be ok for you to share your workaround?

crush123

ok, i will try.

in the scenario above, i wanted the option to add a colour/price option to my products (sheds), so each shed model/colour option could potentially have a different price

in the ajax data source, i no longer used the mjoin for this, instead i used server side events to update the related tables.

for example, on creating a new product, I had a checkbox option in my editor instance, to populate the product colours table with default values from an existing reference list, eg

 ->on('postCreate', function ($editor, $id, $values) {
    //use the checkboxes in editor to append the reference tables if selected
    if ($values['optaddcolours'] == 1 ){      
        addProductColours ( $editor->db(), $id );
    }
      })

the addProductColours function is like this

function addProductColours ($db, $id) {
    // retrieve all rows from the colours reference table and store in an array
    $refcolours = $db->sql( "SELECT refcolour.ColourID, refcolour.DefaultPrice FROM refcolour")
    ->fetchAll();
    //loop through the array - adding all colour references to the tblproductcolour table for this newly inserted product
    foreach ($refcolours as $key => $value) {
        $db->insert( 'tblproductcolour', array(
            'ProductID' => $id,
            'ColourID' => ($refcolours[$key]['ColourID']),
            'Price' => ($refcolours[$key]['DefaultPrice'])
            ) 
        );
    }
}

this then gives me (potentially) all of the colour options and default prices for this shed

I then use a separate page and editor to amend these default colour prices as required.

Conversely, when a product is deleted, i call another function to remove the colours and prices from the linked table

->on('postRemove', function ($editor, $id) {

    removeProductColours ( $editor->db(), $id );

} )

function removeProductColours ( $db, $id ) {
    // delete all rows from the tblproductcolour table when this product is deleted
    $db->delete( 'tblproductcolour', array(
        'ProductID' => $id
    ) );
}

Hope this helps