Extending Mjoin

Extending Mjoin

crush123crush123 Posts: 381Questions: 118Answers: 17

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: 42,672Questions: 1Answers: 5,498 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: 381Questions: 118Answers: 17

    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: 42,672Questions: 1Answers: 5,498 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: 381Questions: 118Answers: 17

    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: 25Questions: 8Answers: 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: 42,672Questions: 1Answers: 5,498 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: 25Questions: 8Answers: 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:

Sign In or Register to comment.