Combining Mjoin and leftJoin

Restful Web Services

Is it possible to combine Mjoin and leftJoin? I get the property_id below but I want to display the property_name.

        ->join(
            Mjoin::inst( 'cms_module_system_tenancies' )
               ->set( false )
               ->link( 'cms_module_system_users_tenants.Id', 'cms_module_system_tenancies.tenant_id' )
               ->fields(
                    Field::inst( 'property_id' )
                        ->set( false ),
                    Field::inst( 'tenancy_link_id' )
                        ->set( false )
            )
        )
        ->leftJoin( 'cms_module_system_properties', 'cms_module_system_properties.Id', '=', 'property_id' )
    ->process( $_POST )
    ->json();

I receive an error message unknown column "property_id" in on clause.

Thanks

allan

What table is the property_name number?

You can use left join and mjoin at the same time, but they aren't really aware of each other.

Allan

Restful Web Services

Hi Allan,

The property_name is coming from the table cms_module_system_properties.

I store the property_id in the cms_module_system_users_tenants table, but for the user I want to show the human readable name which is only stored in the properties table.

Chris

Restful Web Services

A user can be in more than one property at once which is why I need to use Mjoin.

allan

I don't think you need to use a left join for this - just use an Mjoin with two links (i.e. tell it you have a link table.

Important point: Be careful with link tables if you intend to edit them. Editor will delete delete the rows in the link table and then add them again - so you can't really have any information other than the link between the two tables.

Allan

Restful Web Services

Hi Allan,

Thanks for your help. I am not sure my structure is correct for this query. I will try and explain.

I have 3 tables:
cms_module_system_tenancies (the effective Link Table)
cms_module_system_users_tenants and
cms_module_system_users_properties

The table cms_module_system_tenancies contains the columns tenant_id and property_id.

I use this link ->link( 'cms_module_system_users_tenants.Id', 'cms_module_system_tenancies.tenant_id' ) to get information from the tenants table.

This works fine.

However, if I add ->link( 'cms_module_system_properties.Id', 'cms_module_system_tenancies.property_id' ) it does not work.

I get the error:

"Unknown column 'cms_module_system_properties.tenant_id' in 'on clause'.

I haven't even specified that column so I am abit lost. Does my objective seem possible?

allan

It sounds like you want to use:

$editor->join( Mjoin::inst( 'cms_module_system_users_tenants' )
  ->link( 'cms_module_system_users_tenants.id', 'cms_module_system_tenancies. tenant_id' )
  ->link( 'cms_module_system_users_properties.id', 'cms_module_system_tenancies. property_id' )
  ->fields(
    Field::inst( 'id' ),
    Field::inst( 'name' ) // assuming there is a name field in the users_tenants table
  )
);

This is assuming that you are reading cms_module_system_users_properties as the master Editor table (i.e. showing a list of properties)?

Allan

Restful Web Services

Hi Allan,

Thanks again. I still can not get it to work correctly. Here is my full server side script. I am in the table cms_module_system_users_tenants. I want to link to the cms_module_system_properties table via the link table cms_module_system_tenancies. I want to get the columns property_name (which only exists in the properties table) and property_manager (which only exists in the properties table).

Editor::inst( $db, "cms_module_system_users_tenants", "Id" )
    ->fields(
        Field::inst( "cms_module_system_users_tenants.Id" )
            ->set( false ),
        Field::inst( "cms_module_system_users_tenants.create_date" )
            ->set( false )
            ->validator( "Validate::notEmpty" )
            ->getFormatter( function ($val, $data, $field) {
                        $val = date("d/m/Y", strtotime($val));
                        return $val;}), 
        Field::inst( "cms_module_system_users_tenants.modified_date" )
            ->set( false )
            ->validator( "Validate::notEmpty" )
            ->getFormatter( function ($val, $data, $field) {
                        $val = date("d/m/Y", strtotime($val));
                        return $val;}), 
        Field::inst( "cms_module_system_users_tenants.category" )
            ->validator( "Validate::notEmpty" ),    
        Field::inst( "cms_module_system_users_tenants.status" )
            ->validator( "Validate::notEmpty" ),
        Field::inst( "cms_module_system_users_tenants.tenancy_availability" )
            ->validator( "Validate::notEmpty" ),
        Field::inst( "cms_module_system_users_tenants.property" )
            ->getFormatter( function ($val, $data, $field) {
                if ($val == "")return "-";else if ($val <> "")return $val;}),   
        Field::inst( "cms_module_system_users_tenants.forename" )
            ->getFormatter( function ($val, $data, $field) {
                if ($val == "")return "-";else if ($val <> "")return $val;}),                       
        Field::inst( "cms_module_system_users_tenants.surname" )
            ->getFormatter( function ($val, $data, $field) {
                if ($val == "")return "-";else if ($val <> "")return $val;}),
        Field::inst( "cms_module_system_users_tenants.username" )
            ->set( false )
    )
        ->join(
            Mjoin::inst( 'cms_module_system_properties' )
               ->set( false )
               ->link( 'cms_module_system_users_tenants.Id', 'cms_module_system_tenancies.tenant_id' )
               ->link( 'cms_module_system_users_properties.Id', 'cms_module_system_tenancies.property_id' )
               ->fields(
                    Field::inst( Id)
                        ->set( false ),
                    Field::inst( 'property_name' )
                        ->set( false ),
                    Field::inst( 'property_manager' )
                        ->set( false ),
            )
        )
    ->process( $_POST )
    ->json();   

