javascriptsugarcrmsuitecrm

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:

    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