Tengo un formulario de búsqueda de front-end que permite a los usuarios buscar Contactos dados una serie de campos de búsqueda (nombre, dirección, correo electrónico, categorías, etc.). Ahora tengo el requisito de implementar búsquedas de categorías "negativas". En otras palabras, pueden buscar un contacto que pertenezca a una categoría, pero no a otra. Un ejemplo sería devolver todos los contactos asignados a "categoría_1" y no asignados a "categoría_2". Lo hacen ingresando en un campo de formulario html de front-end como este: "categoría_1, -categoría_2" (observe el signo menos).

Estoy intentando usar la función NO EXISTE en MySQL porque necesito el "filtro negativo" en la cláusula WHERE (por varias razones). A continuación se muestra mi intento de implementar. ¿Alguna idea de por qué esto produce cero resultados?

Para mayor claridad, la disposición de mi mesa es la siguiente (muchos a muchos): Contacto -> ContactoCategorías <- Categoría

SELECT DISTINCT
    contact.*
FROM
    Contact contact
    INNER JOIN ContactCategory categories ON contact.ContactId = categories.ContactId
    INNER JOIN Category category ON categories.CategoryId = category.CategoryId
WHERE
    LOWER(category.Name) = 'category_1'
    AND NOT EXISTS ( 
        SELECT DISTINCT
            contact_temp.*
        FROM
            Contact contact_temp
            INNER JOIN ContactCategory categories_temp ON contact_temp.ContactId = categories_temp.ContactId
            INNER JOIN Category category_temp ON categories_temp.CategoryId = category_temp.CategoryId
        WHERE
            LOWER(category_temp.Name) = 'category_2'
    )
1
Burton 4 oct. 2021 a las 19:03

2 respuestas

La mejor respuesta

Debe correlacionar la subconsulta con la consulta principal.

No es necesario seleccionar Contact en la subconsulta, simplemente seleccione ContactCategory y correlacione eso con categories_temp.ContactId = contact.ContactId.

SELECT DISTINCT
    contact.*
FROM
    Contact contact
    INNER JOIN ContactCategory categories ON contact.ContactId = categories.ContactId
    INNER JOIN Category category ON categories.CategoryId = category.CategoryId
WHERE
    LOWER(category.Name) = 'category_1'
    AND NOT EXISTS ( 
        SELECT DISTINCT
            contact_temp.*
        FROM
            ContactCategory categories_temp
            INNER JOIN Category category_temp ON categories_temp.CategoryId = category_temp.CategoryId
        WHERE
            categories_temp.ContactId = contact.ContactId
            AND LOWER(category_temp.Name) = 'category_2'
    )
1
Barmar 4 oct. 2021 a las 16:13

La mayor parte de su código se repite. Por esa razón no usaría NOT EXISTS()

En su lugar, puede usar una sola subconsulta (no correlacionada) para verificar ambas condiciones y unir los resultados a la tabla contact.

Nota: También eliminé la palabra clave DISTINCT. A menudo se utiliza para ocultar consultas o datos defectuosos; arregle la falla, no manipule la consulta.

SELECT
  contact.*
FROM
  Contact contact
INNER JOIN
(
  SELECT
    categories.ContactId
  FROM
    ContactCategory categories
  INNER JOIN
    Category        category
      ON categories.CategoryId = category.CategoryId
  WHERE
    LOWER(category.Name) IN ('category_1', 'category_2')
  GROUP BY
    categories.ContactId
  HAVING
        MAX(CASE WHEN LOWER(category.Name) = 'category_1' THEN 1 END) = 1
    AND MAX(CASE WHEN LOWER(category.Name) = 'category_2' THEN 1 END) = 0
)
  category_search
    ON contact.ContactId = category_search.ContactId

Además, tenga en cuenta que el uso de LOWER(category.Name) inhibe el uso de índices en esa tabla. Es mejor tener lower_name como columna indexada y buscarlo directamente, sin incrustar el LOWER() en su consulta.

1
MatBailie 4 oct. 2021 a las 16:36