Pasar de SQL a SOQL

Objetivos de aprendizaje

Después de completar esta unidad, podrá:

• Comprender los beneficios de los objetos de Force.com.
• Identificar las similitudes y las diferencias entre SQL y SOQL.
• Construir una declaración SOQL sencilla utilizando Workbench.
• Construir consultas de relaciones más complicadas.
• Construir consultas agregadas.

Concepto de objetos de Force.com

La plataforma Force.com proporciona una poderosa base de datos, con muchas funciones que la hacen rápida y fácil para crear aplicaciones. Al haber tratado con SQL Server, sabe que los datos se almacenan en tablas y filas. Por otro lado, la base de datos en Force.com utiliza objetos para almacenar datos. Los objetos contienen todas las funciones que espera de una tabla, con mejoras adicionales que la hacen más potente y versátil. Cada objeto se compone de un número de campos, que se corresponden con columnas en una base de datos. Los datos se almacenan en registros del objeto, que se corresponden con filas en una base de datos. Pero espere... ¡Hay más!

Hay dos tipos de objetos:

1. Objetos estándar: Son objetos que vienen incorporados con Salesforce. Algunos objetos de CRM comunes incluyen Cuentas, Contactos, Oportunidades y Candidatos.
2. Objetos personalizados: Objetos nuevos que crea para almacenar información exclusiva para su aplicación. Los objetos personalizados amplían la funcionalidad que los objetos estándar proporcionan. Por ejemplo si está elaborando una aplicación para hacer un seguimiento de un inventario de productos, podría crear objetos personalizados denominados Mercancías, Pedidos o Facturas.

Como probablemente adivinó, los objetos pueden tener campos de relación que definan la forma de relacionar los registros en un objeto con registros de otro objeto. Estos son esencialmente claves principales y externas, pero son mucho más flexibles, lo que facilita el diseño y la implementación de su modelo de datos.

Tanto si son estándar o personalizados, los objetos de Force.com no solo proporcionan estructuras para almacenar datos sino que también potencian los elementos de la interfaz que permiten a los usuarios interactuar con los datos, como fichas, el formato de los campos de una página, así como las listas de registros relacionados. Para las funciones estándar no es necesario implementar un ORM, redactar una interfaz de usuario para aplicar CRUD a los datos o construir tablas. Estas funciones estándar las proporciona la plataforma de forma automática para usted. Los objetos también tienen compatibilidad incorporada para funciones como gestión de acceso, validación, fórmulas y seguimiento de historiales. Todos los atributos de un objeto están descritos con metadatos, lo que facilita la creación y modificación de registros ya sea a través de una interfaz visual o a través de programación.

Como puede ver, los objetos son mucho más que simples contenedores para almacenar datos. Proporcionan un completo conjunto de funciones que le libera para que se centre en la confección de las funciones exclusivas de su aplicación. Para obtener más información sobre cómo crear objetos personalizados, campos, relaciones y mucho más, consulte el módulo Modelado de datos.

Parecidos pero no iguales

Como desarrollador de .NET, es muy probable que se sienta cómodo trabajando con SQL Server. Y también que esté familiarizado con la redacción de consultas ad hoc utilizando SQL. De este modo pensamos que la mejor manera de presentarle un lenguaje similar diseñado específicamente para Salesforce denominado SOQL, o Salesforce Object Query Language (Lenguaje de consulta de objetos de Salesforce), era comparar los dos.

Lo primero que hay que saber es que, aunque ambos se denominan lenguajes de consulta, SOQL solo se utiliza para realizar consultas con la declaración SELECT. SOQL no tiene equivalentes de las declaraciones INSERT, UPDATE y DELETE. En el mundo de Salesforce, la manipulación de los datos se controla empleando un conjunto de métodos denominado Data Manipulation Language (DML, Lenguaje de manipulación de datos). Hablaremos más sobre DML en breve. Por ahora, solo necesita saber cómo consultar datos de Salesforce empleando la declaración SELECT que proporciona SOQL.

Una gran diferencia que notará de inmediato es que SOQL no cuenta con SELECT *. Como SOQL devuelve datos de Salesforce, y dichos datos residen en un entorno multiusuario donde todos comparten la base de datos, un carácter comodín como * puede ocasionar problemas. Hablando con sinceridad, es demasiado fácil realizar una nueva consulta SQL e ingresar SELECT * FROM SOME-TABLE, en especial cuando no sabe cuáles son los nombres de los campos de la tabla. Esta acción podría afectar gravemente a otros inquilinos de sus entornos compartidos. ¿Cortaría su césped el domingo a las 7 de la mañana? Eso es de mala educación y muestra falta de respeto.

