Migration depuis SQL vers SOQL

Objectifs de formation

Une fois cette unité terminée, vous pourrez :

• Comprendre les avantages des objets Force.com.
• Identifier les similitudes et les différences entre SQL et SOQL.
• Créer une simple instruction SOQL en utilisant le Workbench.
• Créer des requêtes de relation plus complexes.
• Créer des requêtes agrégées.

Compréhension des objets Force.com

La plate-forme Force.com offre une base de données puissante dotée de nombreuses fonctionnalités qui facilitent et accélèrent la création d'applications. Puisque vous avez utilisé SQL Server, vous savez que les données sont stockées dans des tableaux et des lignes. Par contre, dans Force.com, la base de données utilise des objets pour stocker les données. Les objets contiennent toutes les fonctionnalités habituelles d'un tableau, avec des améliorations qui leur procurent davantage de puissance et de flexibilité. Chaque objet comprend plusieurs champs, qui correspondent aux colonnes d'une base de données. Les données sont stockées dans les enregistrements de l'objet, qui correspondent aux lignes d'une base de données. Attendez... ce n’est pas tout !

Il existe deux types d'objet :

1. Objets standard : ils correspondent aux objets intégrés à Salesforce. Parmi les objets CRM courants figurent Comptes, Contacts, Opportunités et Pistes.
2. Objets personnalisés : ils correspondent aux nouveaux objets que vous créez pour stocker des informations spécifiques à votre application. Les objets personnalisés étendent les fonctionnalités qu'offrent les objets standard. Par exemple, si vous créez une application pour suivre un inventaire produit, vous pouvez créer des objets personnalisés appelés Marchandises, Commandes ou Factures.

Vous l'avez probablement deviné, les objets peuvent avoir des champs de relations qui définissent l'association entre les enregistrements de deux objets. Ce sont fondamentalement des clés primaires et étrangères, mais ils sont beaucoup plus flexibles, ce qui facilite la conception et l'implémentation de votre modèle de données.

Qu'ils soient standard ou personnalisés, les objets Force.com n'offrent pas seulement une structure de stockage des données. Ils implémentent également des éléments d'interface qui permettent aux utilisateurs d'interagir avec les données, notamment des onglets, la présentation des champs sur une page et des listes d'enregistrements associés. Pour une fonctionnalité standard, il n'est pas nécessaire d'implémenter une ORM (relation entre les objets), d'écrire une interface utilisateur pour des données CRUD (créées, lues, mises à jour, supprimées) ou de créer des tableaux. Cette fonctionnalité standard est automatiquement fournie par la plate-forme. Les objets ont également une prise en charge intégrée de fonctionnalités telles que la gestion de l'accès, la validation, des formules et le suivi historique. Tous les attributs d'un objet sont décrits à l'aide de métadonnées, qui facilitent la création et la modification d'enregistrements, que ce soit via une interface visuelle ou par programmation.

Comme vous pouvez le constater, les objets sont davantage que de simples conteneurs de données. Ils offrent un ensemble complet de fonctionnalités qui vous libèrent pour créer les fonctionnalités spécifiques à votre application. Pour plus d'informations sur la création d'objets personnalisés, de champs, de relations, et davantage, suivez le module Modélisation des données.

Semblables mais pas identiques

En tant que développeur .NET, vous maîtrisez probablement l'utilisation de SQL Server. Vous connaissez certainement l'écriture de requêtes appropriées en utilisant SQL. Pour vous présenter un langage similaire conçu spécialement pour Salesforce et appelé SOQL (ou Salesforce Object Query Language), nous avons pensé que le plus efficace était de comparer les deux.

Tout d'abord, bien qu'ils désignent tous les deux des langages de requête, SOQL est utilisé uniquement pour exécuter des requêtes avec l'instruction SELECT. SOQL n'a aucune instruction INSERT, UPDATE et DELETE équivalente. Dans l'univers Salesforce, la manipulation des données est gérée en utilisant un ensemble de méthodes appelées DML (langage de manipulation de données). Nous reviendrons plus loin sur le DML. Pour le moment, vous devez savoir comment interroger les données Salesforce en utilisant l'instruction SELECT que fournit SOQL.

