Mémento SQL avec Ruby

Synbioz

Dans un précédent article, je partageais mon affection pour Sequel avec vous. Force est de constater qu'ActiveRecord est le choix qui est le plus souvent fait. Le non-choix devrais-je dire, car ActiveRecord est l'ORM inclus dans Rails. Notre article sur ARel a montré qu'il existait quand même, bien caché derrière la simplicité d'AR, un moteur de génération de SQL puissant.

Générer le SQL c'est une chose, après il faut être capable de l'exécuter et d'en extraire les résultats. Et là, le README d'ARel n'est pas d'une grande utilité. Dans cet article je voudrai faire un mémento qui explique concrètement comment exprimer du SQL en Ruby dans le contexte d'une application Ruby on Rails.

Cet article est publié avec l'aimable autorisation de Synbioz

L'article original peut être lu sur le blog de Synbioz : Memento SQL avec Ruby.

Commentez

- Titre : Mémento SQL avec Ruby

- Auteur : Synbioz

- Parution : 30 janvier 2013

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2013 Synbioz. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Prérequis

- Création

- Création de la base

- Création des tables

- Ajout d'index et de contraintes

- Modification des tables

- Insertions

- Associations

- Cas 1-to-1 et 1-to-N

- Bon à savoir

- Cas N-to-N

- Requêtes

- Chargement

- Sélection globale

- Sélection partielle

- Préchargement des relations

- Bon à savoir

- Agrégation

- 1er exemple

- 2e exemple

- Conditions

- Expressions

- Filtres préétablis

- Jointures

- Jointures par défaut

- Autres types de jointures

- Références

- Conclusion

Prérequis

Pour faire fonctionner les exemples ci-dessous je vais partir du principe que vous disposez de deux applications Rails l'une fonctionnant avec ActiveRecord et l'autre avec Sequel. Le code qui m'a servi de support pour cet article est disponible sur Github.

Création

Création de la base

Avant de créer une table il faut créer puis sélectionner la base à utiliser. En SQL, on voudrait écrire ce genre de chose :

CREATE DATABASE demo_dev DEFAULT CHARACTER SET utf8;
USE demo_dev;

Dans les conventions de Rails, on définit la base à utiliser dans le fichier config/database.yml :

development:
adapter: mysql2
username: root
password:
host: localhost
encoding: utf8
database: demo_dev

Une fois configurée, on peut la créer grâce à la commande suivante :

bundle exec rake db:create

L'utilisation de la base demo_dev est automatique par la suite.

Selon l'ORM et le SGBD utilisés, il peut être nécessaire de faire des réglages supplémentaires. Par exemple, j'ai dû ajouter un initializer pour configurer l'adaptateur MySQL de Sequel :

# config/initializers/sequel.rb
Sequel::MySQL.default_engine  = 'InnoDB'
Sequel::MySQL.default_charset = 'utf8'
Sequel::MySQL.default_collate = 'utf8_unicode_ci'

Par défaut, Sequel n'utilise pas InnoDB mais MyISAM plus rapide mais disposant de moins de fonctionnalités. J'en ai profité pour spécifier l'encodage, mais ce n'est pas nécessaire puisqu'il est déjà présent dans le fichier config/database.yml.

Création des tables

Dès lors que l'on a une base la seconde chose que l'on va vouloir faire c'est de créer des tables. Prenons l'exemple classique d'une table contenant des articles de blog. Ce qui donnera en SQL :

CREATE TABLE posts (
id int(11) NOT NULL AUTO_INCREMENT,
author varchar(100) NOT NULL,
title varchar(100) NOT NULL,
content text NOT NULL,
PRIMARY KEY (id)
);

Pour créer des tables en SQL, Ruby on Rails utilise un système de migration. Ce système permet d'effectuer des opérations et leurs inverses, par exemple, créer la table posts et supprimer la table posts. On commence par générer une migration avec la commande :

bundle exec rails generate migration CreatePostTable

Cette commande va fonctionner tant pour ActiveRecord que pour Sequel. Elle donnera lieu à un fichier nommé db/migrate/YYYYMMDDhhmmss_create_post_table.rb où YYYYMMDDhhmmss correspond au format de la date de génération de la migration. Cette date permettra de jouer ou d'annuler les migrations dans leur ordre de création.

