Self join

Restful Web Services

I have a single table which contains records which are date specific, i.e. past, present and future based on today's date.

I use a where filter to ensure I only show the right records in the right table; i.e. past table, present table, etc.....

Should I, in theory, be able to do a self join in order to show a value in a column when a particular client has multiple records, i.e. if the client has a future and current record then the column would show, for example, the value 'Existing Client'?

I have attempted to add a left join like so,

->leftJoin( 'cms_module_system_tenancies as tenancy', 'cms_module_system_tenancies.Id', '=', 'tenancy.Id' )

This works but it seems that my original WHERE filters are still being used on the leftJoin, which makes sense, however it means that I can still only see 1 record instead of 2 in the column.

Do I need to use ONE-TO-MANY join and if so is that possible when self referencing a table?

Thanks

Chris

allan

You should be able to use leftJoin with an alias. Could you show me your full PHP code please?

Allan

Restful Web Services

Hi Allan,

It looks like this.

if(isset($_POST['category']) && $_POST['category'] != ''){
$category = $_POST["category"];
}else{
$category = 'active';
}
if(isset($_POST['status']) && $_POST['status'] == 'upcoming'){
$status_from = date("Y-m-d H:i");
$status_to = date("Y-m-d H:i");
$status_from_switch = '>=';
$status_to_switch = '>=';
}elseif(isset($_POST['status']) && $_POST['status'] == 'in-progress'){
$status_from = date("Y-m-d H:i");
$status_to = date("Y-m-d H:i");
$status_from_switch = '<=';
$status_to_switch = '>=';
}elseif(isset($_POST['status']) && $_POST['status'] == 'archive'){
$status_from = date("Y-m-d H:i");
$status_to = date("Y-m-d H:i");
$status_from_switch = '<=';
$status_to_switch = '<=';
}elseif(isset($_POST['status']) && $_POST['status'] == 'withdrawn'){
$status_from = date("1970-01-01 00:00");
$status_to = date("2050-01-01 00:00");
$status_from_switch = '>=';
$status_to_switch = '<=';
}

// Alias Editor classes so they are easy to use
    use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;
    
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, "cms_module_system_tenancies", "Id" )
    ->fields(
        Field::inst( "cms_module_system_tenancies.Id" )
            ->validator( "Validate::notEmpty" ),  
        Field::inst( "cms_module_system_tenancies.category" )
            ->validator( "Validate::notEmpty" ),
        Field::inst( "cms_module_system_tenancies.property_id" )
            ->validator( "Validate::notEmpty" ),
        Field::inst( "cms_module_system_tenancies.tenant_id" )
        ->getFormatter( function ($val, $data, $field) {
        if ($val == "null")return "";else if($val == "")return "";else if ($val <> "")return $val;}),
        Field::inst( "cms_module_system_tenancies.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_tenancies.create_date as timestamp" )
            ->set( false )
            ->getFormatter(function ($val, $data, $field) {                       
                                $val = strtotime($val);
                        return $val;
            }),
        Field::inst( "cms_module_system_tenancies.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_tenancies.tenancy_start_date" )
                        ->validator( 'Validate::dateFormat', array(
                                "empty" => true,
                                "format"  => 'd/m/Y',
                                "message" => "Please enter a date in the format dd/mm/yyyy"
                                ))
                        ->setFormatter( function ($val, $data, $field) {
                                $val = str_replace("/", "-", $val);
                        $val = date("Y-m-d", strtotime($val));
                        return $val;})
            ->getFormatter( function ($val, $data, $field) { 
                if ($val == '1970-01-01' || $val == '0000-00-00') 
                return "";
                        else if ($val <> '1970-01-01' && $val <> '0000-00-00')                        
                        $val = date("d/m/Y", strtotime($val));
                        return $val;}),                           
        Field::inst( "cms_module_system_tenancies.tenancy_end_date" )
                        ->validator( 'Validate::dateFormat', array(
                                "empty" => true,
                                "format"  => 'd/m/Y',
                                "message" => "Please enter a date in the format dd/mm/yyyy"
                                ))
                        ->setFormatter( function ($val, $data, $field) {
                                $val = str_replace("/", "-", $val);
                        $val = date("Y-m-d", strtotime($val));
                        return $val;})
            ->getFormatter( function ($val, $data, $field) { 
                if ($val == '1970-01-01' || $val == '0000-00-00') 
                return "";
                        else if ($val <> '1970-01-01' && $val <> '0000-00-00')                        
                        $val = date("d/m/Y", strtotime($val));
                        return $val;}), 
        Field::inst( "cms_module_system_tenancies.tenancy_link_id" )
            ->validator( "Validate::notEmpty" ),
        Field::inst( "tenancy.tenancy_link_id" )
            ->set( false )
    )
    ->where("cms_module_system_tenancies.tenancy_start_date", $status_from, "$status_from_switch")
    ->where("cms_module_system_tenancies.tenancy_end_date", $status_to, "$status_to_switch")
    ->where( "cms_module_system_tenancies.category", $category)
    ->leftJoin( 'cms_module_system_tenancies as tenancy', 'cms_module_system_tenancies.Id', '=', 'tenancy.Id' )
    ->process( $_POST )
    ->json();  

