Forums - Thread Details
  • State Verified Answer
  • Replies 14 replies
  • Subscribers 319 subscribers
  • Views 2651 views
  • Users 0 members are here
Forum Actions
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?

Parents
  • 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?

Reply
  • 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?

Children