One-to-Many (Multiple joins)

ZakInterDev

Hi guys

So I've got 4 tables which need joining. Products, Archived_Products, Archived_Recipe and Archived_Ingredients.

1 Product can have multiple Archived_Products, 1 Archived Product can have Multiple Archived_Recipes and an Archived_Recipe can have multiple Archived_Ingredients.

Currently I have only Products which have an archive showing in a table, once the Product is clicked a child row is shown. Displaying a list of Archived_Products (Date Archived, and some other stats).

However I need to get the Archived_Recipe details and sum up the total cost amount for a recipe to a specific Archived_Product. This sum will be shown in the child row as well, added to the other stats.

I'm struggling on the join methods that datatables provides, to access this data. My code is as follows:

Editor::inst( $db, 'Products', 'Products.Product_ID' )
    ->field( 
        Field::inst( 'Products.Product_ID')->set(false),
        Field::inst( 'Products.Cost_Price' )->set(false),
        Field::inst( 'Products.Selling_Price' )->set(false),
        Field::inst( 'Products.Product_Code' )->set(false),
        Field::inst( 'Product_Category.Product_Category_Desc' )->set(false),
        Field::inst( 'Product_Description.Product_Desc' )->set(false),
        Field::inst( 'Pack_Size.Pack_Size' )->set(false),
        Field::inst( 'Pack_Size.UOM' )->set(false)
    )
    ->leftJoin( 'Pack_Size', 'Pack_Size.Pack_ID', '=', 'Products.Pack_ID' )
    ->leftJoin( 'Product_Description', 'Product_Description.Product_Description_ID', '=', 'Products.Product_Description_ID' )
    ->leftJoin( 'Product_Category', 'Product_Category.Product_Category_ID', '=', 'Product_Description.Product_Category_ID' )
    ->join(
            Join::inst( 'Archive_Products', 'array' )->set(false)
            ->join( 'Product_ID', 'Product_ID' )
            ->field(
               Field::inst( 'Archive_Products.Archive_Product_ID AS A_Archive_ID' )->set(false),
               Field::inst( 'Archive_Products.Cost_Price AS A_Cost_Price' )->set(false),
               Field::inst( 'Archive_Products.Selling_Price AS A_Selling_Price' )->set(false),
               Field::inst( 'Archive_Products.Date_Archived AS A_Date_Archived' )->set(false)
                    ->getFormatter( function ( $val, $data, $opts ) {
                        return date( 'd/m/Y - H:i', strtotime( $val ) );                               
                        })
                    )
            )
    ->where($key = 'Products.Factory_ID', $value = $someValue, $op = '=')
    ->where($key = 'Products.Has_Archive', $value = $anotherValue, $op = '=')
    ->process($_POST)
    ->json();

Any thoughts? Thanks :)

ManuelWenner

Look good so far. Do you have any problems? Does "->set(false)" work in your case? I tried it the same way but in my case it's reseting the values from "Archive_Products"

ZakInterDev

No problems with that code I posted, I just couldn't figure out how to then join Archive_Products to the Archive_Recipe & Archive_Ingredients tables. So yeah set(false) worked for me, but I saw your post and see you fixed your problem :)