We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 37108
    • 80 Posts
    The following query inappropriately adds ticks in the first where clause to all the middle array items. The first and last are correct. Any ideas why this would happen? Is it a bug? Note: This query is a subquery that subsequently gets inserted into another query's select.
    $q = $modx->newQuery('modTemplateVarResource');
    $q->select(array(
    	'address' => "GROUP_CONCAT(DISTINCT CONCAT_WS(':',TemplateVar.name,modTemplateVarResource.value) ORDER BY TemplateVar.name ASC SEPARATOR ',')"
    ));
    $q->where(array(
    	'TemplateVar.name:IN'=>array('item-address','item-address-2','item-state','item-city','item-zip','item-url','item-email')
    ));
    $q->where('`modTemplateVarResource`.`contentid` = `modResource`.`id`');
    $q->prepare();
    $sql = $q->toSQL();
    


    Note that if I change strategy on the where clause to the following it works. However, the above seems more elegant, probably quicker, and should work. I'm not a sql whiz by any means, but I'm not sure I see the reason why any value within IN(x) would ever be wrapped in backticks.
    $q->where(array(
    	array('TemplateVar.name' => 'item-address'),
    	array('TemplateVar.name' => 'item-address-2'),
    	array('TemplateVar.name' => 'item-state'),
    	array('TemplateVar.name' => 'item-city'),
    	array('TemplateVar.name' => 'item-zip'),
    	array('TemplateVar.name' => 'item-url'),
    	array('TemplateVar.name' => 'item-email')
    ),xPDOQuery::SQL_OR);
    
    [ed. note: smg6511v2 last edited this post 8 years, 2 months ago.]