Forums - Thread Details
  • State Verified Answer
  • Replies 14 replies
  • Subscribers 321 subscribers
  • Views 2606 views
  • Users 0 members are here
Related

Simple SugarQuery using a custom field returns no results

Artis Plocins

I created a custom Integer field called ticket_id_c in the Cases module and I can clearly see the field having the value my code is looking for in the cases_cstm table, but I still get no results.

$sq = new SugarQuery();
$sq->select('id');
$sq->from(BeanFactory::newBean('Cases'));
$sq->where()->equals('ticket_id_c', (int) $ticket['id']);
$result = $sq->execute();
$count = count($result);
$GLOBALS['log']->fatal('Result count: ' . $count);
// Outputs 0 to the log - no results
$GLOBALS['log']->fatal('Results: ' . $result);
// Outputs "()" to the log

I thought maybe I need to join the cases_cstm table in the query, but I could not find enough information to do that without errors. But surely the Cases bean should include the custom table as well?

How can I correctly perform this Sugar Query?

  • 0

    Hi Artis Plocins 

    As far I can see your code seems OK.

    Add these lines before $sq->execute(); :

    $sql = $sq->compileSql();

    $GLOBALS['log']->fatal("sql:\n" . $sql);

    You will see the very query executed in your database. Make sure it is OK.

    Kind regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0 in reply to André Lopes

    This is the compiled query:

    SELECT  cases.id id FROM cases INNER JOIN (select tst.team_set_id from team_sets_teams tst INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id
                        AND team_memberships.user_id = ''
                        AND team_memberships.deleted=0 group by tst.team_set_id) cases_tf on cases_tf.team_set_id  = cases.team_set_id
    LEFT JOIN cases_cstm ON cases_cstm.id_c = cases.id  WHERE cases.deleted = 0 AND cases_cstm.ticket_id_c = 192

    What is cases_tf? There is no such table.

    But the cases_cstm.ticket_id_c definitely is 192 in the database and all cases have a deleted value of 0.

    Running the same query through PhpMyAdmin does not return any results too.

    If I take out the team tables join I get the right result in PhpMyAdmin with this query:

    SELECT  cases.id id FROM cases 
    LEFT JOIN cases_cstm ON cases_cstm.id_c = cases.id  WHERE cases.deleted = 0 AND cases_cstm.ticket_id_c = 192

    But obviously SugarQuery has to insert the team permissions in there.

    Could this have something to do with the SugarQuery being run on a REST endpoint that does not require authentication?

  • 0 in reply to Artis Plocins

    I found that it is possible to disable team security for SugarQuery like this to avoid the failing team table join. This change successfully returns results for me:

    $sugarQuery->from($bean, array('team_security' => false)); 

    I don't know whether this is the right way of doing it because it's about ignoring security.

  • 0 in reply to Artis Plocins

    Somehow SugarQuery didn't identify the authenticated user. How did you run this query? Inside SugarCRM interface?, From inside an endpoint?

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0 in reply to Artis Plocins

    This explains why the team join were not properly populated. Without authentication SugarCRM can not reach the user which is important for team join.

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0 in reply to Artis Plocins

    Hi Artis Plocins,

    If you really need to run SugarQuery without authentication, you can pass 'team_security' as false and it will not include team membership in query. Here you go:

    $sq->from(BeanFactory::newBean("Tasks"), array('team_security' => false));

    Regards.

    Hats

  • 0 in reply to Hatim Alam

    Hi Artis Plocins

                 Have you found the solutin to your question

    Regards

    Sidhu

  • 0 in reply to sidhu sidhu

    Artis Plocins

    What are you trying to achieve on the overall solution?

    You mentioned you built an endpoint without authentication that looks like it might output data from your Sugar system, aren't you concerned that someone could find that url, and retrieve information from your system? 

    If this is an integration between two systems (eg: a website that needs to show the case?), why wouldn't you use the authentication to gain the OAuth token and subsequently pass the OAuth token to the GET API for the Cases module? You wouldn't even need to create a custom API for that, but just leverage the filter API.

    There are really really rare cases where you would leverage an unauthenticated endpoint. Same applies from overcoming Team security.

    Authentication and authorisation are in place to make sure you authenticate as a specific user and then the system will allow the correct ACL available to the specific user (being that Team visibility or Roles actions).

    I do not know and do not have visibility to your full use case, but from what I see and know, something does not feel right...

    Hope this gives you some hints to think about

    --

    Enrico Simonetti

    Sugar veteran (from 2007)

    www.naonis.tech


    Feel free to reach out for consulting regarding:

    • API Integration and Automation Services
    • Sugar Architecture
    • Sugar Performance Optimisation
    • Sugar Consulting, Best Practices and Technical Training
    • AWS and Sugar Technical Help
    • CTO-as-a-service
    • Solutions-as-a-service
    • and more!

    All active SugarCRM certifications

    Actively working remotely with customers based in APAC and in the United States

  • 0 in reply to Enrico Simonetti

    This is an integration between a customer support ticket system and SugarCRM.

    None of the Authentication and SSO options are applicable to SugarCRM, to my knowledge

    I would prefer to authenticate, but I didn't see a way here. Please do tell me if authentication is still possible in such a case.

    This system sends data to my endpoint when something happens in it, but it can only send once per event, so I cannot make it authenticate with SugarCRM before sending the data. That's why my endpoint is without authentication.