Search entries AND within associated assets fields

Question

I have a large list of entries. Each entry has a a bunch of fields and a file-field with one or two files. Those files have related field (Author, Date, ..).

I have now implemented a search. All things are working so far. However, the search does not include the asset's fields. Only the asset's title is recognized.

Can I somehow search in asset fields? I tried something like

field1: *query* AND files.author: *query*

but this does not work :(

I have read that I can search all assets that are related to that section, but I would like to include this query in one query than in two queries..

Answer 1

I have now found a workaround using the information @brad-bell provided, that is working, but not exactly what I wanted:

{# Normal search #}
{% set resultsA = craft.entries(search_criteria).ids() %}

{# Assets search #}
{% set assets = craft.assets.customFieldName('*'~query~'*') %}
{% set resultsB = craft.entries.relatedTo({
        targetElement: assets,
        field: 'customFieldName'
    }).ids() | without(resultsA) %}

{# Merge results, paginate #}
{% set results = resultsA | merge(resultsB) %}
{% paginate results.limit(10) as pageInfo, pageEntries %}

How I did it:

1. Perform a normal search for entries containing the query in one of their fields (specified in search_criteria, not included in the code above) and get the IDs of the found entries.
2. Search for assets that contain the query in their customField. Since I'm only searching one field, I do not use the search-function, which would be an option when searching multiple fields.
3. Now I'm getting the entries that are related to the assets. As in step 1, I attain the IDs, ignoring IDs already present in the first set of results, to avoid duplicates (which I found out late are not a problem, since craft.entries - in point 5 - only returns each entry once)
4. I merge both arrays.
5. Get an ElementCriteriaObject containing all entries with the IDs collected before.
6. I paginate them as usual.

If you do not need to paginate, step 5-6 are might be unnecessary. You could simple use find() instead of ids() to get arrays which you then could merge. This however will cause duplicates!

Although this code works, it has a few disadvantages:

• The results are ordered by their position in both arrays. resultsA are listed first, followed by resultsB. In my application I can live with that, since queries fetching many results in assets will usually fetch almost no results in entries and vice versa. I tried to sort and order them by the number of occurrences, however, this did not work using only twig functions. With the help of a simple plugin as host for some php array sorting, ordering of the results could be improved.
• Performance is not very good, since many queries are necessary.

Idea

One idea in my mind was to build a custom MySQL-query, which JOINS the assets fields (combined to a string if there are multiple ones) to the entry's row. However, I was not able to accomplish that. If anyone knows how to do so: Let me know!

Thanks for your help!