The field tenancy.tenancy_link_id should be a single value or multiple values if there is a current and future tenancy for that person.

Cheers

Chris

allan

Hi Chris,

The field tenancy.tenancy_link_id should be a single value or multiple values

With a left join it can only be a single value (or null). If you are expecting an array of values, you would have to use a one-to-many join (Mjoin).

It sounds like you need to modify the code to use Mjoin based on your description. For that you will probably want to use the aliasParentTable method in order to provide the alias.

Allan

Restful Web Services

Can I use a different WHERE in a left join? I don't need the specific value I just need to know if it exists or not. So when I am in the 'Future' table I just need to check the 'Current' data to see if the tenant_id is also present.

If Mjoin is the only option should I experience any problems when trying to reference the same table or should it be fine?

allan

Can I use a different WHERE in a left join? I don't need the specific value I just need to know if it exists or not.

No - this isn't how the left join is working if you could have multiple results. The result of a left join is a flat table. If you want to check if there are one or more joins to a record you will need to use Mjoin.

If Mjoin is the only option should I experience any problems when trying to reference the same table or should it be fine?

As I mentioned you should use aliasParentTable, but yes it is possible to self join.

Allan

Restful Web Services

Hi Allan,

Thanks for your help. I have a few Mjoin functions working in other tables but I cannot get this one to work when using aliasParentTable.

Could you please take a look at my code and see if you can tell my why I am recieving the datatables.net//tn/4 error "table id=cms_module_system_tenancies - Requested unknown parameter 'cms_module_system_tenancies.Id' for row 0."

        ->join(
            Mjoin::inst( 'cms_module_system_tenancies' )
               ->aliasParentTable( 'cms_module_system_tenancies_copy' )
               ->link( 'cms_module_system_tenancies.Id', 'cms_module_system_tenancies_copy.Id' )
               ->fields(
                Field::inst( 'tenancy_link_id' )
                    ->set( false )
                )
        )

Thanks

Chris

allan

Hi Chris,

Could you show me the JSON that is being returned by the server? I suspect, since you are using an Mjoin, you will need to use array syntax in the property. For example cms_module_system_tenancies_copy[].Id.

If you have a link to the page that would be very useful.

Allan

Restful Web Services

Hi Allan,

Yeah sure, it looks like this.

