Sugarquery - Dynamic Select Statement Failing

Hi team.

I am trying to build  a dynamic SugarQuery, the scenario is that I have an array of "fields" I want to use to create a dynamic select statement. For simplicity the $selectGenerator array below contains name and id.

Fullscreen
1
2
3
4
5
6
7
8
9
10
$sugarQuery = new SugarQuery();
$sugarQuery->from(BeanFactory::newBean($filename),array('team_security' => false));
foreach($selectGenerator as $field)
{
$field = "'" . trim($field) . "'";
print "select(array($field))" . "</br>";
$sugarQuery->select(array($field));
}
$sugarQuery->select(array('billing_address_street'));
$result = $sugarQuery->execute();
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

The above query only returns the "billing_address_street" field and not name and ID as expected.

If I manually updated the array to NOT use the variable and instead by hard coded (the exact same string) it works.

Fullscreen
1
2
3
4
5
6
7
8
9
10
$sugarQuery = new SugarQuery();
$sugarQuery->from(BeanFactory::newBean($filename),array('team_security' => false));
foreach($selectGenerator as $field)
{
$field = "'" . trim($field) . "'";
print "select(array($field))" . "</br>";
$sugarQuery->select(array('id'));
}
$sugarQuery->select(array('billing_address_street'));
$result = $sugarQuery->execute();
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

I've tried without the force quotes around name, and also without them, and it seems to always fail whenever I use a variable rather than hard coded. 

For instance I checked to ensure it was generating the same and my code generates the correct format which would be:

select(array('name'))
select(array('id'))

Has anyone else gotten around this or is this a bug?

Thanks,

Daniel

  • I faced issues like that in the past and, actually, trying to do exactly what you are trying to accomplish.

    The answer is: you need to handle a select object before entering such a foreach.

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    $select = $sugarQuery->select(['billing_address_street']);
    foreach($selectGenerator as $field)
    {
    $field = "'" . trim($field) . "'";
    $select->field(array($field));
    }
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    Regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • Hi Andre.

    Thanks for the response, using the code above it seems to still only select "billing_address_street", which is odd, any ideas?

  • If I change it to

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    $select = $sugarQuery->select(['billing_address_street']);
    foreach($selectGenerator as $field)
    {
    $field = "'" . trim($field) . "'";
    $select->field(array('name'));
    }
    $result = $sugarQuery->execute();
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    Again this works, but using $field as 'name', it doesn't work when 'name' is used as a variable ($field).

  • Yes, the method field only accept a string. This is the issue in my previous answer.

    André Lopes
    Lampada Global
    Skype: andre.lampada