Vous allez immédiatement remarquer une différence importante : SOQL n’utilise pas SELECT *. En effet, SOQL renvoie les données Salesforce qui résident dans un environnement mutualisé dans lequel tout le monde « partage la base de données ». Par conséquent, un caractère générique tel que * entraîne des problèmes. Honnêtement, il est trop facile de déclencher une nouvelle requête SQL et de saisir SELECT * FROM SOME-TABLE, notamment lorsque vous ne connaissez pas les noms de champ du tableau. Cette action pourrait gêner considérablement les autres locataires de vos environnements partagés. Oseriez-vous passer la tondeuse le dimanche matin à 7h00 ? Ce serait impoli et irrespectueux.

Dans SOQL, vous spécifiez chaque nom de champ à renvoyer. De plus, l'instruction SELECT fournie par SOQL est semblable à SQL. L'écriture de requêtes SOQL est extrêmement simple. Voici les principaux éléments à connaître : SQL et SOQL sont similaires, mais pas identiques. SOQL ne prend pas en charge certaines fonctionnalités avancées prises en charge par l'instruction SQL SELECT. Cependant, sur la plate-forme Salesforce, ces fonctionnalités supplémentaires ne sont pas véritablement nécessaires. SOQL offre uniquement les éléments dont vous avez besoin.

Création d'une requête avec le Workbench

Concernant l'écriture de requêtes SOQL, vous vous demandez peut-être « Comment procéder ? » Pour faciliter vos premiers pas, vous pouvez utiliser l'outil Web Salesforce appelé Workbench. Nous savons que les développeurs .NET aiment les outils. Cet outil puissant offre aux administrateurs et aux développeurs de nombreuses méthodes pour accéder à leur organisation en utilisant des API Force.com.

Pour le moment, nous allons nous contenter d'utiliser le Workbench pour créer des requêtes SOQL. Cependant, si vous avez un peu de temps, explorez le Workbench pour découvrir toutes ses fonctionnalités. Nous pensons que vous allez apprécier. Inscrivez-vous pour obtenir une organisation Developer Edition (DE) gratuite, puis :

1. Accédez à https://workbench.developerforce.com/login.php.
2. Pour Environnement, sélectionnez Production.
3. Sélectionnez la toute dernière version de l'API dans le menu déroulant API Version.
4. Acceptez les conditions du service, cliquez sur Login with Salesforce.
5. Saisissez vos identifiants de connexion, puis cliquez sur Log in.
6. Pour permettre au Workbench d'accéder à vos informations, cliquez sur Allow.
7. Une fois connecté(e), sélectionnez requêtes > Requête SOQL.
8. Sélectionnez l'objet Account.

Notez que vous choisissez un objet, pas un tableau. Les données sont permanentes dans des objets. Ils sont appelés sObjects, comme dans les objets Salesforce, et ils sont étroitement intégrés à la plate-forme Force.com, ce qui facilite leur utilisation.

1. Maintenez la touche Ctrl enfoncée et sélectionnez CreatedDate, Name, Phone, Type dans la liste Fields. À mesure que vous sélectionnez les objets et les champs, la requête SOQL est créée pour vous dans la zone de texte. Elle se présente comme suit :

   SELECT CreatedDate, Name, Phone, Type FROM Account
   

2. Cliquez sur Query pour afficher les résultats renvoyés dans une liste. Dans les résultats, examinez la valeur renvoyée dans le champ CreatedDate.

La mauvaise nouvelle est que les champs de date/heure dans Salesforce sont aussi complexes et difficiles à utiliser avec SOQL que dans SQL. La bonne nouvelle est que Salesforce offre plusieurs fonctions de date qui sont plus faciles à utiliser avec SOQL. En parcourant la documentation, observez comment SOQL gère les champs de devise, car ils sont légèrement différents, notamment pour les organisations qui travaillent avec plusieurs devises.

Filtrage de vos résultats

SOQL contient uniquement deux clauses requises : SELECT et FROM. La clause WHERE est facultative. Cependant, en tant que bon développeur (ce dont nous ne doutons pas), vous souhaitez inclure une clause WHERE pour la plupart des requêtes que vous écrivez. Il est inutile de renvoyer davantage de données que nécessaire.

Encore une fois, pour observer le fonctionnement, le plus simple est d'utiliser le Workbench.

