Implementing many-many relationships by hand - Help

[Kalendrinn]

Posted this in the Help forum but no responses yet. I'm working on getting a many-many relationship going with the documents to other modules...starting with accounts. Here's where I'm at:

Ok, so I have almost gotten documents related to accounts. The one thing I am not sure about so far is how to get the system to read the relationship metadata file. Here's what I did for that part:

1) I created a metadata file 'documents_accountsMetaData.php' in /metadata which has the following code:

$dictionary["Documents_accounts"] = array (
'table' => 'documents_accounts',
'fields' =>
array (
0 =>
array (
'name' => 'id',
'type' => 'varchar',
'len' => 36,
),
1 =>
array (
'name' => 'deleted',
'type' => 'bool',
'len' => '1',
'default' => '0',
'required' => true,
),
2 =>
array (
'name' => 'document_id',
'type' => 'varchar',
'len' => 36,
),
3 =>
array (
'name' => 'account_id',
'type' => 'varchar',
'len' => 36,
),
),
4 =>
array (
'name' => 'date_modified',
'type' => 'datetime',
),
'indices' =>
array (
0 =>
array (
'name' => 'PRIMARY',
'type' => 'primary',
'fields' =>
array (
0 => 'id',
),
),
1 =>
array (
'name' => 'idx_doc_acct_doc',
'type' => 'index',
'fields' =>
array (
0 => 'document_id',
),
),
2 =>
array (
'name' => 'idx_doc_acct_acct',
'type' => 'index',
'fields' =>
array (
0 => 'accounts_id',
),
),
3 =>
array (
'name' => 'documents_accounts_alt',
'type' => 'index',
'fields' =>
array (
0 => 'document_id',
1 => 'accounts_id',
),
),
),
'relationships' =>
array (
'Documents_accounts' =>
array (
'lhs_module' => 'Documents',
'lhs_table' => 'documents',
'lhs_key' => 'id',
'rhs_module' => 'Accounts',
'rhs_table' => 'accounts',
'rhs_key' => 'id',
'relationship_type' => 'many-to-many',
'join_table' => 'documents_accounts',
'join_key_lhs' => 'document_id',
'join_key_rhs' => 'account_id',
),
),
);


2) I created a file 'Documents.php' in /custom/Extension/application/Ext/TableDictionary which has the following code:

include_once('metadata/documents_accountsMetaData.php');

The problem I can see is the the Documents.php file is not being read when rebuilding the extensions and so the metadata file isn't being read. So what happens is that all documents show up under each and every account's Documents subpanel and vice versa. There was a line in the Documents.php file above that said it was an autogenerated file but I am still not sure where that gets autogenerated...or the tabledictionary.ext.php that it generates gets autogenerated. I was obviously wrong in my first guess above. Any help would be GREATLY appreciated.

Other than that I have gotten the subpanels to show up and everything. It ended up that I decided to do a many-to-many relationship and forego the relate-to field in the documents edit and detail views. That way a document could be associated with multiple accounts (ie there's a project that encompasses multiple acocunts but they all had to sign the same contract...).

[Kalendrinn]

So one thing I have figured out is that if I install the basic module I created to get the information and files I needed for doing the relationship stuff for the doucments module, it reads the file in /custom/Extensions/application/TableDictionary. Why it doesn't do it when rebuilding the extensions, rebuilding the relationships or clearing the vardef cache is beyond me. Is this a bug that I've found??

[Kalendrinn]

Sugar team member please reply.

[Kalendrinn]

I have attached the files I currently have. The panels display in each module. The data shows. The only problem is the documents_accountsMetaData.php in /metadata is not being included/read so the subpanels show all data from the other module, not just the related data. I need to know what I am missing to get the Documents.php in /custom/Extension/application/TableDictionary read so it includes the metadata.

I have tried every conceivable approach I can think of but I know there is something missing. Doing any of the rebuilding operations or clearing the vardefs cache doesn't work either. It rebuilds the extensions but the metadata file still does not get read.

HELP!!

[Kalendrinn]

Seriously, I'm looking for help here from anyone. As soon as I can figure out what's going wrong and fix it, I can finish this one and plow through the others I was looking at hooking with the documents module.

Please please please would a Sugar Team member or someone knowledgable in v5 help me out? I have provided the files and the situation, it should be a cake-walk for you guys from here.

[Kalendrinn]

Kudos to Ken Brill on his creating a many-to-many relationship tutorial on the developer site. However, it didn't explain my issue.

I DID manage to figure it out though. There was no row inserted into the relationships table and it has to be there to exist. It was working when I had the new module i created installed because it generically read the directory. So instead of trying to insert the row by hand, I created a module out of the new relationship and did a manifest file. That is where the real kudos to Ken come in. I've never done one and his was simple and to the point, with direct relation to what I was doing.

Now the issue is getting the install to go properly. It installs fine, but there's certain weirdness going on. I tried doing individual language files and the install failed saying:
Warning: dir(G:\webs\dev\sugar5/cache/upload//upgrades/temp\298.tmp/modules/Accounts/en_us.lang.php) [function.dir]: failed to open dir: Result too large in G:\webs\dev\sugar5\include\dir_inc.php on line 56

Fatal error: Call to a member function read() on a non-object in G:\webs\dev\sugar5\include\dir_inc.php on line 57


So I tried doing it like the module I created before and put a single language file in the language/application folder which installs it to custom/Extension/application/Ext/Language. It installs but the language file doesn't work...I still get the LBL_ACCOUNTS_SUBPANEL_TITLE text rather than the Accounts text associated with it.

The other thing I noticed is that the vardef and layoutdef files end up with the module name and not the name of the files they started as...which I don't think will work once I have 5+ relationships set up.

Any ideas on how to get those two parts fixed and working properly?

[Kalendrinn]

I decided to keep this thread going in case anyone is looking and has had or is having the same issues. I am almost finished creating relationships between the documents module and all the other major modules. I am trying to get it related to Tasks, but I am not sure that I'm doing it right. I have started by doing it like the rest of the modules, but it gives me the error in the Tasks module:

Error retrieving Task list: Query Failed: SELECT documents.id , documents.document_name , documents.is_template , documents.template_type , documents.created_by , 'documents' panel_name FROM documents where documents.deleted=0 ORDER BY document_id asc LIMIT 0,10::MySQL error 1054:

Not sure where the "'documents' panel_name" stuff comes from...I think that's why it's not working. Any ideas?

[Kalendrinn]

-snip-

And why am I the only one that has been posting to this thread? Is no one interested in how to do this or willing to help?

[Kalendrinn]

So here's where I'm at, I got all of the relationships installed and no errors, etc.

Problems:

1) Documents subpanel does not show under Campaigns, but Campaigns subpanel shows under Documents.


On this I really do need help guys. I made sure the code and metadata was correct for it. If I can get it working I will post everything I have for download so anyone else can install it and/or modify what I have to suit their needs. Someone please respond.

---addition

I have noticed that the Campaigns list of subpanels will change depending on what type of campaign it is. Is there someplace I need to add the Documents subpanel? If so, is it upgrade safe?

[Superman]

Hello

Could you post here latest stable release?
I will try to help you w/ problems regarding Campaings subpanels