En SOQL, usted especifica cada nombre de campo que hay que devolver. Más allá de eso, la declaración SELECT que proporciona SOQL es similar a SQL. Encontrará que redactar consultas SOQL es bastante sencillo. Pero aquí está lo que necesita saber: Aunque SQL y SOQL son parecidos, no son iguales. SOQL no admite algunas de las funciones más avanzadas que la declaración SELECT de SQL aporta. Pero en la plataforma Salesforce, no se necesitan realmente estas funciones adicionales. SOQL ofrece justo lo que necesita de una forma que le hace sentir en casa.

Creación de una consulta con Workbench

Hablando de redactar consultas SOQL, ahora podría estar pensando “Bien, ¿cómo hago eso?”. Una manera sencilla de empezar a trabajar es utilizar la herramienta basada en web de Salesforce denominada Workbench. Sabemos lo mucho que les gustan las herramientas a los desarrolladores de .NET, y esta potente herramienta ofrece muchas formas de acceder a las organizaciones de administradores y desarrolladores empleando las API de Force.com.

Por ahora, nos centraremos en el uso de Workbench para crear consultas SOQL, pero si tiene algo de tiempo libre, eche un vistazo a Workbench y todo lo que ofrece. Creemos que le gustará. Suscríbase para obtener una Developer Edition (DE) gratuita y luego:

1. Navegue hasta https://workbench.developerforce.com/login.php.
2. Para Environment (Entorno), seleccione Production (Producción).
3. Seleccione la versión de la API más reciente desde el menú desplegable API Version.
4. Acepte las condiciones del servicio y haga clic en Login with Salesforce (Iniciar sesión con Salesforce).
5. Ingrese sus credenciales de inicio de sesión y haga clic en Iniciar sesión.
6. Para permitir que Workbench acceda a su información, haga clic en Allow (Permitir).
7. Después de iniciar sesión, seleccione queries > SOQL Query (consultas | Consulta SOQL).
8. Seleccione Account (Cuenta) como el objeto.

Observe que eligió un objeto y no una tabla. Los datos persisten dentro de los objetos. En realidad, se llaman sObjects, como Objetos de Salesforce, y están integrados estrechamente con la plataforma Force.com, que facilita el trabajo con ellos.

1. Mantenga pulsada la tecla Ctrl y seleccione CreatedDate, Name, Phone, Type (Fecha de creación, Nombre, Teléfono, Tipo) de la lista de campos. A medida que selecciona los objetos y los campos, la consulta SOQL se crea por usted en el cuadro de texto. Tiene el siguiente aspecto:

   SELECT CreatedDate, Name, Phone, Type FROM Account
   

2. Haga clic en Query (Consultar) para ver los resultados devueltos como una lista. En los resultados, mire el valor devuelto en el campo CreatedDate.

La mala noticia es que los campos de fecha y hora en Salesforce son tan complicados y difíciles para el trabajo en SOQL como en SQL. La buena noticia es que Salesforce ofrece varias funciones de fecha que hace que el trabajo con ellas en SOQL sea menos complicado. Y mientras está echando un ojo a la documentación, consulte cómo SOQL controla los campos de divisa, ya que estos también son un poco diferentes, especialmente para organizaciones que tratan con varias divisas.

Filtrado de sus resultados

SOQL solo tiene dos cláusulas obligatorias: SELECT y FROM. La cláusula WHERE es opcional. Pero como buen desarrollador (que estamos seguros que es su caso), desea incluir una cláusula WHERE en casi cada consulta que redacte. No tiene sentido devolver más datos de los necesarios.

Una vez más, la forma más sencilla de ver cómo funciona esto es utilizando Workbench.

1. Después de iniciar sesión en Workbench, seleccione queries > SOQL Query (consultas | Consulta SOQL).
2. Seleccione Contact (Contacto) como el objeto.
3. Mantenga pulsada la tecla Ctrl y seleccione AccountId, Email, Id, LastName (Id. de cuenta, Email, Id. Apellidos) de la lista de campos. A medida que selecciona los objetos y los campos, la consulta SOQL se crea por usted en el cuadro de texto.
4. Haga clic en Query (Consultar) para ver los resultados devueltos como una lista. Tiene el siguiente aspecto:

   SELECT AccountId, Email, Id, LastName FROM Contact
   