1. Une fois connecté(e) au Workbench, sélectionnez requêtes > Requête SOQL.
2. Sélectionnez l'objet Contact.
3. Maintenez la touche Ctrl enfoncée et sélectionnez AccountId, Email, Id, LastName dans la liste Fields. À mesure que vous sélectionnez les objets et les champs, la requête SOQL est créée pour vous dans la zone de texte.
4. Cliquez sur Query pour afficher les résultats renvoyés dans une liste. Elle se présente comme suit :

   SELECT AccountId, Email, Id, LastName FROM Contact
   

Cette requête renvoie tous les contacts de votre organisation. Pour une organisation de développement, cette liste peut être petite. Pour la plupart des organisations réelles, le nombre de contacts renvoyés peut atteindre des milliers. Par conséquent, vous devez toujours penser à filtrer vos requêtes SOQL avec une clause WHERE, notamment celles utilisées dans un code Apex.

1. Dans la liste déroulante Filter results by, sélectionnez Email.
2. Appuyez sur la touche Tabulation pour passer au champ suivant, puis cliquez sur la flèche de la case déroulante des opérateurs.
3. Sélectionnez contains dans la liste, appuyez sur Tabulation pour passer au dernier champ, puis saisissez .net. La requête construite se présente comme suit

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

Avez-vous remarqué ? Votre requête construite ne comprend pas le terme contains. Elle utilise à la place LIKE. La requête inclut en outre les guillemets droits requis (car Email est un champ de texte), ainsi que des signes de pourcentage de début et de fin pour indiquer qu'il s'agit d'une recherche de caractères génériques.

Remarque : L'utilisation de caractères génériques, notamment de début et de fin comme dans l'exemple ci-dessus, est recommandée. Nous présenterons la création de requêtes efficaces dans une prochaine unité, mais pour le moment, essayez d'éviter autant que possible les recherches de caractères génériques.

1. Profitez-en pour trier les résultats par LastName en sélectionnant LastName dans la liste déroulante Sort results by.
2. Laisser les autres paramètres par défaut définis sur A to Z et Nulls First. Votre requête se présente désormais comme suit :

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

3. Cliquez sur Query pour renvoyer les résultats dans une liste classée.

Les éléments importants que nous souhaitons vous montrer dans les résultats sont les deux champs ID : AccountId et Id. Ils contiennent une chaîne unique de 18 caractères attribuée par la plate-forme lors de la création des enregistrements Compte et Contact. Le champ AccountId est associé à l'enregistrement Compte auquel ce Contact particulier a été attribué. En termes SQL, il s'agit d'une relation de clé étrangère. Le champ Id est associé au contact. En termes SQL, il représente la clé primaire.

Pendant que nous évoquons les relations de clé étrangère, vous vous demandez probablement comment joindre des tableaux en SOQL. La réponse est que cette opération n'est pas possible, car SOQL n'a pas de clause JOIN équivalente. Ne vous inquiétez pas, nous pensons que c'est une bonne chose.

Un type de jointure différent

Vous ne serez pas surpris(e) d'apprendre que Salesforce adopte une approche différente de celle à laquelle vous êtes habitué(e) pour combiner des objets, ou des tableaux. Au lieu de les combiner avec une clause JOIN, vous écrivez ce que l'on appelle des requêtes de relation.

Nous vous entendons presque demander « Que sont ces requêtes de relation ? ». Bonne question.

Salesforce utilise une relation parent-enfant pour combiner deux objets. De la même façon qu'en SQL, SOQL utilise une clé étrangère pour associer ces deux objets, mais la syntaxe de requête est différente en SOQL. Nous n'allons pas vous mentir. Au premier abord, l'utilisation de cette nouvelle syntaxe peut sembler étrange, car vous travaillez avec des objets au lieu de lignes. Néanmoins, une fois habitué(e) aux concepts de base, vous allez constater que l'écriture de requêtes de relation est beaucoup plus simple que les jointures que vous écrivez en SQL.

SOQL inclut deux types de requête de relation de base que vous devez mémoriser :

• Enfant vers parent
• Parent vers enfant

Chacune fonctionne de façon différente. Comme nous avons déjà examiné des champs des objets Compte et Contact, qui sont le plus souvent combinés, nous allons commencer par eux. Pour le moment, il est important de savoir que Compte est le parent et Contact est l'enfant.

Écriture d'une requête enfant vers parent