On notera qu'ActiveRecord crée par défaut une clé primaire nommée id lors de la création d'une table. Avec ActiveRecord on modifie le fichier généré de la manière suivante :

class CreatePostTable < ActiveRecord::Migration
def change
create_table :posts do |t|
t.string :author,  null: false, limit: 100
t.string :title,   null: false, limit: 100
t.text   :content, null: false
end
end
end

Avec Sequel le fichier et le DSL changent légèrement de format.

Sequel.migration do
change do
create_table :posts do
primary_key :id
String :author,  null: false, size: 100
String :title,   null: false, size: 100
String :content, null: false, text: true
end
end
end

Une fois la migration écrite, il faut l'exécuter sur la base de données. Pour cela on utilise la commande suivante :

bundle exec rake db:migrate

Ajout d'index et de contraintes

Avec Ruby on Rails, les contraintes sont exprimées grâce à la validation de l'ORM plutôt qu'avec de véritables contraintes SQL. C'est un choix défendable. Il me semble parfois pertinent d'assurer la cohérence des données à tous les niveaux. Il y a des situations où ça me semble indispensable : lorsqu'une application tierce peut manipuler les données par exemple. Pour des raisons de performance, il est également indispensable de pouvoir ajouter des index.

CREATE INDEX index_posts_on_author ON posts (author);

ALTER TABLE posts ADD UNIQUE index_posts_on_title (title);

La première ligne permet un accès rapide lors d'une recherche par auteur. La seconde assure que chaque titre d'article est unique.

Ruby on Rails permet les modifications de schéma au travers de migrations, tout comme on l'a vu pour la création d'une table. C'est donc grâce la commande ci-dessous que nous allons déclarer cet index et cette contrainte. Le nom de la migration est assez verbeux mais par la suite lorsqu'il y en aura des dizaines, voir des centaines, ce sera un plus pour s'y retrouver.

bundle exec rails generate migration AddAuthorIndexAndTitleConstraintOnPosts

Le contenu de la migration devra être le suivant pour ActiveRecord :

class AddAuthorIndexAndTitleConstraintOnPosts < ActiveRecord::Migration

def change

add_index :posts, :author

add_index :posts, :title, unique: true

end

end

Avec Sequel, il est nécessaire d'utiliser une forme plus explicite pour la migration. On déclare deux blocs : up et down pour respectivement mettre en place la migration et pour l'annuler. Dans les exemples précédents j'utilisais change qui impose de contenir uniquement des opérations réversibles. Or, add_unique_constraint n'est pas réversible pour le moment.

Sequel.migration do

up do

alter_table :posts do

add_index :author

add_unique_constraint :title

end

end

down do

alter_table :posts do

drop_index :author

drop_constraint :title, type: :unique

end

end

end

ActiveRecord et Sequel traitent la création d'une contrainte d'unicité comme un index unique. Ainsi, on ne peut pas directement exprimer la seconde ligne du SQL ci-dessus mais un équivalent fonctionnel.

À nouveau, pour effectuer la modification on exécutera la commande suivante :

bundle exec rake db:migrate

Modification des tables

Après quelques itérations, certaines fonctionnalités vont nécessiter l'ajout de colonnes. La procédure est là même que pour l'ajout d'index ou de contraintes c'est-à-dire :

- générer une migration avec rails generate migration ;

- remplir les méthodes/blocs up et down ou change ;

- appliquer la migration avec la tâche rake db:migrate.

Dans le cas de l'ajout d'une colonne on utilise la méthode add_column, tant pour ActiveRecord que pour Sequel. Seuls les paramètres changeront, consultez les documentations pour les détails.

Insertions

L'insertion correspond à l'ajout d'une ligne dans une table. En SQL cela s'écrit :

INSERT INTO posts (author, title, content) VALUES (?, ?, ?);

ActiveRecord est un ORM, dans le cas général, il va faire correspondre une ligne de table avec une instance d'objet. Sequel dispose lui aussi d'un module d'ORM (optionnel). Plaçons-nous dans le cas où nous avons les modèles suivants :

# ActiveRecord

class Post < ActiveRecord::Base

attr_accessible :author, :title, :content

end

# Sequel

class Post < Sequel::Model

end

On fait une insertion équivalente à celle en SQL avec la ligne ci-dessous. Cette simple insertion est identique pour ActiveRecord et Sequel.