Esta consulta devuelve todos los contactos de su organización. Para una organización de desarrollo, esta lista podría ser pequeña. Pero para la mayoría de las organizaciones del mundo real, el número de contactos devueltos podrían ser miles, y este es el motivo por el que siempre debería considerar filtrar sus consultas SOQL con una cláusula WHERE, especialmente aquéllas empleadas en código Apex.

1. Desde la lista desplegable Filter results by (Filtrar resultados por), seleccione Email.
2. Pase con la tecla de tabulación al siguiente campo y haga clic en la flecha del cuadro desplegable para ver una lista de operadores posibles.
3. Seleccione contains (contiene) de la lista y luego pase con la tecla de tabulación al último campo e ingrese .net. La consulta construida tiene el siguiente aspecto:

   SELECT AccountId, Email, Id, LastName FROM Contact WHERE Email LIKE '%.net%'
   

¿Se dio cuenta de lo que pasó? Su consulta construida no incluye la palabra contains. En su lugar, utiliza LIKE. La consulta también incluye las comillas simples necesarias (porque Email es un campo de texto), así como los signos de porcentaje iniciales y finales para indicar que es una búsqueda con caracteres comodín.

Nota: El uso de comodines, especialmente los comodines iniciales y finales como en el anterior ejemplo no se considera una mejor práctica. Aprenderá más sobre cómo crear consultas eficientes en una unidad posterior, pero por ahora tenga en cuenta evitar las búsquedas con caracteres comodín como estas siempre que sea posible.

1. Mientras está en ello, ordene los resultados por LastName seleccionando LastName (Apellidos) desde la lista desplegable Sort results by (Ordenar resultados por).
2. Deje los otros valores predeterminados de A to Z y Nulls First. Su consulta ahora tiene un aspecto como este:

   SELECT AccountId,Email,Id,LastName FROM Contact
     WHERE Email LIKE '%.net%' ORDER BY LastName ASC NULLS FIRST
   

3. Haga clic en Query (Consultar) para devolver los resultados como una lista ordenada.

Lo importante que queremos que advierta en los resultados son dos campos de identificación: AccountId e Id. Estos campos contienen una cadena exclusiva de 18 caracteres que asignó la plataforma cuando se crearon los registros Account y Contact. El campo AccountId está asociado con el registro Account al que este Contact en particular se asignó. En términos SQL, es una relación de clave externa. El campo Id está relacionado con el contacto. En términos SQL, representa la clave principal.

Mientras sacamos a colación relaciones de claves externas, es posible que se esté preguntando ahora cómo unir tablas en SOQL. La respuesta breve es que no se hace. SOQL no tiene una cláusula JOIN equivalente. Pero no se preocupe, creemos que esto es bueno.

Un tipo diferente de unión

Estamos seguros de que no se sorprenderá al leer esto, pero Salesforce hace las cosas de una manera un poco diferente cuando se trata de combinar objetos o tablas, según está acostumbrado a pensar en ellos. En vez de combinar tablas con una cláusula JOIN, usted redacta lo que se conoce como consultas de relación.

Podemos escuchar cómo se pregunta “¿Y esas qué son?”. ¡Nos complace que preguntara!

Salesforce utiliza una relación principal-secundario para combinar dos objetos. Del mismo modo que en SQL, SOQL utiliza una clave externa para relacionar estos dos objetos, pero en SOQL la sintaxis de las consultas es diferente. No vamos a mentirle. Al principio probablemente se sentirá un poco extraño al utilizar esta nueva sintaxis porque está trabajando con objetos en lugar de con filas. Pero una vez que se acostumbre a los fundamentos, encontrará que la redacción de consultas de relación es mucho más sencilla que las uniones que redacta en SQL.

SOQL tiene dos tipos de consultas de relación básicas que necesita recordar:

• Secundario a principal
• Principal a secundario

Cada uno funciona de forma diferente. Debido a que ya vio algunos campos para los objetos Account y Contact, que son de los que más se combinan, empezaremos por ellos. Lo importante que hay que saber es que Account es el objeto principal y Contact es el secundario.

Redactar una consulta Secundario a principal

Digamos que desea redactar una consulta que devuelve información de Account y Contact. Su primera opción es redactar una consulta secundario a principal. Esta consulta de relación utiliza una “notación de puntos” para acceder a los datos desde el principal, o sea, que un punto separa el nombre de la relación del nombre del campo que se está consultando.

Para ver cómo funciona esto, le guiaremos por la redacción de una consulta de relación que devuelve una lista de contactos, incluyendo el nombre de la cuenta asociada. Pero esta vez, en vez de utilizar Workbench, utilizaremos la ficha Query Editor (Editor de consultas) en Developer Console.