Supposons que vous souhaitez écrire une requête qui renvoie les informations de Compte et de Contact. La première possibilité consiste à écrire une requête enfant vers parent. Cette requête de relation utilise une « notation par points » pour accéder aux données du parent, c'est-à-dire qu'un point sépare le nom de la relation du nom du champ interrogé.

Pour observer son fonctionnement, nous allons écrire une requête de relation qui renvoie une liste de contacts avec le nom du compte associé. Par contre, au lieu d’avoir recours au Workbench, nous allons ici utiliser l’onglet Query Editor de la Developer Console.

1. Cliquez sur Configuration, puis sur Developer Console.
2. Dans la Developer Console, cliquez sur l'onglet Query Editor dans le volet inférieur.
3. Supprimez le code existant, puis insérez l'extrait suivant :

   SELECT FirstName, LastName, Account.Name FROM Contact
   

4. Cliquez sur Exécuter.
5. Les résultats de la requête comprennent trois colonnes. Parcourez les résultats. Certains résultats sous le champ Account.name sont null, car les contacts ne sont pas tous associés à un compte.

Puisque vous réfléchissez probablement encore en termes SQL, imaginez ce scénario. Supposons que vous avez deux tableaux SQL nommés Compte et Contact, associés par une relation un à plusieurs. Comment écrivez-vous une requête SQL qui renvoie la même information ?

Vous utilisez bien entendu une jointure. Cependant, dans ce cas, vous avez besoin d'une jointure externe droite, car vous souhaitez une requête équivalente qui renvoie tous les contacts, même ceux qui ne sont pas associés à un compte. Elle peut se présenter comme suit :

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

Nous souhaitons maintenant que vous examiniez la requête SOQL équivalente, à savoir :

    SELECT FirstName, LastName, Account.Name FROM Contact

La requête SOQL semble beaucoup plus simple, n'est-ce pas ?

Honnêtement, les requêtes de relation peuvent parfois s'avérer complexes, notamment pour déterminer le nom de relation entre des objets personnalisés. Pour en savoir plus sur la conversion des requêtes SQL en requêtes SOQL, regardez cette vidéo de formation pratique.

Concernant ces types de requête, il est important de savoir que la notation par point permet de traverser jusqu'à cinq niveaux. Vous pouvez ainsi aller de l'enfant au parent, au grand-parent, à l'arrière-grand-parent, et ainsi de suite.

Écriture de requêtes parent vers enfant

Les requêtes parent vers enfant utilisent également le nom de la relation, mais dans ce que l'on appelle une requête select imbriquée. Comme dans le type de requête précédent, il est plus facile de présenter une requête parent vers enfant en prenant un exemple.

Nous allons écrire une requête depuis l'objet Compte parent et inclure une requête imbriquée qui renvoie également des informations sur chaque contact associé.

1. Dans la Developer Console, cliquez sur l'onglet Query Editor dans le volet inférieur.
2. Supprimez le code existant, puis insérez l'extrait suivant :

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

Dans la requête imbriquée, le nom de la relation utilise le terme Contacts au pluriel, pas Contact. Ce détail est important à comprendre, car il piège souvent les personnes. Lors de l'utilisation de requêtes de relation, le nom de la relation parent vers enfant doit être au pluriel.

Lors de l'utilisation d'objets personnalisés, non seulement le nom de la relation est au pluriel, mais deux traits de soulignement et un r lui sont ajoutés. Par exemple, le nom de la relation de l’objet personnalisé Mon_Objet__c est Mes_Objets__r.

1. Cliquez sur Exécuter.
2. Les résultats de la requête comprennent deux colonnes. Dans l'image ci-dessous, notez l'affichage des résultats sous la colonne Contact. Chaque compte est généralement associé à plusieurs contacts. Par conséquent, le premier nom et le dernier nom sont affichés sous le format JSON.

Examinons là encore l'écriture de la même requête en SQL. Pour cette requête, l'équivalent SQL est une jointure externe gauche semblable à la suivante :

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

La requête SQL renvoie tous les enregistrements de Compte et tous les contacts associés à ces comptes. Le résultat renvoyé en SQL n'est pas formaté en JSON. Sinon, les requêtes SQL et SOQL génèrent les mêmes résultats.

À ce stade, vous vous demandez peut-être si SOQL prend en charge l'utilisation d'alias. La réponse est oui, mais pas de la même façon qu'en SQL. SOQL n'a aucun mot-clé AS. Vous pouvez utiliser des alias pour représenter des noms d'objet dans des requêtes SOQL. Cependant, avec des noms de champ, les alias fonctionnent uniquement pour des requêtes agrégées que nous allons examiner.

