Extending Mjoin

Extending Mjoin

crush123crush123 Posts: 417Questions: 126Answers: 18

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

  • crush123crush123 Posts: 417Questions: 126Answers: 18

    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;
    
  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    Answer ✓

    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

  • crush123crush123 Posts: 417Questions: 126Answers: 18

    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 ;-)

  • rw152rw152 Posts: 56Questions: 15Answers: 1

    +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)

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

  • rw152rw152 Posts: 56Questions: 15Answers: 1

    +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 :smile:

  • hanssonrickardhanssonrickard Posts: 24Questions: 2Answers: 0

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

  • crush123crush123 Posts: 417Questions: 126Answers: 18

    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

This discussion has been closed.