1. Haga clic en Setup (Configuración) y, luego, en Developer Console.
2. En Developer Console, haga clic en la ficha Query Editor en el panel inferior.
3. Elimine el código existente e inserte el siguiente fragmento de código:

   SELECT FirstName, LastName, Account.Name FROM Contact
   

4. Haga clic en Execute (Ejecutar).
5. Los resultados de la consulta incluyen tres columnas. Desplácese por los resultados. Algunos resultados bajo el campo Account.name son nulos porque no todos los contactos están relacionados con una cuenta.

Ya que sabemos que probablemente esté aún pensando en términos de SQL, imagine este escenario. Si tuviera dos tablas SQL denominadas Account y Contact con una relación de uno a muchos entre las tablas, ¿cómo redactaría una consulta SQL que devolviera esta misma información?

Utilizaría una unión, por supuesto. Pero en este caso, necesitaría una combinación externa derecha, ya que desea una consulta equivalente que devuelva todos los contactos, incluso aquellos no relacionados con una cuenta. Y tendría un aspecto similar al siguiente:

    SELECT c.FirstName, c.LastName, a.Name FROM Account a
    RIGHT JOIN Contact c ON (c.AccountId = a.Id)

Así que ahora queremos que vuelva y mire la consulta SOQL equivalente, que era, en caso de que no la recuerde:

    SELECT FirstName, LastName, Account.Name FROM Contact

La consulta SOQL tiene un aspecto mucho más sencillo, ¿no le parece?

Para ser honesto, las consultas de relación pueden ser un poco complicadas a veces, especialmente al determinar el nombre de la relación para objetos personalizados. Para aprender más sobre la conversión de consultas SQL a consultas SOQL, eche un vistazo a este video de capacitación práctica.

Una cosa súper importante que hay que tener en cuenta con estos tipos de consultas es que puede atravesar cinco niveles con la notación de puntos. Así que puede ir desde el secundario al principal al principal del principal y al principal de este, y así sucesivamente.

Redacción de consultas principal a secundario

Las consultas principal a secundario también utilizan el nombre de relación, pero lo hacen en lo que se denomina una consulta de selección anidada. Como con el último tipo de consulta, es mejor explicar esto mostrando un ejemplo.

Esta vez redactaremos una consulta desde el objeto Account principal y haremos que incluya una consulta anidada que también devuelve información sobre cada contacto asociado.

1. En Developer Console, haga clic en la ficha Query Editor en el panel inferior.
2. Elimine el código existente e inserte el siguiente fragmento de código:

   SELECT Name, (Select FirstName, LastName FROM Contacts) FROM Account
   

El nombre de relación dentro de la consulta anidada utiliza el nombre en plural Contacts, en contraste con Contact. Este importante comprender este detalle, y es la parte que normalmente hace tropezar. Cuando se trabaja con consultas de relación, el nombre de la relación principal a secundario debe ser un nombre en plural.

Cuando se trabaja con objetos personalizados, el nombre de la relación no solo está en plural, también se le agrega dos guiones bajos y una r. Por ejemplo, el nombre de la relación para el objeto personalizado My_Object__c es My_Objects__r.

1. Haga clic en Execute (Ejecutar).
2. Los resultados de la consulta incluyen dos columnas. Observe en la siguiente imagen cómo se muestran los resultados bajo la columna Contactos. Debido a que cada cuenta está asociada normalmente con múltiples contactos, los nombres y apellidos se muestran en formato JSON.

Una vez más, miremos cómo se redacta la misma consulta en SQL. Para esta consulta, el equivalente SQL es una combinación externa izquierda similar a la siguiente:

    SELECT a.Name, c.FirstName, c.LastName
    FROM Account a
    LEFT JOIN Contact c ON (a.Id = c.AccountId)

La consulta SQL obtiene todos los registros de Account y cualquier contacto asociado con esas cuentas. El resultado devuelto en SQL no tiene el formato JSON. A parte de eso, las consultas SQL y SOQL generan los mismos resultados.

En este punto, es posible que se esté preguntando si SOQL admite la creación de alias. Sí lo hace, pero no de la forma a la que está acostumbrado con SQL. SOQL no tiene la palabra clave AS. Puede utilizar alias para representar nombres de objeto en consultas SOQL, pero para los nombres de campos, la creación de alias solo funciona para consultas de funciones agregadas, que trataremos a continuación.