Post.create(author: 'Nicolas', title: 'Titre 1', content: 'Lorem ipsum dolor...')

Associations

Un autre type d'insertion est la création d'une association.

On a deux cas de figure :

- le cas où l'un des deux modèles embarque une référence vers l'autre (1-to-1 et 1-to-N) et

- le cas où l'on a une table dédiée à la représentation de l'association (N-to-N).

Cas 1-to-1 et 1-to-N

On introduit la notion de commentaire. La relation est 1 Post - N Comment, le modèle Comment qui embarquera une référence vers un Post. En SQL on ajoute une table comments de la manière suivante :

CREATE TABLE posts (

id int(11) NOT NULL AUTO_INCREMENT,

author varchar(100) NOT NULL,

email varchar(100) NOT NULL,

content text NOT NULL,

post_id int(11) NOT NULL,

PRIMARY KEY (id),

INDEX post_id_index (post_id),

CONSTRAINT post_id_fk FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE

);

Voici la migration permettant l'ajout de la colonne comments. Comme précédemment, je ne précise pas le nom des contraintes pour rester concis et utilise donc les noms choisis par défaut par ActiveRecord et Sequel. Voici d'abord avec ActiveRecord :

class CreateComments < ActiveRecord::Migration

def change

create_table :comments do |t|

t.string  :author,  null: false, limit: 100

t.string  :email,   null: false, limit: 100

t.text    :content, null: false

t.integer :post_id, null: false

end

add_index :comments, [:post_id]

end

end

Et voici la version utilisant Sequel :

Sequel.migration do

change do

create_table :comments do

primary_key :id

String  :author,  null: false, size: 100

String  :email,   null: false, size: 100

String  :content, null: false, text: true

foreign_key :post_id, :posts, on_delete: :cascade, null: false

end

end

end

Ensuite pour créer une association, il suffit d'insérer la bonne référence dans la table des commentaires :

INSERT INTO comments (author, content, email, post_id) VALUES (?, ?, ?, ?);

Comme on vient de le voir cela se fait simplement avec la ligne suivante :

Comment.create(author: 'Nicolas', email: 'nicolas@example.com', content: 'Lorem ipsum dolor...', post_id: 1)

Les ORM fournissent des méthodes pour manipuler ces associations de manière transparente. Pour accéder à ces méthodes, on déclare les extrémités de la relation dans chacune des classes concernées. Voici la version ActiveRecord suivie de la version Sequel :

# model/post.rb

class Post < ActiveRecord::Base

has_many :comments, dependent: :delete_all

end

# model/comment.rb

class Comment < ActiveRecord::Base

belongs_to :post

end

# Exemple

p = Post.create(...)

p.comments.create(author: 'Nicolas', email: 'nicolas@example.com', content: 'Lorem ipsum dolor...')

# model/post.rb

class Post < Sequel::Model

one_to_many :comments

end

# model/comment.rb

class Comment < Sequel::Model

many_to_one :post

end

# Exemple

p = Post.create(...)

p.add_comment(author: 'Nicolas', email: 'nicolas@example.com', content: 'Lorem ipsum dolor...')

Attention, Sequel ne fournit pas par défaut de gestion des dépendances : dependent: :delete_all n'existe pas. Il faut activer le plugin AssociationDependencies pour avoir une gestion similaire. Il s'agit là de la politique de Sequel afin de ne pas pénaliser ceux qui n'utilisent pas l'ensemble des fonctionnalités. Les plugins peuvent même être activés par modèle plutôt que globalement.

Dans notre situation, ce n'est pas encore nécessaire d'introduire cette dépendance et j'explique tout de suite pourquoi.

Bon à savoir

Vous avez peut-être remarqué que Sequel permet de préciser une contrainte de clé étrangère ainsi que l'action à effectuer lorsque l'on supprime la ligne référencée. Cette pratique va à contre-courant des choix de Ruby on Rails, d'ailleurs ActiveRecord ne le permet pas. Cependant, il est bon de connaître les conséquences des choix que l'on fait, volontairement ou non.

Voici un exemple de session interactive avec ActiveRecord sans contraintes de clés étrangères :

p = Post.create(author: 'Nicolas', title: 'Titre 1', content: 'Lorem ipsum dolor...')

