How can I link a link table without losing my IDs when the original link table is updated?

paravis

I've got an interesting issue here ... We are trying to link a link table, and when the original link table gets updated, all of the IDs are deleted and our secondary link table becomes completely worthless.

We are currently getting around this by using triggers, but that's not ideal.

Or, if this is not possible, how to create a link table that joins 3+ tables?

Thanks for any suggestions!

allan

Is this with MJoin? If so, then yes, a link table is expected to have only link information in it and rows will be removed and then re-added as required. The MJoin documentation notes this behaviour:

Additionally, please be aware that when Editor does an update for one-to-many linked data, it will delete all of the old references before inserting the new ones required by the edited data.

If you are using left joins that that should not be happening. Can you show me the code you are using please?

Allan

paravis

Well, we're using a mix of Mjoin and leftJoin ... I'm sure there is a better way to do this.

Here are the two PHP class instances that we are using:

Editor::inst($db,'domains','id')
    ->fields(
        Field::inst('domains.id'),
        Field::inst('domains.public_name')
    )
    ->join(
        Mjoin::inst('modules')
            ->link('domains.id','domain_modules.domain_id')
            ->link('modules.id','domain_modules.module_id')
            ->fields(
                Field::inst('id')->options('modules','id','display',function($q){$q->where('active',1);}),
                Field::inst('display'),
                Field::inst('active')->setFormatter(function($val,$data,$opts){return ! $val ? 0 : 1; })
            )
        )
    ->where('domains.enabled',1)
    ->process($_POST)
    ->json();

Editor::inst($db,'domain_modules','id')
    ->fields(
        Field::inst('modules.id'),
        Field::inst('domain_modules.id'),
        Field::inst('domain_modules.domain_id'),
        Field::inst('domain_modules.module_id'),
        Field::inst('module_all_users.all_users')->setFormatter(function($val,$data,$opts){return ! $val ? 0 : 1; }),
        Field::inst('domain_modules.all_users')->setFormatter(function($val,$data,$opts){return ! $val ? 0 : 1; }),
        Field::inst('modules.display')
    )
    ->join(
        Mjoin::inst('domain_users')
            ->link('domain_modules.id','module_acls.domain_module_id')
            ->link('domain_users.id','module_acls.user_id')
            ->fields(
                Field::inst('id')->options('domain_users','id','name',function($q){global $domain;$q->where('domain_id',$domain)->where('active',1)->where('bizio_user',1);}),
                Field::inst('name')
            )
        )
    ->leftJoin('module_all_users','module_all_users.domain_module_id','=','domain_modules.id')
    ->leftJoin('modules','modules.id','=','domain_modules.module_id')
    ->where('domain_modules.domain_id',$domain)
    ->where('modules.active',1)
    ->process($_POST)
    ->json();

I guess we are trying to build dependencies on dependencies that are undependable. :-) At this point, it really isn't a HUGE problem, as we have some database tricks and PHP processing to get around this, but I can see our tricks exponentially increasing the overhead as we actually start serious production.

Our guy just got on-site so I have to run. Please let me know what other information you need.

If there's a better approach (I'm sure there is), I'm all ears! Thanks again Allan for all your help.

allan

Thanks for the code. With the above code, the information in the module_acls table that results to the row(s) being edited would be deleted and then recreated on each edit.

Is that the table that you are seeing issues with? I would recommend having the link table as having two columns only (the link columns). Any meta data in other columns would be lost on edit.

edit I was looking primarily at the second code set - however, I see you also use domain_modules in the first code set as an Mjoin and that is going to cause issues since it does have meta information.

Do you actually need to edit the Mjoined data there? If not, then use ->set( false ) to disable any writing to the Mjoined table. However, if you do need to edit it and also retain the other information on the table, I'm afraid the Editor libraries just don't support that.

Allan

paravis

Yes, thank you Allan. It seems that I'll just stick with what I've got for now using the DB triggers and pre-processing.

It actually works half decent, and even will delete/update data in "linked link" tables. I'm just worried to see how much resource overhead it starts to take up as the database grows. But these tables are not frequently updated, so I hope it does not become a problem.

All is good. Thanks for the info!

allan

Clever solution using triggered :-). Good to hear it is working for you.

Allan