Desenvolvedores podem saber N linguagens de programação; como buscar os desenvolvedores pelo nome ou pela linguagem?

help-sept22.sql

CREATE DATABASE sept22;
USE sept22;

CREATE TABLE developers (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;

CREATE TABLE languages (
    id INT AUTO_INCREMENT NOT NULL,
    lang VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;

CREATE TABLE developers_languages (
    developer_id INT NOT NULL,
    language_id INT NOT NULL,
    PRIMARY KEY(developer_id, language_id),
    FOREIGN KEY (developer_id) REFERENCES developers(id),
    FOREIGN KEY (language_id) REFERENCES languages(id)
) ENGINE = InnoDB;

INSERT INTO
    developers (id, name)
VALUES
    ( 1, 'Artur'),
    ( 2, 'Balzi'),
    ( 3, 'Bezerra'),
    ( 4, 'Fernando '),
    ( 5, 'Fillipe'),
    ( 6, 'Gabriel'),
    ( 7, 'Hallison'),
    ( 8, 'Herbertt'),
    ( 9, 'Hugo'),
    (10, 'Ítalo'),
    (11, 'Jair'),
    (12, 'Jamal'),
    (13, 'Marcos'),
    (14, 'Oséias'),
    (15, 'Philippe'),
    (16, 'Rodrigo'),
    (17, 'Schulz');

INSERT INTO
    languages (id, lang)
VALUES
    ( 1, 'C'),
    ( 2, 'CPP'),
    ( 3, 'Delphi'),
    ( 4, 'GO'),
    ( 5, 'Java'),
    ( 6, 'JavaScript'),
    ( 7, 'Objective-C'),
    ( 8, 'Perl'),
    ( 9, 'PHP'),
    (10, 'Python'),
    (11, 'R'),
    (12, 'Ruby'),
    (13, 'Visual Basic');

INSERT INTO
    developers_languages (developer_id, language_id)
VALUES
    ( 1,  5), /*    Artur -> Java */
    ( 1,  6), /*    Artur -> JavaScript */
    ( 2,  6), /*    Balzi -> JavaScript */
    ( 2, 12), /*    Balzi -> Ruby */
    ( 3,  4), /*  Bezerra -> GO */
    ( 3,  8), /*  Bezerra -> Perl */
    ( 4,  3), /* Fernando -> Delphi */
    ( 4, 13), /* Fernando -> Visual Basic */
    ( 5, 11), /*  Fillipe -> R */
    ( 5, 12), /*  Fillipe -> Ruby */
    ( 6,  1), /*  Gabriel -> C */
    ( 6, 10), /*  Gabriel -> Python */
    ( 7,  6), /* Hallison -> JavaScript */
    ( 7,  8), /* Hallison -> Perl */
    ( 8,  4), /* Herbertt -> GO */
    ( 8,  7), /* Herbertt -> Objective-C */
    ( 9, 10), /*     Hugo -> Python */
    ( 9, 11), /*     Hugo -> R */
    (10,  1), /*    Italo -> C */
    (10,  3), /*    Italo -> Delphi */
    (11,  4), /*     Jair -> GO */
    (11,  5), /*     Jair -> Java */
    (12,  2), /*    Jamal -> CPP */
    (12,  9), /*    Jamal -> PHP */
    (13,  5), /*   Marcos -> Java */
    (13, 12), /*   Marcos -> Ruby */
    (14,  8), /*   Oséias -> Perl */
    (14,  9), /*   Oséias -> PHP */
    (15,  7), /* Philippe -> Objective-C */
    (15, 12), /* Philippe -> Ruby */
    (16,  1), /*  Rodrigo -> C */
    (16, 11), /*  Rodrigo -> R */
    (17,  6), /*   Schulz -> JavaScript */
    (17, 10); /*   Schulz -> Python */

-- Buscando todos desenvolvedores e suas linguagens
SELECT
    dv.name,
    lg.lang
FROM
    developers dv
    LEFT JOIN developers_languages dl ON dl.developer_id = dv.id
    LEFT JOIN languages lg ON lg.id = dl.language_id;

-- Buscando desenvolvedores e linguagens, de nome @word ou linguagem @word
SET @word = 'Jair';

SELECT
    dv.name,
    lg.lang
FROM
    developers dv
    LEFT JOIN developers_languages dl ON dl.developer_id = dv.id
    LEFT JOIN languages lg ON lg.id = dl.language_id
WHERE
    dv.name = @word OR lg.lang = @word;

-- Resultado:
-- +------+------+
-- | name | lang |
-- +------+------+
-- | Jair | GO   |
-- | Jair | Java |
-- +------+------+

-- De novo a query acima, só que buscando por uma linguagem ao invés de um nome de desenvolvedor
SET @word = 'PHP';

-- Resultado obtido:
-- +---------+------+
-- | name    | lang |
-- +---------+------+
-- | Jamal   | PHP  |
-- | Oséias  | PHP  |
-- +---------+------+

-- Resultado esperado: 
-- +---------+------+
-- | name    | lang |
-- +---------+------+
-- | Jamal   | CPP  |
-- | Jamal   | PHP  |
-- | Oséias  | Perl |
-- | Oséias  | PHP  |
-- +---------+------+

-- Eu quero dar match nos desenvolvedors não apenas através de seus nomes, mas também de suas linguagens. Gostaria que
-- fossem retornadas também todas as linguagens do desenvolvedor que foi selecionado.

SET @word = 'PHP';

select developers.name, languages.lang from
/**
filtra developers expandido developers_languages  para ter lang
e filtrar por @word*/
(select distinct developers.* from developers_languages
inner join developers
on developers_languages.developer_id = developers.id
inner join languages
on developers_languages.language_id = languages.id
where developers.name = @word or languages.lang = @word) as developers
/** expande  developers_languages para ter as outras langs do dev **/
inner join developers_languages
on developers.id = developers_languages.developer_id
inner join languages
on developers_languages.language_id = languages.id

Vou só formatar sua query, @jsbjair:

SET @word = 'PHP';

SELECT
    developers.name, 
    languages.lang 
FROM
    /* filtra developers expandido developers_languages  para ter lang e filtrar por @word */
    (
        SELECT 
            DISTINCT developers.* 
        FROM 
            developers_languages
            INNER JOIN developers ON developers_languages.developer_id = developers.id
            INNER JOIN languages ON developers_languages.language_id = languages.id
        WHERE
            developers.name = @word OR languages.lang = @word
    ) AS developers
    /* expande  developers_languages para ter as outras langs do dev */
    INNER JOIN developers_languages ON developers.id = developers_languages.developer_id
    INNER JOIN languages ON developers_languages.language_id = languages.id;

Outras soluções:

-- Usando IN
SELECT
    dv.name, 
    lg.lang 
FROM
    developers dv
    LEFT JOIN developers_languages dl ON dl.developer_id = dv.id
    LEFT JOIN languages lg ON lg.id = dl.language_id
WHERE dv.id IN (
    SELECT
        dv.id
    FROM
        developers dv
        LEFT JOIN developers_languages dl ON dl.developer_id = dv.id
        LEFT JOIN languages lg ON lg.id = dl.language_id
    WHERE
        dv.name = @word OR lg.lang = @word
);

-- Usando EXISTS
SELECT
    dv.name, 
    lg.lang 
FROM
    developers dv
    LEFT JOIN developers_languages dl ON dl.developer_id = dv.id
    LEFT JOIN languages lg ON lg.id = dl.language_id
WHERE EXISTS (
    SELECT
        1
    FROM
        developers sub_dv
        LEFT JOIN developers_languages sub_dl ON sub_dl.developer_id = sub_dv.id
        LEFT JOIN languages sub_lg ON sub_lg.id = sub_dl.language_id
    WHERE
        sub_dv.id = dv.id AND (sub_dv.name = @word OR sub_lg.lang = @word)
);

Agora a questão é, como fazer isso no Doctrine?