1

I'm trying to write a simple query in Sugar 7.2 that will join a table. This SQL is what I'm trying to create in the Sugar framework using SugarQuery(). 

SELECT * FROM keyname_table_a LEFT JOIN keyname_table_b

So what I've created is the following. It works just fine when I use the core modules, but if I switch it to custom modules that we've created, Sugar logs me out and brings me to a login prompt. 

$query = new SugarQuery();
$query->from(BeanFactory::getBean('keyname_table_a'));
$query->join('keyname_table_b');
$results = $query->execute();

return print_r($results, true);

The above logs me out and gives me the following error message in a popup message (similar to how app.alert.show works), and logs me out.

Error: Request failure, or, missing/invalid parameter. Please contact technical support

If I replace the "from" and "join" tables to be "Accounts" and "cases" respectively, then the query works just fine and pulls in the expected result. However, switching it to the custom modules that we have results in the above error and we are logged out instantly.

I have verified that the relationship exists and there is data linking the two together. It is a one to many relationship in Sugar. table_a is one, while table_b is many.

What is the proper way to join two custom modules with the SugarQuery() object?

darkhorizon
  • 530
  • 6
  • 12

2 Answers2

2

You need to pass link field name instead of table_name or relationship name

$query = new SugarQuery();
$query->from(BeanFactory::getBean('keyname_table_a'));
$query->join('link_name');
$results = $query->execute();
Muhammad Saad Shahid
  • 413
  • 4
  • 16
1

The join should be the relationship name. If you would like to join a table directly you can use joinTable().

In the Accounts example above, cases is the relationship name, not the table name.

Have a great day!

JimB
  • 11
  • 1
  • I went to table_a in Module Builder, clicked the module at the bottom, then went to "View Relationships" and tried to use the 'name' from the first column of the relationship. It still logs me out and gives me the same error. Any ideas why? Thanks for joinTable(), but it just turns up an empty array instead of the data from both modules. Thoughts? – darkhorizon Jun 17 '14 at 19:47
  • It is not relationship name, it's **link** field name. join works with link field – Muhammad Saad Shahid Feb 27 '15 at 07:38