AnsweredAssumed Answered

I need to put in a subquery in searchfields.php 2 different parameters of vardefs.php in where clause

Question asked by Guillermo Regairaz on Jul 11, 2014
Hi! my name is Guillermo Regairaz, I'm currently working with SugarCRM 6.2.1 community ed. and I need some support as I'm not finding the solution in Internet.

I already added custom search fields in the contacts module, but for some reason when I put data in Sigla_Actividad field and anio_actividad Field, the results are not the same if I generate just one query with both conditions in the same where clause.

Is it possible to pass two different parameters (sigla_actividad and anio_actividad) to only one subquery?


SearchDefs.php:


  'sigla_actividad' => 
      array (
        'type' => 'name',
        'default' => true,
        'studio' => 'visible',
        'label' => 'Sigla Actividad',
        'width' => '10%',
        'name' => 'sigla_actividad',
      ),
      'anio_actividad' => 
      array (
        'type' => 'name',
        'default' => true,
        'studio' => 'visible',
        'label' => 'Anio Actividad',
        'width' => '10%',
        'name' => 'anio_actividad',


 SearchFields.php:

'sigla_actividad' => 
  array (
    'query_type' => 'default',
    'operator' => 'subquery',
    'subquery' => 'SELECT distinct(c.id) FROM contacts AS c, udesa_acizadas_contacts_c AS act_rel_contact, 
   udesa_actividadesrealizadas AS act_rel, udesa_activesrealizadas_c AS act_rel_act,udesa_actividades AS act
   WHERE c.id = act_rel_contact.udesa_af012acts_ida AND act_rel_contact.deleted = 0 
   AND  act_rel_contact.udesa_a8df0adas_idb =  act_rel.id AND 
   act_rel.deleted = 0 AND act_rel_act.udesa_acti2d4clizadas_idb = act_rel.id 
   AND act_rel_act.deleted = 0  AND 
   act_rel_act.udesa_acti07cfvidades_ida = act.id AND act.deleted = 0 
   AND act.sigla_actividad  LIKE  ',
    'db_field' => 
    array ('id',),),


'anio_actividad' => 
  array (
    'query_type' => 'default',
    'operator' => 'subquery',
    'subquery' => 'SELECT distinct(c.id) FROM contacts AS c, udesa_acizadas_contacts_c AS act_rel_contact, 
   udesa_actividadesrealizadas AS act_rel, udesa_activesrealizadas_c AS act_rel_act,udesa_actividades AS act
   WHERE c.id = act_rel_contact.udesa_af012acts_ida AND act_rel_contact.deleted = 0 
   AND  act_rel_contact.udesa_a8df0adas_idb =  act_rel.id AND 
   act_rel.deleted = 0 AND act_rel_act.udesa_acti2d4clizadas_idb = act_rel.id 
   AND act_rel_act.deleted = 0  AND 
   act_rel_act.udesa_acti07cfvidades_ida = act.id AND act.deleted = 0 
   AND act.anio = ',
    'db_field' => 
    array ('id',),), 

But, the results are not correct if I complete both fields.
I need to execute a query like this:

    'subquery' => 'SELECT distinct(c.id) FROM contacts AS c, udesa_acizadas_contacts_c AS act_rel_contact, 
udesa_actividadesrealizadas AS act_rel, udesa_activesrealizadas_c AS act_rel_act,udesa_actividades AS act
WHERE c.id = act_rel_contact.udesa_af012acts_ida AND act_rel_contact.deleted = 0 
AND  act_rel_contact.udesa_a8df0adas_idb =  act_rel.id AND 
act_rel.deleted = 0 AND act_rel_act.udesa_acti2d4clizadas_idb = act_rel.id 
AND act_rel_act.deleted = 0  AND 
act_rel_act.udesa_acti07cfvidades_ida = act.id AND act.deleted = 0 
AND act.sigla_actividad  LIKE  ',
    'db_field' => 
    array ('id',),),
AND act.anio = ',<Field anio_actividad>

I'll appreciate your help.
Thanks and regards.

Outcomes