Search many to many relationship field

👨‍💻 Development Help
1

Hello!

I have tried to code the custom query to search through a many to many related field but it always returns 0 results and doesn’t show any errors.

I want to search by the Account Name in a Project, and they are related with a Many to Many relationship.

In custom\modules\Project\metadata\searchdefs.php I have:

'accounts' => 
      array (
        'type' => 'link',
        'label' => 'LBL_ACCOUNTS',
        'width' => '10%',
        'default' => true,
        'name' => 'accounts',
      ),

And in custom\modules\Project\metadata\SearchFields.php:

'accounts' =>
  array (
    'query_type' => 'format',
    'operator' => 'subquery',
    'subquery' => 'SELECT projects_accounts.account_id FROM projects_accounts
                           INNER JOIN accounts ON accounts.id = projects_accounts.account_id AND accounts.deleted = 0
                           WHERE projects_accounts.deleted = 0 AND accounts.name LIKE \'%{0}%\'',
    'db_field' => 
    array (
      0 => 'id',
    ),
  ),

If I put something wrong on purpose in the query it shows the error, so I’m quite sure the query is being executed correctly. The only message that I can see is:
[DEPRECATED] Using row number in fetchByAssoc is not portable and no longer supported. Please fix your code.

It always returns 0 results when you fill the field Account. Any ideas?

Thank you!!

2

I’m answering to myself with the approach I ended up doing.

I coulnd’t find why it would always return 0 results, so I tried a different solution:1. I created a text field in Projects for each N-N entity that I was going to do the search.

  1. Created a logic hook for after_relationship_add and after_relationship_delete and in each one, I would call the same method: I would get the related beans and create a String to fill the field created in the previous step.
  2. Only show the text field in the ListView, so that you can’t edit it by yourself and when you navigate to the DetailView you still can see the entities in their panel
    This is the logic hook:

class logic_hook_after_relationships
    {
        function fillRelationships(&$focus, $event, $arguments) {
            
            $empresasAdheridas = $this->rellenaEntidad($focus->get_linked_beans('accounts','Accounts',array()));
            $clientes = $this->rellenaEntidad($focus->get_linked_beans('project_clien_clientes_1','clien_Clientes',array()));
            $colaboradores = $this->rellenaEntidad($focus->get_linked_beans('colab_colaboradores_project_1','colab_Colaboradores',array()));
            $ambitos = $this->rellenaAmbitos($focus->get_linked_beans('ce_categoriasempresa_project','ce_CategoriasEmpresa',array()));
            
            $focus->categorias_por_empresa_c = $ambitos;
            $focus->clientes_c = $clientes;
            $focus->colaboradores_c = $colaboradores;
            $focus->empresas_c = $empresasAdheridas;
            
            $focus->save(true);
        }
        
        function rellenaEntidad($arrayEmpresas) {
            $str = "";
            foreach ($arrayEmpresas as $bean) {
                $str .=  ", " . $bean->name;
            }
            $str = substr($str, 2);
            return $str;
        }
        
        function rellenaAmbitos($arrayEmpresas) {
            $str = "";
            foreach ($arrayEmpresas as $bean) {
                $str .= ", " . $bean->ambito;
            }
            $str = substr($str, 2);
            return $str;
        }
    }

I tried to replicate this same logic hook in a custom module, but it shows an error. I will post it in another post in case anyone can help.

Thank you!

3

Hi,
do you resolved this issue?

4

Hi, another way to realise this is by vardefs:

  1. Create the file

custom/Extension/modules/Project/Ext/Vardefs/account_name.php

with contents

<?php

$dictionary['Project']['fields']['account_name'] = array (
		'name' => 'account_name',
		'rname' => 'name',
		'id_name' => 'account_id',
		'vname' => 'LBL_ACCOUNT_NAME',
		'type' => 'relate',
		'link' => 'accounts',
		'table' => 'accounts',
		'isnull' => 'true',
		'module' => 'Accounts',
		'dbType' => 'varchar',
		'source' => 'non-db',
		'studio' => array('editview' => false, 'detailview' => false, 'listview' => false, 'dashlet' => false, 'popuplist' => false),
	);
  1. Create the file

custom/Extension/modules/Project/Ext/Language/en_us.account_name.php

with contents

<?php

$mod_strings['LBL_ACCOUNT_NAME'] = 'Account Name';
  1. Do a Admin -> Repair -> Quick Repair and Rebuild

Afterwards you can go into studio and add the field in your Quick Filter and/or Advanced Filter.

The field in the filter is a relate field, from which you can search and select existing Accounts. In case you need a text field (so you can use wildcards), then, after you have added the field to your filter, edit:

custom/modules/Project/metadata/searchdefs.php

and locate the account_name field:

      'account_name' =>
      array (
        'type' => 'relate',
        'link' => true,

Modify ‘type’ from ‘relate’ to ‘varchar’, so it becomes

     'account_name' =>
      array (
        'type' => 'varchar',
        'link' => true,

and save.

Afterwards to a Admin -> Repair -> Quick Repair and Rebuild.

2 Likes