AnsweredAssumed Answered

custom search on many to many

Question asked by Mike Solomon on Aug 27, 2015
Latest reply on Aug 27, 2015 by Mike Solomon
I'm trying to create a custom search for a many to many relationship

I found this link http://sugarmods.co.uk/adding-many-to-many-relationships-to-the-search-box-in-suitecrm-and-sugarcrm-...

I followed the instructions and added the following to SearchFields.php

'yy_status_name' => array (
                'query_type' => 'default',
                'operator' => 'subquery',
                'subquery' => 'select sl.yy_status_yy_mainyy_main_idb from yy_status_yy_main_c sl left join yy_status ma on ma.id = sl.yy_status_yy_mainyy_status_ida where sl.deleted = 0 and ma.name LIKE',
                'db_field' => array (
                        0 => 'id',
                ),
        )


This doesn't work for me

My sql statement comes out as :

SELECT  yy_main.id , yy_main.name  , jt0.user_name assigned_user_name ,   jt0.created_by assigned_user_name_owner  , 'Users'   assigned_user_name_mod, yy_main.assigned_user_id  FROM yy_main   LEFT   JOIN  users jt0 ON yy_main.assigned_user_id=jt0.id AND jt0.deleted=0     AND jt0.deleted=0 where ((yy_main.name like 't%' ) AND (   '%'y'%'y'%'_'%'m'%'a'%'i'%'n'%'.'%'i'%'d'%' '%'I'%'N'%'   '%'('%'s'%'e'%'l'%'e'%'c'%'t'%'   '%'y'%'y'%'_'%'s'%'t'%'a'%'t'%'u'%'s'%'_'%'y'%'y'%'_'%'m'%'a'%'i'%'n'%'y'%'y'%'_'%'m'%'a'%'i'%'n'%'_'%'i'%'d'%'b'%'   '%' '%'f'%'r'%'o'%'m'%' '%'('%'s'%'e'%'l'%'e'%'c'%'t'%'   '%'s'%'l'%'.'%'y'%'y'%'_'%'s'%'t'%'a'%'t'%'u'%'s'%'_'%'y'%'y'%'_'%'m'%'a'%'i'%'n'%'y'%'y'%'_'%'m'%'a'%'i'%'n'%'_'%'i'%'d'%'b'%'   '%'f'%'r'%'o'%'m'%'   '%'y'%'y'%'_'%'s'%'t'%'a'%'t'%'u'%'s'%'_'%'y'%'y'%'_'%'m'%'a'%'i'%'n'%'_'%'c'%'   '%'s'%'l'%' '%'l'%'e'%'f'%'t'%' '%'j'%'o'%'i'%'n'%'   '%'y'%'y'%'_'%'s'%'t'%'a'%'t'%'u'%'s'%' '%'m'%'a'%'   '%'o'%'n'%' '%'m'%'a'%'.'%'i'%'d'%' '%'='%'   '%'s'%'l'%'.'%'y'%'y'%'_'%'s'%'t'%'a'%'t'%'u'%'s'%'_'%'y'%'y'%'_'%'m'%'a'%'i'%'n'%'y'%'y'%'_'%'s'%'t'%'a'%'t'%'u'%'s'%'_'%'i'%'d'%'a'%'   '%'w'%'h'%'e'%'r'%'e'%'   '%'s'%'l'%'.'%'d'%'e'%'l'%'e'%'t'%'e'%'d'%' '%'='%'   '%'0'%' '%'a'%'n'%'d'%' '%'m'%'a'%'.'%'n'%'a'%'m'%'e'%'   '%'L'%'I'%'K'%'E'%'   '%'''%'u'%'n'%'i'%'n'%'v'%'o'%'i'%'c'%'e'%'d'%'%'%'''%')'%'     '%'y'%'y'%'_'%'s'%'t'%'a'%'t'%'u'%'s'%'_'%'n'%'a'%'m'%'e'%'_'%'d'%'e'%'r'%'i'%'v'%'e'%'d'%')'%'))   AND yy_main.deleted=0 ORDER BY yy_main.name ASC

Any idea on what i've done wrong?

Outcomes