JOIN subquery in suiteCRM Error 40, ("name":"Access Denied","number":40,"description":"You do not have access")

Using a suiteCRM query that connects with sugarCRM i need to retrieve all the opportunities that have an specific user and client id, being the client id (account_id) not queryable (at least directly).

So this means that i cannot use a syntax like this one:

 session: await CRMAuth.getSession(),
  modules: CRM_MODULES.OPPORTUNITY,
  query: `assigned_user_id = '${uid}' AND account_id = '${client_id}'`,
  order_by: 'date_modified DESC',
  offset: 0,
  select_fields: [
    'id',
    'name',
  ],
  link_name_to_fields_array: [],
  max_results: 100,
  deleted: false,

but instead i should have something like this as the query:

  session: await CRMAuth.getSession(),
  modules: CRM_MODULES.OPPORTUNITY,
  query: `opportunities.assigned_user_id = '${uid}' AND opportunities.id IN (
          SELECT opportunity_id FROM accounts_opportunities r
          JOIN accounts a ON (r.account_id = a.id)
          WHERE a.id = '${account_id}'
          AND r.deleted=0 and a.deleted=0)`,
  order_by: 'date_modified DESC',
  offset: 0,
  select_fields: [
     'id',
     'name',
  ],
  link_name_to_fields_array: [],

max_results: 100, deleted: false,

i tried differents variations of this like uppercases in modules/tables names, and adding AS before "r", "a" (and even i replaced them with the full name). I also tried simpler queries but i still got the same error.

Also looking at similar problems i can assure that is not a session problem, but a syntax one.

Solution

My 2 mistakes were:

Relating the modules upside-down
Using the get_entry_list method instead of get_relationships (this method ask for different parameters)

get_relationships

So i ended up with this structure

let response = await CRM(CRM_METHODS.GET_RELATIONSHIPS, {
  session: await CRMAuth.getSession(),
  module_name: CRM_MODULES.ACCOUNTS,
  module_id: '${client_id}',
  link_field_name: `opportunities`,
  related_module_query:`opportunities.assigned_user_id = '${uid}'`,
  related_fields: [*the fields i want*],
  related_module_link_name_to_fields_array:[],
  deleted: 0,
});

BE CAREFULL: there is not an id for the module (Opportunities, Accounts, etc), the "module_id" field is the id of the RECORD that you are working with, in my case the id of my client.

This bring me all the opportunities created by a specific user for a specific client