Subconsultas a fondo

Abre la related list de Oportunidades de una cuenta, ordénala por importe y quédate con las primeras: eso, para todas las cuentas a la vez, es una subconsulta con su propio ORDER BY y LIMIT. Y el filtro cruzado de un informe («Cuentas con Oportunidades») es la otra mitad de la sub-lección.

El nombre de la relación: dónde se mira

El FROM de una subconsulta no lleva el nombre del objeto hijo, sino el de la relación hija. Para los objetos estándar es el plural en inglés (Contacts, Opportunities, Cases). Para los personalizados lo decidió quien creó el campo de búsqueda, y lleva __r al final. No hay que adivinarlo: está escrito en Setup.

La nutria: Lo configuraste tú al crear el campo

Está en Object Manager → el objeto HIJO → Fields & Relationships → el campo que apunta al padre → «Child Relationship Name». Es el nombre que elegiste en el asistente al crear la relación, junto a la etiqueta de la related list. Con __r detrás, es lo que va en el FROM.

Su propio WHERE, ORDER BY y LIMIT

Dentro de los paréntesis de una subconsulta caben las mismas cláusulas que ya conoces. Y aquí está la idea que más cuesta al principio: se aplican a los hijos DE CADA PADRE por separado. LIMIT 3 dentro de la subconsulta no significa «tres oportunidades en total», significa «como mucho tres oportunidades por cuenta».

List<Account> accounts = [
    SELECT Name,
        (SELECT Name, Amount
         FROM Opportunities
         WHERE IsClosed = false
         ORDER BY Amount DESC
         LIMIT 3)
    FROM Account
    WHERE Industry = 'Technology'
];
// 10 cuentas → hasta 30 oportunidades: las 3 mayores DE CADA UNA

Recorrerla: un bucle dentro de otro

Cada cuenta trae sus hijos en una propiedad con el nombre de la relación (a.Opportunities, a.Contacts): una List normal que recorres con el for de siempre. Si no hay hijos llega vacía, no null, y el bucle no da ninguna vuelta.

for (Account a : accounts) {
    System.debug(a.Name + ' tiene ' + a.Opportunities.size() + ' abiertas');

    for (Opportunity o : a.Opportunities) {
        System.debug('   ' + o.Name + ' · ' + o.Amount);
    }
}

Filtrar los padres por sus hijos: IN y NOT IN

Cuando lo que quieres es decidir QUÉ padres entran según sus hijos, la subconsulta va en el WHERE, detrás de IN o NOT IN, y devuelve una lista de Ids. Es exactamente el filtro cruzado de los informes: «Cuentas CON casos abiertos» es IN; «Cuentas SIN oportunidades» es NOT IN.

// Filtro cruzado «CON»: cuentas que tienen algún caso abierto
List<Account> withOpenCases = [
    SELECT Name FROM Account
    WHERE Id IN (SELECT AccountId FROM Case WHERE IsClosed = false)
];

// Filtro cruzado «SIN»: cuentas que nunca han tenido una oportunidad
List<Account> neverSold = [
    SELECT Name FROM Account
    WHERE Id NOT IN (SELECT AccountId FROM Opportunity)
];

• A la izquierda del IN va el Id o un campo que apunta a otro registro (AccountId, OwnerId), y sin puntos: Account.Id no vale.
• La subconsulta selecciona UN solo campo, que también apunta a un registro, y tampoco puede llevar puntos.
• Los dos lados tienen que hablar del mismo objeto: Id de Account a la izquierda, AccountId a la derecha.

La nutria: Traer hijos y filtrar por hijos no se sustituyen

Esto me costó entenderlo: en un informe con Report Type «Cuentas con Oportunidades» ves las oportunidades Y solo salen las cuentas que tienen alguna. En SOQL son dos piezas separadas: la subconsulta del SELECT trae los hijos pero no quita padres; el IN del WHERE quita padres pero no trae hijos. Para reproducir ese informe hacen falta las dos a la vez.

// Solo cuentas con casos abiertos, y cada una con SUS casos abiertos
List<Account> support = [
    SELECT Name,
        (SELECT CaseNumber, Subject, Priority
         FROM Cases
         WHERE IsClosed = false
         ORDER BY Priority)
    FROM Account
    WHERE Id IN (SELECT AccountId FROM Case WHERE IsClosed = false)
];

Los límites que hay que saberse

Y el límite que más importa, el de consultas: una consulta con subconsultas es UNA sentencia SOQL de las 100 que permite una transacción, traiga los hijos que traiga. Por eso la subconsulta no es solo cómoda: es la forma de no preguntar a la base de datos una vez por cada registro. Cambia de pestaña en el diagrama y sube a 200 cuentas.

La nutria: ¿Y por qué no un Flow? Porque «las que nunca compraron» no se puede preguntar

«Cuentas con negocio abierto» sale en Flow con dos Get Records y un Loop que copia los AccountId para el operador In. Pero «las que nunca han tenido una oportunidad» obliga a traer al flow todas las oportunidades de la historia solo para saber qué cuentas tienen alguna, y en una org con años de datos ese Get Records revienta en «Too many query rows: 50001». Con NOT IN (SELECT AccountId FROM Opportunity), el cruce lo hace la base de datos y a tu código solo llegan las cuentas.