Filtering entities by related entity's properties

[alasdaircr]

Hi, question about the RDB mapping and improved searching/filtering.

I'm thinking about improving the capabilities so that I could filter one entity based on the properties of linked entities.

eg Search contacts with filter: account.type = "Partner", return all contacts with links to accounts which have account.type = "Partner"

In the 'Add filter' dropdown there could be the related filters of ANY linked entity, in nested lists.

So for Emails, you would see the filters for Leads, Accounts, Opportunity, Case.

The query might be a little bit slow with all the joins but would be really useful.

So thinking about Espo, is this approach possible in a sane way which only needs core modifications?

[alasdaircr]

Here is a visualisation of what I mean:

[yuri]

Hi

There is a field type "foreign" that works on ORM level but not tested in application. Create your field (manually in metadata) of "foreign" type. It should work only with belongsTo relationship.


I guess it should be:

{
  "fields": {
       "accountType": {
          "type": "foreign",
          "relation": "account"
       }
   }
} 

or

{
   "fields": {
       "accountType": {
          "type": "foreign",
          "link": "account"
       }
   }
} 

Then add this field to filters layout.

[alasdaircr]

Brilliant I'll give that a try. Would be even better if this was automatic for all belongsTo relationships, but this is a good approximation.

[alasdaircr]

Sadly that doesn't work, either for "relation": "account", or "link": "account". I'm going to have a closer look

[yuri]

Check if this field is created in data/cache/application/ormMetadata.php
Is there "relation" attribute?

Please let me know the result.

[alasdaircr]

Doesn't look it works:

here is the Contact portion of ormMetadata.php: http://sprunge.us/cCbL

I looked through ../../fields/foreign.php, and looking at that, the name is 'link', not 'relation'.

Seeing errors when in my log when I'm rebuilding, so going to remove all my modifications and try on a vanilla espo.

[alasdaircr]

In the meantime I'm looking at how to have the correct field type recognised for a foreign filter in the GUI

[alasdaircr]

I also set filter: true in application/Espo/Resources/metadata/fields/foreign.json, in case that made a difference

[alasdaircr]

These are the errors when rebuilding on an untouched espo 2.5.2:

[2014-09-23 14:32:28] Espo.NOTICE: E_NOTICE: Undefined index: links {"file":"/var/www/crm/espo-testing/application/Espo/Core/Utils/Database/Orm/Converter.php","line":286} []
[2014-09-23 14:32:28] Espo.WARNING: Field type does not exist for Contact:accountId. Use default type [varchar] [] []
[2014-09-23 14:32:28] Espo.NOTICE: E_NOTICE: Undefined index: links {"file":"/var/www/crm/espo-testing/application/Espo/Core/Utils/Database/Orm/Converter.php","line":286} []
[2014-09-23 14:32:28] Espo.WARNING: Field type does not exist for Contact:accountId. Use default type [varchar] [] []

But perhaps there not to be worried about

[yuri]

Forgot foreign attribute:

{
  "fields": {
    "accountType": {
       "type": "foreign",
       "link": "account",
       "foreign" => "type"
    }
  }
} 

It's a field name of foreign entity you want to fetch.

[alasdaircr]

No I'm afraid that doesn't work either (after fixing the json above). There is still no mention of accountType in the Contact section of ormMetadata.php

[yuri]

application/Espo/Resources/metadata/fields/foreign.json

set
"fieldDefs":{
"skip":false
}

and

"filter": true,

[alasdaircr]

Great, that works, and no errors when searching. However searching doesn't work, because Account.Type is an enum, and the search is just a varchar, but maybe we're getting somewhere!

[alasdaircr]

Actually, sorry searching does work. I'd just rather the UI offered enum, array search rather than just string. So I will look at that now.