{
    "data": [
        {
            "DT_RowId": "row_709",
            "cms_module_system_tenancies": {
                "Id": "709",
                "category": "active",
                "property_id": "-",
                "tenant_id": "-",
                "create_date": "09\/05\/2016",
                "modified_date": "28\/06\/2016",
                "tenancy_start_date": "14\/06\/2016",
                "tenancy_end_date": "27\/06\/2017",
                "tenancy_link_id": "DONTDELETETWO",
                "rent_amount": "-",
                "rent_amount_other": "-",
                "rent_plan": "Termly",
                "rent_plan_other": "-",
                "rent_payee": "-",
                "utilities_allowance": "-",
                "deposit_total": "-",
                "deposit_insurance_deadline": "09\/08\/2016",
                "mydeposits_information": "",
                "deposit_1_amount": "-",
                "deposit_1_due_date": "09\/05\/2016",
                "deposit_1_method": "Cash",
                "deposit_1_paid": "10\/06\/2016",
                "deposit_2_amount": "-",
                "deposit_2_due_date": "24\/01\/2017",
                "deposit_2_method": "Standing order",
                "deposit_2_paid": "",
                "application_form_complete": "",
                "guarantor_form_sent": "10\/06\/2016",
                "guarantor_form_received": "10\/06\/2016",
                "standing_order_1_complete": "10\/06\/2016",
                "standing_order_1_sent_to_bank": "",
                "standing_order_2_complete": "10\/06\/2016",
                "standing_order_2_sent_to_bank": "",
                "tenancy_agreement_signed": "12\/06\/2016",
                "key_collection_date": "29\/06\/2016 00:00",
                "key_collection_task_id": "0",
                "rental_terms_accepted": "",
                "room": "",
                "inventory_date": "0000-00-00",
                "notes": "-"
            },
            "timestamp": 1462802681,
            "cms_module_system_properties": {
                "property_name": "-",
                "property_owner": "-",
                "property_manager": "-"
            },
            "cms_module_system_owners": {
                "forename": null,
                "surname": null
            },
            "cms_module_system_users_staff": {
                "forename": "-",
                "surname": "-"
            },
            "cms_module_system_users_tenants": {
                "forename": "Christopher",
                "surname": "Johnson",
                "username": "-",
                "mobile_phone": "-",
                "facebook_name": "-",
                "twitter_name": "-",
                "guarantor": "-",
                "guarantor_relationship": "-",
                "guarantor_address_line_1": "-",
                "guarantor_address_line_2": "-",
                "guarantor_city": "-",
                "guarantor_county": "-",
                "guarantor_post_code": "-",
                "guarantor_email": "-",
                "guarantor_phone": "-",
                "application_form_complete": "28\/06\/2016"
            },
            "cms_module_system_tasks": {
                "status": null,
                "due_date": "01\/01\/1970 00:00"
            },
            "cms_module_system_tenancies_copy": [
                {
                    "tenancy_link_id": "DONTDELETETWO"
                }
            ]
        }
    ],
    "options": [],
    "files": []
}

There should be two tenancy_link_id values in the cms_module_system_tenancies_copy data.

Cheers

Chris

allan

Try using:

cms_module_system_tenancies_copy[, ].tenancy_link_id

As you will be able to see from the above JSON, it is an array with an object, that has a single field:

            "cms_module_system_tenancies_copy": [
                {
                    "tenancy_link_id": "DONTDELETETWO"
                }
            ]

The single field corresponds to the single Field::inst( 'tenancy_link_id' ) in the Mjoin configuration.

Allan

Restful Web Services

Hi Allan,

If I add a value for ->name then it seems to work but I am getting duplicate values. So for example the below should just return DONTDELETEONE and DONTDELETETWO but I am getting them twice.

        ->join(
            Mjoin::inst( 'cms_module_system_tenancies' )
               ->name( 'cms_module_system_tenancies_copy' )
               ->aliasParentTable( 'cms_module_system_tenancies_copy' )
               ->link( 'cms_module_system_tenancies.tenant_id', 'cms_module_system_tenancies_copy.tenant_id' )
               ->fields(
                Field::inst( 'tenancy_link_id' )
                    ->set( false )
                )
        )

            "cms_module_system_tenancies_copy": [
                {
                    "tenancy_link_id": "DONTDELETETWO"
                }, {
                    "tenancy_link_id": "DONTDELETETWO"
                }, {
                    "tenancy_link_id": "DONTDELETEONE"
                }, {
                    "tenancy_link_id": "DONTDELETEONE"
                }
            ]

{
                "data": "cms_module_system_tenancies_copy", 
                "render": "[, ].tenancy_link_id",
                "createdCell" : function(td) {
                    $(td).addClass('grey pointer');
                }           
            }

Can you see anything wrong?

Thanks

Chris

allan

Hi Chris,

Can you show me the Javascript Editor code please? Are you attempting to edit both values in the Mjoin? If so, that I'm afraid won't work if they are spread over two fields.

It is possible to create a custom field type that would submit the two values in a single object if you need to be able to do that - it would also have to cope with the array of data, and this isn't something the built in field types can do.

Regards,
Allan

Restful Web Services

Hi Allan,

I don't want to edit the values. I just want to display the values. I have used ->set( false ), is that not sufficient?

Chris

allan

Oh I see - sorry. I had assumed that was after an edit.

I'm afraid that isn't an issue that I immediately recognise so I don't have an immediate solution. We'd need to do some debugging - in the Database/Drivers/Mysql/Query.php file you'll find a commented out line file_put_contents. Can you comment it back in and update the path to be suitable for your server.

What will output the SQL Editor uses to the target file. If you run those queries, what is the output? Does it show the duplicates? If you could send me a dump of your database and the full PHP code you are using for Editor I can debug it here.

Allan