Thanks again for your time.

allan

As far as I can see, that should work. Do you get any errors reported? What actually happens when you run that code? Can you show me the JSON that it returns?

Thanks,
Allan

Restful Web Services

Great, it does indeed work now. A silly error on my part whereby I forgot to enclose Id in colons. It should be,

Field::inst( 'Id' ) ->set( false ),

Thanks for your help.

Restful Web Services

Just one last issue! I normally display a - character in my table if the value is empty. I sometimes do this client side like so,

"columnDefs" : [{
                "targets" : [58],
                "render" : function(data, type, row) {
                        if (row.cms_module_system_users_tenants.application_form_complete == '') {
                        return '-';
                        }else{
                        return 'row.cms_module_system_users_tenants.application_form_complete';
                        }
                        }                                                                                                                                                                                         
            }]

But, it doesn't seem to work in this situation. Is there an easy way to display a default value when no value present in the case of using the Mjoin function?

Thanks

allan

if ( ! row.cms_module_system_users_tenants || ! row.cms_module_system_users_tenants.length ) {
   return '-';
}
...

Allan

Restful Web Services

Hi Allan,

Thanks for the reply, that worked perfectly.

However, sorry, I have another last issue! I have the Mjoin function working nicely across various tables. In some cases though where there are a number of records the order of the data is somewhat random.

Is it possible to order the data being outputted in anyway?

For example if in the below example I wanted to order by position how would I go about doing that?

    ->join(
        Mjoin::inst( 'cms_module_system_prices' )
               ->set( false )
               ->link( 'cms_module_system_activities.Id', 'cms_module_system_prices.record_id' )
               ->fields(
                    Field::inst( 'position' )
                        ->set( false ),
                    Field::inst( 'name' )
                        ->set( false ),
                    Field::inst( 'quantity' )
                        ->set( false ),
                    Field::inst( 'unit_price' )
                        ->set( false )
                        ->getFormatter( function ($val, $data, $field) {
                            return number_format((float)$val, 2, '.', '');
                        })
        )
    )

https://www.datatables.net/forums/discussion/21444/join-tables-one-to-many-join-example-ordering-access-options-alphabetically

I have found the following forum post which suggests it is possible but I don't fully understand the code example and have failed to implement it into my code successfully.

Am I on the right lines, will this example work for me?

Thanks

Chris

allan

Hi Chris,

The link you provided is specifically about the options list in the Editor form. Is that what you are interested in setting, or is it the order for how they are displayed in the DataTable?

If the latter (which I suspect is the case from what you have written above) then you would need to do the ordering in the columns.render method. There is currently no option in the PHP to order that data.

Allan

Restful Web Services

Hi Allan,

Thanks again for your response, I have to say the level of support you provide is exceptionally good.

You are correct, it is the order the data is displayed that I am concerned with. I previously manipulated my data using the columns.render option like so,

                "render": function ( data, type, full ) {
                    if ( ! full.cms_module_system_prices || ! full.cms_module_system_prices.length ) {
                    return '-';
                    }else{
                    return $.map( data, function ( d, i ) {
                        return d.name + ' = €'+ d.unit_price + ' for ' + d.quantity;
                    }).join( '<br>' );
                    }                 
                }

This outputs the data in the 'prices' column like so,

• 2 Pax = €190.00 for 2
• 1 Pax = €140.00 for 1
• 3 Pax = €220.00 for 3
• 4 Pax = €240.00 for 4

However, as you can probably see it would be more logical if it were in order.

I have updated the render function to use this sort function data.sort(function(a, b){return a.quantity > b.quantity? 1: -1;}); and it seems to work, although I don't know if this is the most appropriate solution?

                "render": function ( data, type, full ) {
                    if ( ! full.cms_module_system_prices || ! full.cms_module_system_prices.length ) {
                    return '-';
                    }else{
                    $data = data.sort(function(a, b){return a.quantity > b.quantity? 1: -1;});
                    return $.map( data, function ( d, i ) {
                        return d.name + ' = €'+ d.unit_price + ' for ' + d.quantity;
                    }).join( '<br>' );
                    }                 
                }

This outputs the data in the 'prices' column like so,

• 1 Pax = €140.00 for 1
• 2 Pax = €190.00 for 2
• 3 Pax = €220.00 for 3
• 4 Pax = €240.00 for 4

Should that work in all cases?

Thanks

Chris

allan

Hi Chris,

Thanks :-). And yes, your solution looks spot on to me. As long as data contains objects which have valid numbers for the quantity property, then yes, that will work nicely.

Allan