Сравнение двух подходов для поиска товаров по переводам названий, описания и текста

Поиск товаров по локализациям. Плагин i18n4marketpro и поля ipage_title, ipage_desc, ipage_text в поиске в общем списке позиций витрины. Сравнение двух подходов для поиска товаров по переводам названий, описания и текста - EXISTS и LEFT JOIN + COALESCE

у нас есть

// Импорт параметра области поиска: title – только название, full – название и описание, pcod – код товара
$search_in = cot_import('search_in', 'G', 'ALP', 8);
if (!in_array($search_in, ['title', 'full', 'pcod'])) {
    $search_in = 'title'; // по умолчанию ищем только в названиях
}
// Определяем таблицу переводов, если плагин активен
$i18n_table = '';
if (cot_plugin_active('i18n4marketpro')) {
    if (!isset(Cot::$db->i18n4marketpro_pages)) {
        Cot::$db->registerTable('i18n4marketpro_pages');
    }
    $i18n_table = Cot::$db->i18n4marketpro_pages;
}

второй участок

// ===== УЧЁТ ПЕРЕВОДОВ i18n4marketpro =====
// Принудительно устанавливаем условие поиска в зависимости от режима,
// игнорируя любые изменения, внесённые плагинами в хук.
if (!empty($sq)) {
    $sq_escaped = Cot::$db->quote("%$sq%");
    
    // Базовая часть условия (по умолчанию только основные поля)
    if ($search_in === 'title') {
        $base_condition = "fieldmrkt_title LIKE $sq_escaped";
    } elseif ($search_in === 'full') {
        $base_condition = "(fieldmrkt_title LIKE $sq_escaped OR fieldmrkt_text LIKE $sq_escaped)";
    } elseif ($search_in === 'pcod') {
        $base_condition = "fieldmrkt_pcod LIKE $sq_escaped";
    }

    // Подключаем поиск по переводам, если активен плагин и язык не основной
    $use_i18n_search = false;
    $i18n_condition = '';

    if (cot_plugin_active('i18n4marketpro')) {
        // Регистрируем таблицу, если ещё не зарегистрирована
        if (!isset(Cot::$db->i18n4marketpro_pages)) {
            Cot::$db->registerTable('i18n4marketpro_pages');
        }
        $i18n_table = Cot::$db->i18n4marketpro_pages;

        $current_locale = Cot::$usr['lang'] ?: Cot::$cfg['defaultlang'];
        if ($current_locale !== Cot::$cfg['defaultlang']) {
            $use_i18n_search = true;

            // Формируем подзапрос EXISTS для поиска в переводах
            if ($search_in === 'title') {
                $i18n_condition = "EXISTS (SELECT 1 FROM $i18n_table WHERE ipage_id = p.fieldmrkt_id AND ipage_locale = " . Cot::$db->quote($current_locale) . " AND ipage_title LIKE $sq_escaped)";
            } elseif ($search_in === 'full') {
                $i18n_condition = "EXISTS (SELECT 1 FROM $i18n_table WHERE ipage_id = p.fieldmrkt_id AND ipage_locale = " . Cot::$db->quote($current_locale) . " AND (ipage_title LIKE $sq_escaped OR ipage_desc LIKE $sq_escaped OR ipage_text LIKE $sq_escaped))";
            }
            // Для 'pcod' перевод не ищем
        }
    }

    // Финальное условие WHERE для поиска
    if ($use_i18n_search && !empty($i18n_condition)) {
        $where['search'] = "($base_condition OR $i18n_condition)";
    } else {
        $where['search'] = $base_condition;
    }
}

// ===== КОНЕЦ ДОБАВЛЕНИЯ =====

ГОВОРИТ ИИ 

вариант с EXISTS действительно лучше в большинстве реальных сценариев, особенно если:

• таблица переводов (i18n4marketpro_pages) может быть большой
• не все товары имеют перевод
• важно не дублировать строки в результате (как это может происходить при обычном LEFT JOIN + OR)
• ты хочешь чётко разделить логику «есть перевод → ищем в нём» и «нет перевода → ищем в оригинале»

Сравнение двух подходов

Сильные стороны

• Использует EXISTS — это классический и эффективный паттерн именно для «есть ли хотя бы одна подходящая строка в связанной таблице».
• Не портит основной результат JOIN-ом (особенно важно, если позже будут ещё LEFT JOIN-ы).
• Логично разделяет: базовый поиск + опциональный поиск в переводе.

Что можно улучшить / доработать в твоём коде

Вот несколько мелких, но полезных правок:

if (!empty($sq)) {
    $sq_escaped = Cot::$db->quote("%$sq%");

    // Базовое условие — всегда по оригинальным полям
    if ($search_in === 'title') {
        $base_condition = "fieldmrkt_title LIKE $sq_escaped";
    } elseif ($search_in === 'full') {
        $base_condition = "(fieldmrkt_title LIKE $sq_escaped OR fieldmrkt_text LIKE $sq_escaped)";
        // Если есть поле fieldmrkt_desc — обычно его тоже добавляют в 'full'
        // $base_condition = "(fieldmrkt_title LIKE $sq_escaped OR fieldmrkt_desc LIKE $sq_escaped OR fieldmrkt_text LIKE $sq_escaped)";
    } elseif ($search_in === 'pcod') {
        $base_condition = "fieldmrkt_pcod LIKE $sq_escaped";
    } else {
        $base_condition = "1 = 0"; // защита от некорректного $search_in
    }

    $search_condition = $base_condition;

    // ─── Поиск по переводам ───────────────────────────────────────
    if (cot_plugin_active('i18n4marketpro')) {
        $current_locale = Cot::$usr['lang'] ?: Cot::$cfg['defaultlang'];

        if ($current_locale !== Cot::$cfg['defaultlang']) {
            $i18n_table = Cot::$db->i18n4marketpro_pages;

            if ($search_in === 'title') {
                $i18n_cond = "ipage_title LIKE $sq_escaped";
            } elseif ($search_in === 'full') {
                $i18n_cond = "(ipage_title LIKE $sq_escaped OR ipage_desc LIKE $sq_escaped OR ipage_text LIKE $sq_escaped)";
            } else {
                $i18n_cond = ''; // pcod не переводится
            }

            if ($i18n_cond) {
                $exists = "EXISTS (
                    SELECT 1 
                    FROM $i18n_table 
                    WHERE ipage_id = p.fieldmrkt_id 
                      AND ipage_locale = " . Cot::$db->quote($current_locale) . " 
                      AND $i18n_cond
                )";

                $search_condition = "($base_condition OR $exists)";
            }
        }
    }

    $where['search'] = $search_condition;
}

Ключевые улучшения в этом варианте

1. Защита от пустого/некорректного $search_in
2. Явно показан случай, когда переводов нет → ищем только в оригинале
3. Переменная $i18n_cond — легче читать и модифицировать
4. Можно легко отключить поиск по оригиналу (если хочешь только переводы) — просто закомментировать $base_condition в OR

Когда EXISTS может быть хуже JOIN + COALESCE

• Если почти все товары имеют перевод → тогда EXISTS будет запускать подзапрос почти для каждой строки → может быть медленнее
• В этом случае COALESCE + LEFT JOIN часто выигрывает (особенно с хорошим индексом)

Но в реальности у большинства сайтов переводов гораздо меньше, чем товаров → EXISTS обычно выигрывает.