Pour en savoir plus, suivez les liens des vidéos de formation pratique dans la section Ressources.

Qu'en est-il des agrégations ?

SOQL comprend des agrégations dont le fonctionnement est plutôt classique. Presque... Lors de l'utilisation d'agrégations, il est important de noter que pour la plupart des fonctions votre résultat est renvoyé sous forme de type AggregateResult.

En SOQL, vous pouvez utiliser les fonctions indiquées dans le tableau ci-dessous. Pour plus d'informations sur chaque fonction, reportez-vous aux documents officiels.

Fonctions d’agrégation SOQL

Pour obtenir le nombre d'enregistrements d'un tableau particulier nommé Account en SQL, vous utilisez :

SELECT COUNT(*) FROM Account

En SOQL, la même requête se présente comme suit :

SELECT COUNT() FROM Account

Elles se ressemblent, n'est-ce pas ?

Leurs différences dépendent de la version de la fonction count utilisée, qui renvoie des éléments différents. La fonction COUNT() sans nom de champ est une ancienne version qui était disponible avant les autres fonctions d'agrégation. Elle renvoie un nombre entier et est semblable à la fonction count(*) proposée dans SQL.

Count(fieldName) est une version plus récente qui renvoie le nombre de lignes dans lesquelles fieldName a une valeur non nulle. La différence est qu'elle renvoie ce résultat sous forme de liste AggregateResults, pas en tant que valeur unique.

Examinons-la en action.

1. Dans la Developer Console, cliquez sur l'onglet Query Editor dans le volet inférieur.
2. Supprimez le code existant, puis insérez l'extrait suivant :

   SELECT COUNT() FROM Account
   

3. Cliquez sur Exécuter. Les résultats de la requête indiquent le nombre total de lignes avec un numéro.
4. Revenez à l'onglet Query Editor et modifiez la requête comme suit :

   SELECT COUNT(Id) FROM Account
   

5. Cliquez sur Exécuter. Les résultats de la requête renvoient maintenant une seule ligne et une colonne qui affiche le nombre total d'enregistrements.

Jusqu'à présent, nous n'avons pas examiné la gestion des données renvoyées à partir de vos requêtes SOQL. Cette étape est inévitable. Préparons-nous à explorer quelques données agrégées.

Nous allons utiliser un code Apex plus complexe. Ne vous inquiétez pas si vous ne saisissez pas la logique au premier abord. Nous reviendrons sur les détails plus loin.

1. Dans la Developer Console, sélectionnez Débogage > Ouvrir une fenêtre d’exécution anonyme.
2. Supprimez le code existant, puis insérez l'extrait suivant :

   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'));
   }
   

Notez que nous avons utilisé un alias pour représenter le total avec la clause GROUP BY. En SOQL, vous pouvez représenter les champs avec des alias uniquement dans des requêtes agrégées qui utilisent la clause GROUP BY.

1. Assurez-vous qu'Open Log est sélectionnée, puis cliquez sur Execute. Un onglet affiche le journal d'exécution.
2. Sélectionnez l'option Debug Only pour afficher uniquement les instructions de débogage du journal.

En savoir plus

En plus de la clause GROUP BY, SOQL offre d'autres clauses de regroupement, notamment GROUP BY ROLLUP, GROUP BY CUBE et GROUPING. Ces clauses sont utiles pour inspecter les résultats lorsqu'une requête renvoie les valeurs de plusieurs tableaux associés. GROUP BY CUBE est une jolie petite clause qui permet d'ajouter les sous-totaux de toutes les combinaisons de champ groupés dans les résultats de requête. Pour plus d’informations sur ces clauses, consultez le document GROUP BY dans les Ressources.

Les fonctions d'agrégation prennent en charge une clause facultative HAVING qui est similaire à la clause HAVING dans SQL Server. Elle devrait par conséquent vous être familière. Elle permet de filtrer les résultats renvoyés par une fonction d'agrégation. Pour en savoir plus, suivez les liens de la section Ressources.

Ressources

• SOQL SELECT Syntax dans Force.com SOQL and SOSL Reference
• Count and Count(fieldName) dans Force.com SOQL and SOSL Reference
• Requêtes SOQL et SOSL