Queries Avanzadas en Salesforce (Parte 1)
Si has entrado en este artículo entiendo que ya sabes hacer queries sencillas en Salesforce. Si no es así, igual la lectura se te hace algo pesada porque vamos a hablar de opciones más avanzadas a la hora de consultar datos en Salesforce. Si no cuentas con la base para hacer consultas sencillas en Salesforce te recomiendo que adquieras esa base antes y dejes este artículo para más adelante.
1 Subconsultas
Traernos los registros “hijo” de un objecto en una subconsulta es muy sencillo. Si quisiéramos recuperar todos los contactos u oportunidades de cada una de las cuentas que nos devuelve una query, lo podemos hacer añadiendo una subconsulta en el SELECT.
SELECT Id, Name, (SELECT Name FROM Contacts), (SELECT Name FROM Opportunities)
FROM AccountCómo habrás podido observar, los nombres de las tablas en nuestras subconsultas no son exactamente el mismo que las tablas originales. Contact es Contacts, Opportunity es Opportunities o CaseTeamMember es TeamMembers.
SELECT Name, (SELECT MemberId FROM TeamMembers)
FROM CaseEstos nombres son los nombres de relación que definimos cuando creamos un campo Lookup o Master-Detail. Si no lo hemos creado nosotros o no nos acordamos del nombre podemos verlo en la configuración del campo en el Object Manager.
También podemos utilizar subconsultas para filtrar campos utilizando una cláusula WHERE.
SELECT Name
FROM Contact
WHERE AccountId IN (SELECT Id
FROM Account
WHERE Rating = 'Hot')En este caso si utilizaremos el nombre de la tabla original sobre la que estamos realizando la subconsulta, el nombre de la tabla sólo cambiará si esa subconsulta está en el SELECT.
Para este tipo de subconsultas sólo podemos incluir un campo en el SELECT, el campo que tiene el valor o los valores que estamos filtrando en la cláusula WHERE de la consulta principal.
2 Relaciones polimórficas
Obtener datos relacionados de campos cuando pueden ser distintos tipos de objectos. Por ejemplo, el campo WhatId en Task que puede relacionarse con prácticamente cualquier objecto de nuestra modelo de datos, o el campo OwnerId que tienen todos los objetos que puede ser un usuario o un grupo.
Este tipo de relación en Salesforce se llama relación polimórfica. Usaremos la cláusula TYPEOF para definir para cada tipo de objeto que pueda ser la relación, los campos que nos tenemos que traer. Se ve mucho más claro con un ejemplo.
SELECT CaseNumber,
TYPEOF Owner
WHEN User THEN Username, IsActive
WHEN Group THEN DeveloperName
END
FROM CaseEsto sólo será necesario cuando queramos traernos campos de un tipo de objeto que otro u otros tipos de objetos que puede tener la relación polimórfica no tienen. Si todos los tipos de objetos tienen un campo que se llama igual, lo podremos traer sin necesidad de la cláusula TYPEOF.
SELECT Owner.Name, Owner.CreatedDate
FROM AccountTambién podríamos filtrar nuestros datos para traernos los registros únicamente relacionados con determinado tipo de dato en nuestra relación polimórfica.
SELECT Id
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')3 Scope
Seguramente te habrás fijado que en algunas funcionalidades de Salesforce destinadas a visualizar datos, como pueden ser las List Views o los Reports, nos aparece un selector para elegir el “scope” de los datos a mostrar. Este mismo comportamiento lo podemos replicar a la hora de hacer consultas directamente a la base de datos.
Es muy útil para traernos fácilmente todos los registros que pertenecen a una persona, o los registros que pertenecen a determinado territorio si tenemos activado la gestión de territorios.
Por ejemplo, para traernos sólo las cuentas de las que yo soy propietario lo haríamos así:
SELECT Name, Rating
FROM Account
USING SCOPE mineTenemos estos diferentes “scopes” disponibles:
- delegated: Registros delegados a otros usuarios. Por ejemplo, una tarea que hemos delegado a otra persona.
- everything: Todos los registros. Es lo mismo que no incluir la cláusula SCOPE.
- mine: Sólo los registros del usuario que está ejecutando la consulta.
- mine_and_my_groups: Este filtro sólo aplica al objeto ProcessInstanceWorkItem de los Approval Process. Nos devolverá sólo los registros asignados a nosotros o a alguno de los miembros de nuestro equipo.
- my_territory: Sólo estará disponible si tenemos activada la gestión de territorios. Nos mostrará sólo los registros del territorio del usuario que está ejecutando la consulta.
- my_team_territory: Sólo estará disponible si tenemos activada la gestión de territorios. Nos mostrará sólo los registros del territorio del usuario que está ejecutando la consulta o el de alguno de los de su equipo.
- scopingRule: Sólo estará disponible si hemos activado alguna Scoping Rule sobre el objeto que estemos consultando. Nos mostrará sólo los registros definidos en la Scoping Rule que aplica al usuario que está ejecutando la consulta.
- team: Sólos nos mostrará los registros asignados a un equipo, cómo un Account Team o un Case Team.
4 Filtros de fecha
SOQL nos ofrece una gran cantidad de expresiones para filtrar los datos por campos de fecha de manera dinámica. Algunos ejemplos donde esto nos puede ser útil pueden ser:
- Obtener todos los registros de determinado objeto que se creando hoy. Por si hemos implementado una nueva funcionalidad y queremos revisar su acogida.
SELECT Name
FROM Account
WHERE CreatedDate = TODAY- Obtener todos los registros creados y actualizados en el último cuatrimestre para hacer un informe de negocio.
SELECT Name
FROM Account
WHERE LastModifiedDate = LAST_QUARTER- Obtener todos los registros creados en el último año para pasarlo a un histórico.
SELECT Name
FROM Account
WHERE LastModifiedDate = LAST_YEARTip Adicional: Si quiero consultar los registros creados o actualizados en determinada fecha, lo hago directamente sobre LastModifiedDate. Cuando un registro se crea, no se crea con el campo LastModifiedDate vacío sino con el mismo valor que el campo CreatedDate.
Tip Adicional (2): Incluso si queremos optimizar más la consulta. Podemos sustituir el campo LastModifiedDate por el campo SystemModStamp que está indexado y contempla las modificaciones de los procesos a parte de las modificaciones de los usuarios.
Si queréis consultar el listado completo de expresiones de fecha (muy recomendable), lo podéis encontrar en la documentación oficial de Salesforce:
5 toLabel()
Muy sencillo y muy útil a veces. Cuando recuperamos el valor seleccionado en un campo Picklist, la base de datos nos devuelve su API Name. Si queremos modificar este comportamiento estándar tendremos que encapsular el campo Picklist del SELECT dentro de la función toLabel()
SELECT toLabel(MyCustomPicklist__c)
FROM AccountEsto no es ningún problema si la Label de nuestro valor es el mismo que el API Name, y además no tenemos ningún otro idioma en nuestro entorno. Pero si la Label de nuestro valor es diferente a su API Name o tenemos algún idioma en nuestro entorno para el que hayamos tenido que traducir la Label, puede que queramos pintar el valor que tenemos en la Label del valor en vez de su API Name.
También nos sirve para traernos la traducción que aplique al usuario que está ejecutando la consulta en todos aquellos metadata al que tengamos acceso desde SOQL. Por ejemplo, para traernos la traducción de los nombres de los Record Types:
SELECT toLabel(RecordType.Name)
FROM AccountSi quisiéramos traernos tanto la Label como el API Name del valor de la Picklist, simplemente tendríamos que crear un “alias” para alguno de las referencias al campo. Por ejemplo:
SELECT MyCustomPicklist__c, toLabel(MyCustomPicklist__c) PicklistLabel
FROM AccountDe la misma manera podemos utilizar esta función para los filtros de la consulta. Aunque no podemos utilizarlo junto a la cláusula ORDER BY.
6 GROUP BY ROLLUP
Seguramente ya sepas mostrar el número de registros en cada grupo cuando haces un GROUP BY, pero ¿sabrías mostrar en esa misma consulta la suma total de registros? Para eso podemos utilizar la cláusula GROUP BY ROLLUP.
Veamos un ejemplo de cómo utilizar esta cláusula:
SELECT Rating, COUNT(Name) cnt
FROM Account
GROUP BY ROLLUP(Rating)Lo que nos devolverá lo siguiente:
| Rating | cnt |
| --------- | ------ |
| Hot | 7 |
| Warm | 5 |
| Cold | 2 |
| | 14 | // Nos muestra el total al finalDe la misma manera que con el GROUP BY, podemos agrupar los datos por varios campos.
SELECT Rating, Type, COUNT(Name) cnt
FROM Account
GROUP BY ROLLUP(Rating, Type)7 GROUP BY CUBE
Si con la cláusula anterior podíamos agregar el total de registros en los resultados. Con la cláusula GROUP BY CUBE podemos agregar subtotales en cada uno de los grupos/subgrupos. Esto tiene sentido cuando agrupamos por dos o más campos.
Veamos un ejemplo de cómo utilizar esta cláusula:
SELECT Rating, Industry, COUNT(Name) cnt
FROM Account
GROUP BY CUBE(Rating, Industry)Lo que nos devolverá lo siguiente:
| Rating | Industry | cnt |
| --------- | -------------- | ----- |
| Hot | | 2 |
| Hot | Electronics | 4 |
| Hot | Energy | 1 |
| Hot | | 7 | // Subtotal del grupo Rating
| Warm | | 1 |
| Warm | Apparel | 1 |
| Warm | Education | 2 |
| Warm | Hospitality | 1 |
| Warm | | 5 | // Subtotal del grupo Rating
| Cold | | 1 |
| Cold | Transportation | 1 |
| Cold | | 2 | // Subtotal del grupo Rating
| | 14 | | // Total al finalDe la misma manera que con el GROUP BY y GROUP BY ROLLUP, podemos agrupar los datos por varios campos.
SELECT Rating, Type, Industry, COUNT(Name) cnt
FROM Account
GROUP BY CUBE(Rating, Type, Industry)8 Queries dinámicas
En ocasiones nos surgirá la necesidad de construir nuestras consultas de manera dinámica. Puede que los campos en el SELECT o los filtros que se aplican a la consulta dependan de alguna acción del usuario en la pantalla, y hasta que el usuario no realice esa acción no sabemos la consulta exacta que tenemos que lanzar.
La única manera de cumplir este requisito es construir esa consulta de manera dinámica. La alternativa sería escribir todas las posibles consultas que se pudieran lanzar en nuestro código y tener tantas condiciones como consultas para lanzar una u otra.
Veamos un ejemplo de cómo construir una query dinámica:
String soqlQuery = 'SELECT Id FROM Account';
if (filterBy == 'Type') {
soqlQuery += ' WHERE Type = \'Prospect\'';
} else if (filterBy == 'Rating') {
soqlQuery += ' WHERE Rating = \'Hot\'';
}
List<sObject> queryResult = Database.query(soqlQuery);De la misma manera que en las SOQL estáticas podemos hacer referencia a variables en nuestros filtros poniéndole “:” antes del nombre de la variable:
String myAccount = 'Account Test';
List<sObject> queryResult = Database.query('SELECT Id FROM Account WHERE Name = :myAccount');Hay que tener en cuenta que las queries dinámicas siempre nos devolverá una lista de sObjects genéricos. Está en nuestras manos convertir esa lista en una lista de objectos específicos.
List<sObject> queryResult = Database.query(soqlQuery);
List<Account> accountList = (List<Account>) queryResult;Realizar las queries de manera dinámica puede ser muy útil pero también puede introducir una vulnerabilidad de seguridad. Un usuario podría introducir un valor de los que usamos para construir la query dinámicamente y añadir contenido a esa query. Para minimizar el riesgo de esto podemos usar el método “escapeSingleQuotes” para sanear la información introducida por el usuario.
Tip adicional: No necesitamos escribir el Id en SELECT de todas las consultas. En todas las consultas que hacemos en Salesforce nos devuelve el Id. De nada.
Hemos obviado muchas opciones que ofrecen las consultas SOQL por ser más básicas y ser generales en casi todos los motores de base de datos. Sólo hemos incluido cosas muy particulares de Salesforce y su lenguaje de consulta, funciones que uno no suele utilizar en el día a día, pero conocerlas nos puede solucionar un problema imprevisto.
Si te has quedado con ganas de más puedes ver la segunda parte: Queries Avanzadas en Salesforce (Parte 2). Sígueme para más artículos técnicos de Salesforce.