Recuerde, para profundizar más en este tema, consulte el vínculo del video de capacitación práctica de la sección Recursos.

¿Qué hay sobre las funciones agregadas?

Así es, SOQL tiene funciones agregadas, que funcionan de forma parecida a lo que podría esperar. Bueno, así así. Lo importante que hay que tener en cuenta cuando se trabaja con funciones agregadas es que para la mayoría de las funciones su resultado se devuelve como un tipo AggregateResult.

En lo referente a las funciones que puede utilizar, SOQL tiene las que se muestran en la siguiente tabla. Consulte los documentos oficiales para obtener datos específicos sobre cada función.

Funciones agregadas SOQL

Para obtener un conteo de registros de una tabla en particular denominada Account en SQL, hay que hacer algo parecido a lo siguiente:

SELECT COUNT(*) FROM Account

En SOQL, esta misma consulta tiene este aspecto:

SELECT COUNT() FROM Account

Hay bastante parecido, ¿verdad?

Sus diferencias dependen de qué versión de la función de conteo utilice, porque devuelven cosas diferentes. La función COUNT() sin un nombre de campo es una versión anterior que estaba disponible antes de otras funciones agregadas. Devuelve un entero y se parece más a la función count(*) que ofrece SQL.

Count(nombreCampo) es una versión más reciente que devuelve el número de filas donde nombreCampo tiene un valor que no es nulo. La diferencia es que devuelve los resultados como una lista de AggregateResults y no como un único valor.

Veamos un poco de esto en acción.

1. En Developer Console, haga clic en la ficha Query Editor en el panel inferior.
2. Elimine el código existente e inserte el siguiente fragmento de código:

   SELECT COUNT() FROM Account
   

3. Haga clic en Execute (Ejecutar). Los resultados de la consulta muestran el número total de filas con un número a su lado.
4. Vuelva a la ficha Query Editor y cambie la consulta para que sea así:

   SELECT COUNT(Id) FROM Account
   

5. Haga clic en Execute (Ejecutar). Ahora los resultados de la consulta muestran únicamente una fila devuelta y una columna que muestra el número total de registros.

Hasta este punto, no hablamos mucho sobre el modo de controlar los datos que se devuelven en sus consultas SOQL. ¿Pero por qué postergar lo inevitable? Remanguémonos nuestras camisas y pongámonos manos a la obra con algunos datos de funciones agregadas.

Vamos a encontrarnos con un poquito de Apex, pero no se preocupe si no lo entiende completamente aún. Trataremos esto con detalle más adelante.

1. En Developer Console, seleccione Debug > Open Execute Anonymous Window (Depurar | Abrir ventana de ejecución anónima).
2. Elimine el código existente e inserte el siguiente fragmento de código:

   List<AggregateResult> results  = [SELECT Industry, count(Id) total
       FROM Account GROUP BY Industry];
   for (AggregateResult ar :results) {
       System.debug('Industry:' + ar.get('Industry'));
       System.debug('Total Accounts:' + ar.get('total'));
   }
   

Observe cómo utilizamos un alias para representar el total junto con la cláusula GROUP BY. En SOQL, solo puede crear alias de campos en consultas de funciones agregadas que utilizan la cláusula GROUP BY.

1. Asegúrese de que Open Log (Abrir registro) está seleccionado y haga clic en Execute (Ejecutar). Se carga una ficha que le muestra el registro de ejecución.
2. Seleccione la opción Debug Only (Solo depurar) de modo que vea únicamente las declaraciones de depuración en el registro.

Más información

Además de la cláusula GROUP BY, SOQL ofrece otras cláusulas de agrupación, como GROUP BY ROLLUP, GROUP BY CUBE y GROUPING. Estas cláusulas son útiles para inspeccionar los resultados cuando una consulta devuelve valores de varias tablas relacionadas. GROUP BY CUBE es una estupenda cláusula que le permite agregar subtotales para todas las combinaciones de campos agrupados en los resultados de consultas. Para aprender más sobre estas cláusulas, consulte el documento GROUP BY en Recursos.

Las funciones agregadas también admiten una cláusula HAVING opcional que es parecida a la cláusula HAVING en SQL Server, de modo que estará familiarizado con esta. Básicamente le permite filtrar los resultados que devuelve una función agregada. Consulte los Recursos para aprender más.

Recursos

• SOQL SELECT Syntax en la Force.com SOQL and SOSL Reference
• Count and Count(fieldName) en la Force.com SOQL and SOSL Reference
• SOQL and SOSL Queries