p.comments.create(author: 'Nicolas', email: 'nicolas@example.com', content: 'Lorem ipsum dolor...')

p.destroy

La dernière ligne : p.destroy va donner lieu aux deux requêtes ci-dessous.

DELETE FROM `comments` WHERE `comments`.`post_id` = 11

DELETE FROM `posts` WHERE `posts`.`id` = 11

Avec une clé étrangère SQL on aurait pu faire un p.delete et obtenir le même résultat, avec une seule commande SQL et non deux. En effet, une contrainte de clé étrangère sur comments.post_id avec l'option ON DELETE CASCADE permet de supprimer les commentaires associés à un billet supprimé.

L'approche de Ruby on Rails privilégie la représentation par objets au modèle relationnel en échangeant expressivité contre performance et intégrité.

Cas N-to-N

Ajoutons maintenant un système de tags. Chaque article va pouvoir être tagué plusieurs fois. Considérons que le modèle tag est créé et qu'il contient uniquement une clé primaire id et une chaîne de caractères name. Les tags étant communs aux articles on a la relation N Tag - N Post.

Lorsque l'on est en présence d'une telle association la marche à suivre en SQL est de créer une table dédiée à l'association. En SQL tout d'abord, cela se fait de la manière suivante :

CREATE TABLE posts_tags (

post_id int(11) NOT NULL,

tag_id int(11) NOT NULL,

PRIMARY KEY (post_id,tag_id),

CONSTRAINT posts_tags_post_fk FOREIGN KEY (post_id) REFERENCES posts (id),

CONSTRAINT posts_tags_tag_fk FOREIGN KEY (tag_id) REFERENCES tags (id)

);

À nouveau, voici les versions ActiveRecord et Sequel l'une à la suite de l'autre.

class CreatePostsTags < ActiveRecord::Migration

def change

create_table :posts_tags, id: false do |t|

t.integer :post_id

t.integer :tag_id

end

add_index :posts_tags, [:post_id, :tag_id], unique: true

end

end

Sequel.migration do

change do

create_join_table(post_id: :posts, tag_id: :tags)

end

end

Le nom de la table de jointure est défini par les deux ORM de la même manière. Il s'agit du nom des tables à joindre (ici posts et tags) triées par ordre alphabétique et jointe par un underscore. Avec Sequel, on a à disposition une méthode permettant de faire automatiquement la table de jointure voulue.

Comme on l'a vu pour les relations 1-to-1, il faut déclarer ces relations N-to-N auprès de l'ORM.

# model/post.rb

class Post < ActiveRecord::Base

has_and_belongs_to_many :tags

end

# model/tag.rb

class Tag < ActiveRecord::Base

has_and_belongs_to_many :posts

end

# model/post.rb

class Post < Sequel::Model

many_to_many :tags

end

# model/tag.rb

class Tag < Sequel::Model

many_to_many :posts

end

On souhaite maintenant ajouter un tag sur un article. En SQL, on crée simplement une entrée dans la table posts_tags pour y parvenir.

INSERT INTO posts_tags (post_id, tag_id) VALUES (?, ?);

En passant par l'ORM, cela va se faire de la façon suivante :

# ActiveRecord

p = Post.first

p.tags << Tag.where(name: 'Cool').first_or_create

# Sequel

p = Post.first

p.add_tag Tag.find_or_create(name: 'Cool')

Lorsqu'une relation va contenir de l'information, on passera par un modèle intermédiaire. Dans cette situation, il faudra donner un peu plus d'information à l'ORM afin qu'il réalise les jointures, voir l'option through d'ActiveRecord, l'option join_table de Sequel et l'option dataset de Sequel.

Requêtes

À présent que les bases sont prêtes, nous pouvons aborder la question des requêtes. Faire des requêtes en SQL c'est le b.a.-ba. Pourtant, dès que je passe par un ORM, ça se complique…

Chargement

Sélection globale

La chose que l'on fait régulièrement c'est de vouloir sélectionner toutes les colonnes de toutes les lignes d'une table. En SQL cela donne :

SELECT * FROM posts;

Avec les ORM :

Post.all

Sélection partielle

Je n'y pense pas toujours, mais il n'est pas toujours nécessaire de charger toutes les colonnes d'un modèle. Par exemple, sur l'index des billets du blog, je souhaite n'afficher que le titre et l'auteur des articles. Dans ce cas, il est inutile de charger le contenu de tous les articles. En SQL cela donne :

