How to extend the article search to take into account the values of custom fields?

Question

In articles I have added a series of custom fields, now I need to be able to filter through these fields, while being in the article manager view. I have searched about it but without success, I just need that when searching take into account the values that have been added in these custom fields. I have read of a couple of extensions that allow this as ja megafilter or easylayout but I understand they work in the frontend context and I need the search to be done in the context of article manager view. How to extend the article search to take into account the values of custom fields?

Update 1

This temporary solution has been working

This query returns articles according to the value of a custom field

SELECT
    DISTINCT c.id, c.title
FROM fields f 
    join fields_values v on f.id=v.field_id 
    join content c on c.id=v.item_id
WHERE
    f.name='{field_name}' and v.value='{value}'

It is possible to extend the where and aggregate the custom fields that you require, for example

SELECT
    DISTINCT c.id, c.title
FROM fields f 
    join fields_values v on f.id=v.field_id 
    join content c on c.id=v.item_id
WHERE
    (f.name='{field_name1}' and v.value='{value}') or (f.name='{field_name2}' and v.value = '{value}')

In Joomla I modified the query in the file plugins/system/jacontenttype/models/com_content.admin.articles.php in the getListQuery method

From

$query
    ->from('#__content AS a');

To

$query
    ->from('#__content AS a')
    ->join('LEFT', '#__fields_values AS v ON a.id = fv.item_id')
    ->join('LEFT', '#__fields f ON f.id = fv.field_id');

In the same method in the section where it is verified if the $search variable is not empty, modify the else section by adding orWhere as follows

$search = $db->quote('%' . str_replace(' ', '%', $db->escape(trim($search), true) . '%'));
$query->where('(a.title LIKE ' . $search . ' OR a.alias LIKE ' . $search . ' OR a.note LIKE ' . $search . ')', 'OR');
$query->orWhere("(f.name = 'field_1' and fv.value LIKE $search) OR (f.name = 'field_2' and fv.value LIKE $search)")

I know it is not the right solution because I modify joomla files but maybe this is for someone of utility

by the way, the credit for the query to Elbek, in this answer

Thanks for your comments

Answer 1

First of all. This is potentially an awesome idea you have for searcheable admin custom fields. This could be done using template override and/or layout override of

JPATH_ADMINISTRATOR/components/com_content/articles/default.php

And create a dedicated search plugin, better yet a system plugin eg: plg_system_searchcustomfields to hook into content filtering and add the desired behaviour.