Nested readonly `leftJoin` in `Join`

RagnarGrootKoerkamp

Just like a lot of others, I had the need of a leftJoin on a joined table. It turns out this is quite easy to do when both the joined table and the nested leftjoined table are readonly.

Implementation

All we have to do is copy the relevant leftJoin code form Editor.php to Join.php:
- the _leftJoin[] array
- the leftJoin($table, $field1, $operator, $field2) function to add a left join
- the private _perform_left_join($query) function
- Now, we need to append the join to the query statement in the public data function. The way to do this is to put

$this->_perform_left_join(stmt);

just before the call to $res = $stmt->exec();.

Usage

Now you can do something like

$editor = Editor::inst( $db, 'A', 'A.id')
$editor->Join(
    MJoin('B')
    ->link('B.id', 'A.b_id')
    ->leftJoin('C', 'C.id', '=', 'B.c_id')
    ->fields(
           Field::inst('B.id')->set(Field::SET_NONE),
           Field::inst('C.some_nice_property')->set(Field::SET_NONE)
     )
     ->set(Field::SET_NONE)
);

Make sure to use an alias for C if C is also leftjoined to A directly.

@allan, this might be a nice feature for future Editor versions as well, because I think this is quite useful, even without support for editing them.

allan

Agreed. This is something I'd like to see in future versions of Editor as well.

Thanks for posting your workaround for now!

Allan

Restful Web Services

Hi RagnarGrootKoerkamp. I am trying to do exactly what you describe above but I must be making a mistake as I cannot get it to work. Is there any chance you could share your Join.php file please?

I get "DataTables warning: table id=product - Table selected fields (i.e. '{table}.{column}') in Join must have a name alias which does not contain a period ('.'). Use name('---') to set a name for the field"

I have experienced this error before and never found a way to solve it. Replacing the period for --- doesn't work, if that is what it is suggesting?

    ->join(
        Mjoin::inst( 'product' )
            ->set( false )
            ->name( 'product_variant' )
            ->aliasParentTable( 'product_variant' )
            ->link( 'product.parent_id', 'product_variant.product_id' ) 
            ->leftJoin('product_stock', 'product_stock.product_id', '=', 'product_variant.product_id')
            ->fields(      
                Field::inst( 'product_stock.stock_level' )
                    ->set( false ),                                       
                Field::inst( 'sku' )
                    ->set( false ),            
                Field::inst( 'cost_price' )
                    ->set( false )
                    ->getFormatter(function ($val, $data, $field) {
                        $val = ($val / 100);                    
                        return number_format($val, 2, '.', '');             
                    }),                            
                Field::inst( 'retail_price' )
                    ->set( false )
                    ->getFormatter(function ($val, $data, $field) {
                        $val = ($val / 100);                    
                        return number_format($val, 2, '.', '');             
                    })                               
            )
            ->where( 'product.branch', '1', '=' )              
            ->where( 'product.status_id', '2', '=' )                                                        
    )  

Thanks

RagnarGrootKoerkamp

I hope this is still relevant:

This is probably due to using column names that are not prefixed with their table name in the Field::inst('column_name') calls. Instead, it should be Field::inst('table_name.column_name').

Restful Web Services

Hi,

Sorry for slow reply. I still cant get this to work and the issue is the prefix. Even without the nested leftjoin if I add a prefix to the field within my Mjoin instance it doesnt work.

This works fine,

    ->join(
        Mjoin::inst( 'product_feature' )
            ->set( false )
            ->link( 'product_feature.product_id', 'product.product_id' )             
            ->fields(                    
                    Field::inst( 'feature' )
                        ->set( false )          
            )
            ->where( 'product_feature.branch', '1', '=' )
            ->where( 'product_feature.status_id', '2', '=' )             
    )  

This does not work,

    ->join(
        Mjoin::inst( 'product_feature' )
            ->set( false )
            ->link( 'product_feature.product_id', 'product.product_id' )             
            ->fields(                    
                    Field::inst( 'product_feature.feature' )
                        ->set( false )          
            )
            ->where( 'product_feature.branch', '1', '=' )
            ->where( 'product_feature.status_id', '2', '=' )             
    )     

I see the same error "DataTables warning: table id=product - Table selected fields (i.e. '{table}.{column}') in Join must have a name alias which does not contain a period ('.'). Use name('---') to set a name for the field".

I don't understand because that is exactly how I would initiate a field outside of an MJoin?

Thanks

Restful Web Services

Got it. I don't know why it didn't occur to me before but obviously I can just use as. This works fine.

->join(
    Mjoin::inst( 'product_feature' )
        ->set( false )
        ->link( 'product_feature.product_id', 'product.product_id' )            
        ->fields(                   
                Field::inst( 'product_feature.feature as product_feature' )
                    ->set( false )         
        )
        ->where( 'product_feature.branch', '1', '=' )
        ->where( 'product_feature.status_id', '2', '=' )            
)   

Thanks, I now have the leftJoin working within the Mjoin, this is a really great addition and hopefully this will become part of the core at somepoint because it is super useful.

RagnarGrootKoerkamp

Nice!

I think I never had this problem because I usually create fields by calling

Field::inst('table.column', 'column')

which gives it the name column as far as Editor is concerned.