SELECT author, title FROM posts;

Avec les ORM :

Post.select([:author, :title]).all

Attention dans ce cas-là, les attributs non sélectionnés vaudront nil ce qui posera peut-être des problèmes de validation. Si vous ne sélectionnez pas la clé primaire id, vos associations ne marcheront pas non plus. Pour éviter ces problèmes, il est possible d'utiliser des attributs paresseux. Sequel dispose du plugin LazyAttributes et ActiveRecord d'une gem activerecord-lazy-attributes.

Préchargement des relations

Lorsque l'on dispose d'une collection d'objets on est tenté d'itérer dessus. Et, lorsque pendant l'itération on tente d'accéder à un objet en relation on génère ce que l'on appelle des requêtes n+1. Prenons l'exemple ci-dessous dans lequel je veux afficher la liste des titres de billets et pour chaque billet, les tags qui lui sont associés :

Post.select([:id, :title]).all.each do |post|

puts 'post.title'

puts "  #{post.tags.map(&:name).join(', ')}"

end

ActiveRecord génère le SQL suivant :

SELECT id, title FROM posts;

-- Pour chaque billet :

SELECT tags.* FROM tags

INNER JOIN posts_tags ON tags.id = posts_tags.tag_id

WHERE posts_tags.post_id IS ?;

Pour chaque billet, notre code va produire une requête SQL pour demander les tags associés. C'est très mauvais en ce qui concerne les performances d'une application. Cela multiplie le nombre de requêtes et toutes les latences qui vont avec.

L'objectif est donc de charger, un maximum d'informations en un minimum de requêtes. Voilà ce que l'on pourrait écrire en SQL pour récupérer toutes nos informations en une seule fois :

SELECT posts.id, posts.title, tags.name FROM posts

LEFT OUTER JOIN posts_tags ON (posts_tags.post_id = posts.id)

LEFT OUTER JOIN tags ON (tags.id = posts_tags.tag_id);

-- id  title    name

-- 1   Foobar   Foo

-- 1   Foobar   Bar

-- 2   Foobar2  Foo

-- 2   Foobar2  Bar

-- 3   Toto     NULL

Le souci avec ce code est que pour chaque tag d'un même article, on duplique les attributs de l'article. Dans notre cas on a uniquement l'attribut title et l'id. De la même manière le nom d'un tag va apparaître autant de fois qu'il est associé à un billet.

Dans cette situation particulière on va confier à l'ORM la tâche de reconstruire la relation et effectuer une requête en deux temps.

Avec ActiveRecord on utilisera includes. Attention avec l'arrivée de Rails 4, le comportement du includes est légèrement modifié. Consultez notre article sur Rails 4 pour apprendre ce qu'il y a de neuf.

Post.select([:id, :title]).includes(:tags)

Et, avec Sequel on utilise eager :

Post.select(:id, :title).eager(:tags)

Le SQL résultant est le suivant :

SELECT id, title FROM posts;

SELECT tags.*, posts_tags.post_id AS fk_post_id FROM tags

INNER JOIN posts_tags

ON posts_tags.tag_id = tags.id

AND posts_tags.post_id IN (?);

-- Résultat pour un tag Foo présent sur deux billets d'id 1 et 2

--

-- id  name  fk_post_id

-- 1   Foo   1

-- 1   Foo   2

La première requête va servir à obtenir la liste des billets. La seconde va récupérer toutes les associations tag/billet relatives aux billets de la première requête. On remarque que l'on duplique les informations relatives aux tags : name et id. C'est le même cas de figure que pour title, tout à l'heure. La situation est toutefois moins mauvaise que la précédente puisque l'on ne duplique plus les attributs du billet.

C'est donc cette dernière solution qui est le plus couramment utilisée par les ORM. À moins de charger les tables posts, tags et posts_tags il semble difficile d'éviter la duplication.

Bon à savoir

Sachez tout de même que l'on peut encore faire des économies en utilisant les fonctions d'agrégat string_agg en PostgreSQL ou group_concat en MySQL.

SELECT posts.id, posts.title, GROUP_CONCAT(tags.name) AS tags FROM posts

LEFT OUTER JOIN posts_tags ON (posts_tags.post_id = posts.id)

LEFT OUTER JOIN tags ON (tags.id = posts_tags.tag_id)

GROUP BY posts.id;

-- id  title    tags

-- 1   Foobar   Foo,Bar

-- 2   Foobar2  Foo,Bar

-- 3   Toto     NULL

Cette solution à l'inconvénient de ne pas être portable d'un SGBDR à un autre et n'est donc pas incluse par défaut dans le langage des ORM. Sequel permet toutefois d'écrire cette requête de la manière suivante :

Post.left_join(:posts_tags, post_id: :posts__id).

left_join(:tags, id: :posts_tags__tag_id).

group(:posts__id).

select{[posts__id, posts__title, group_concat(tags__name).as(tags)]}

Agrégation

J'ai régulièrement besoin de sélectionner d'autres colonnes en plus de celles de mon modèle. Ces colonnes supplémentaires font parfois partie d'une association, parfois sont une agrégation issue d'un group by, etc. Ci-dessous, vous trouverez deux exemples en SQL.

Dans le premier exemple, on compte le nombre de posts de chaque auteur. Et, dans le second, on compte le nombre de tags de chaque article.

SELECT author, count(id) AS posts_count

FROM posts

GROUP BY author;

SELECT posts.*, count(tag_id) AS tags_count

FROM posts

LEFT JOIN posts_tags ON post_id = id

GROUP BY id;

Ce sont deux opérations qui sont parfois faites directement en Ruby à cause du fait qu'elles ne collent pas au mapping objet/relationnel. Voici donc ce que l'on peut voir en Ruby :

# Avec Sequel ou Active Record

Post.select(:author).all.reduce({}){|h,p| h[p.author] = (h[p.author] ||= 0) + 1 ; h }

# => SELECT author FROM posts

# Avec ActiveRecord

Post.includes(:tags).all.reduce({}){|h,p| h[p] = p.tags.size ; h }

# => SELECT posts.* FROM posts

# => SELECT tags.*, t0.post_id AS ar_association_key_name FROM tags

#    INNER JOIN posts_tags AS t0 ON tags.id = t0.tag_id

#    WHERE t0.post_id IN (?)

# Avec Sequel

Post.eager(:tags).all.reduce({}){|h,p| h[p] = p.tags.size ; h }

# => SELECT posts.* FROM posts

# => SELECT tags.*, posts_tags.post_id AS x_foreign_key_x FROM tags

#    INNER JOIN posts_tags

#    ON ((posts_tags.tag_id = tags.id) AND (posts_tags.post_id IN (?)))

On utilise la méthode reduce qui va faire office de GROUP BY et d'agrégateur. Cette méthode ne fait pas partie des ORM, c'est du pur Ruby. Utiliser cette méthode limite les performances. On va calculer des choses qui auraient pu l'être au niveau de la base de données. On va aussi charger des données inutiles : les noms des tags, alors que seul leur nombre nous importe.

1er exemple

Voilà comment faire pour exprimer nos requêtes avec ActiveRecord :

posts = Post.select(['author', 'count(id) as count']).group(:author).all

posts.first.attributes['count']

Comme on peut le voir, le count s'exprime assez mal avec ActiveRecord. ARel permet d'arranger ça. Par arranger j'entends que l'on n'écrit pas directement le SQL, mais qu'il est généré. C'est un bon point en faveur de la portabilité et de la modularité. L'écriture est plus longue et moins lisible pour moi, mais c'est peut-être une question d'habitude.

t = Post.arel_table

posts = Post.select([t[:author], t[:id].count.as('count')]).group(:author).all

posts.first.attributes['count']

Et voilà comment faire avec Sequel :

posts = Post.group_and_count(:author)

posts.first.values[:count]

Bon, vous allez me dire que c'est un peu facile comme exemple puisque Sequel prévoit ce genre d'opérations. Si l'on choisit de ne pas utiliser cette méthode, voilà une autre manière de le faire.

posts = Post.select{[author, count(id).as(count)]}.group(:author).all

posts.first.values[:count]

L'écriture utilise les Virtual Rows qui font partie du noyau de Sequel. On peut trouver que c'est encore un peu facile… Voilà une dernière manière de faire avec Sequel, sans les Virtuals Rows, en utilisant les Expressions :

posts = Post.select(:author, Sequel.function(:count, :id).as(:count)).group(:author).all

posts.first.values[:count]

2e exemple

Dans le second exemple on utilise une jointure, une jointure à gauche en plus ! Dans la section concernant les jointures nous en reparlerons.

Conditions

Expressions

Pour exprimer des conditions classiques, notre article sur ARel est très fourni. Concernant Sequel, il existe une page de documentation concernant les conditions qui synthétise les conditions habituelles.

Voici une mise en situation basique. Je voudrais afficher les articles écrits par un auteur lambda.

SELECT * FROM posts WHERE author = 'Nicolas';

Post.where(author: 'Nicolas')

Même chose avec une liste d'auteurs :

SELECT * FROM posts WHERE author IN ('Nicolas', 'Toto');

Post.where(author: ['Nicolas', 'Toto'])

Si on ajoute une condition sur le titre, cela donne :

SELECT * FROM posts WHERE author IN ('Nicolas', 'Toto') OR title LIKE 'Titre%';

# ActiveRecord seul

Post.where('author IN (?) OR title LIKE ?', ['Nicolas', 'Toto'], 'Titre%')

# ActiveRecord & ARel

t = Post.arel_table

Post.where(t[:author].in(['Nicolas', 'Toto']).or(t[:title].matches('Titre%')))

# Sequel

Post.where{{author: ['Nicolas', 'Toto']} | {title: /^Titre/}}

Voilà, j'aurais bien quelques autres exemples idiots à donner, mais je pense que ceux-là suffisent pour comprendre l'idée générale.

Filtres préétablis

Dans une application on utilise souvent les mêmes conditions. ActiveRecord et Sequel disposent de méthodes pour donner un nom à ces conditions et pouvoir les combiner facilement.

# ActiveRecord

class Post

scope :of, ->(name){ where(author: name) }

scope :start_with, ->(str){ where(self.arel_table[:title].matches("#{str}%")) }

end

# Sequel

class Post

dataset_module do

def of(name)

where(author: name)

end

def start_with(str)

where(title: /^#{str}/)

end

end

end

# Utilisation

Post.of('Nicolas').start_with('Titre')

Voilà un exemple simple, mais il est possible de faire des choses plus complexes. On peut par exemple ajouter des jointures dans ces méthodes.

Jointures

Pour rappel voici un petit récapitulatif graphique des jointures en SQL.

Jointures par défaut

On souhaite maintenant exprimer une relation articles liés. Cette relation permet de relier entre eux des articles partageant au moins un tag. Voici un exemple de requête SQL qui permet d'obtenir les articles liés, ? est l'id de l'article en question. Dans notre cas, on ne s'intéresse pas au contenu des articles liés, notre objectif est simplement d'afficher des liens basés sur le titre et l'auteur.

SELECT DISTINCT id, author, title FROM posts

INNER JOIN posts_tags AS pt1 ON (pt1.post_id = ?)

INNER JOIN posts_tags AS pt2 ON ((pt2.tag_id = pt1.tag_id) AND (pt2.post_id != ?))

WHERE (pt2.post_id = id)

On fait une première jointure sur posts_tags qui nous donne tous les tag_id associé au post d'id = ?. Puis, on fait une seconde jointure sur posts_tags permettant d'obtenir tous les id des posts ayant un tag en commun avec celui d'id = ?. Remarquez que l'on retire du résultat de notre seconde jointure les articles d'id = ?. Enfin, on sélectionne seulement les posts issus de notre dernière jointure grâce à la condition pt2.post_id = id.

On ajoute la méthode Post#related qui gardera en cache le résultat de la requête.

Avec ActiveRecord on va pouvoir écrire :

class Post < ActiveRecord::Base

has_and_belongs_to_many :tags

def related_request

t = Post.arel_table

pt1 = Arel::Table.new(:posts_tags)

pt2 = pt1.alias

query = t.project(t[:id], t[:author], t[:title]).

join(pt1).on(pt1[:post_id].eq(id)).

join(pt2).on(pt2[:tag_id].eq(pt1[:tag_id]).and(pt2[:post_id].not_eq(t[:id]))).

where(pt2[:post_id].eq(t[:id]))

query.distinct

query

end

def related(force_reload=false)

(!@related || force_reload) ?

(@related = Post.find_by_sql(related_request)) :

@related

end

end

Avec Sequel, voici une version possible :

def related_request

Post.distinct.select(:id, :author, :title).

join(:posts_tags___pt1, post_id: id).

join(:posts_tags___pt2, [{pt2__tag_id: :pt1__tag_id}, Sequel.~(pt2__post_id: id)]).

where(pt2__post_id: :posts_id)

end

def related(force_reload=false)

(!@related || force_reload) ?

(@related = related_request.all) :

@related

end

ARel est très efficace dans sa manière d'utiliser les alias : à tout moment, on sait quelle table on référence. La notation de Sequel pour l'aliasing : ___ n'est pas mal non plus, plus lisible mais moins aisément manipulable.

Autres types de jointures

Que ce soit avec ARel ou avec Sequel, vous pouvez faire une jointure à gauche. C'est utile, très utile même. Reprenons le second exemple de la section sur les agrégations :

SELECT posts.*, count(tag_id) AS tags_count

FROM posts

LEFT JOIN posts_tags ON post_id = id

GROUP BY id;

La jointure à gauche me permet d'obtenir les articles n'ayant aucun tag associé. Si j'avais écrit la requête ci-dessous, je n'aurais récupéré que les articles avec au moins un tag.

SELECT posts.*, count(tag_id) AS tags_count

FROM posts

JOIN posts_tags ON post_id = id

GROUP BY id;

Avec les jointures à gauche, on peut exprimer des conditions intéressantes. Admettons que le système de tag d'article est nouveau et que je recherche les articles non tagués. Je vais pouvoir faire une jointure à gauche et ajouter une contrainte. Il s'agit du cas Left Excluding JOIN.

SELECT posts.id, posts.author, posts.title

FROM posts

LEFT JOIN posts_tags ON post_id = id

WHERE post_id IS NULL;

Bien sûr ce type de requête peut se faire avec une requête imbriquée :

SELECT posts.id, posts.author, posts.title

FROM posts

WHERE id NOT IN (SELECT post_id FROM posts_tags);

Voici comment on peut faire ces deux choses avec ActiveRecord :

t  = Post.arel_table

pt = Arel::Table.new(:posts_tags)

# LEFT JOIN

query = t.project(t[:id], t[:author], t[:title]).

join(pt, Arel::Nodes::OuterJoin).on(t[:id].eq(pt[:post_id])).

where(pt[:post_id].eq(nil))

# Sous-requête

query = t.project(t[:id], t[:author], t[:title]).

where(t[:id].not_in(pt.project(pt[:post_id])))

Post.find_by_sql(query)

Voilà pour les versions utilisant Sequel :

# LEFT JOIN

Post.select(:id, :author, :title).

left_join(:posts_tags, post_id: :id).

where(post_id: nil)

# Sous-requête

pt = Sequel::DATABASES.first[:posts_tags]

Post.select(:id, :author, :title).where{~{id => pt.select(:post_id)}}

Sequel permet de faire des jointures complètes (full) et dispose des méthodes suivantes : join, inner_join, full_outer_join, right_outer_join, left_outer_join, full_join, right_join et left_join. Je n'ai pas vu cette possibilité avec ActiveRecord et ARel autrement qu'en écrivant soi-même le SQL.

Références

Voici les liens que j'ai utilisés pour m'aider dans la rédaction de cet article.

- La documentation sur la syntaxe MySQL

- La documentation sur la syntaxe PostgreSQL

- La documentation d'ActiveRecord::Migration

- Le code source d'ARel

- Le guide de migration de Sequel

- Les méthodes de modification de schéma de Sequel

- Le mémento Sequel

- Une représentation graphique des différentes jointures

Conclusion

C'en est terminé de ce récapitulatif. J'espère que celui-ci pourra vous servir de mémento en ce qui concerne l'interaction avec une base de données relationnelle depuis Ruby on Rails. Il y a certainement des points que je n'ai pas traités, n'hésitez pas à les mentionner en commentaire pour que je puisse mettre à jour l'article.

Cet article est publié avec l'aimable autorisation de Synbioz.

L'article original peut être lu sur le blog de Synbioz : Mémento SQL avec Ruby.

Nous tenons à remercier ClaudeLELOUP pour sa relecture attentive de cet article.