Cet article est publié sous licence CC BY-NC-SA
Dans un précédent article, je partageai 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 memento qui explique concrètement comment exprimer du SQL en Ruby dans le contexte d’une application Ruby on Rails.
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.
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’adapteur 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.
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
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
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 :
rails generate migration
up et down ou change
db:migrate
Dans le cas de l’ajout d’une colonne on utilise la méthode add_column, tant pour ActiveRecord que pour Sequel.
Seul les paramètres changeront, consultez les documentations pour les détails.
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...')
Un autre type d’insertion est la création d’une association.
On a deux cas de figure :
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 ORMs 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 suivi 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.
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 connaitre 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é.
Ajoutons maintenant un système de tags. Chaque article va pouvoir être taggué plusieurs fois.
Considérons que le modèle tag est créé et qu’il contient uniquement une clé primaire id et une chaine 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éfinie par les deux ORMs 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.
À 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éaba. Pourtant, dès que je passe par un ORM, ça se complique…
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 ORMs :
Post.all
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 ORMs :
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 attibuts paresseux.
Sequel dispose du plugin LazyAttributes et
ActiveRecord d’une gem activerecord-lazy-attributes.
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 billets, 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 tache 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'ids 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 pire 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 ORMs.
À moins de charger les tables posts, tags et posts_tags il semble difficile d’éviter la duplication.
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 inclue par défaut dans le langage des ORMs. 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)]}
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 ORMs, c’est du pur Ruby.
Utiliser cette méthodes 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.
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 é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]
Dans le second exemple on utilise une jointure, une jointure à gauche en plus ! Dans la section concernant les jointures nous en reparlerons.
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
Voilà, j’aurais bien quelques autres exemples idiots à donner, mais je pense que ceux là suffisent pour comprendre l’idée générale.
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.
Pour rappel voici un petit récapitulatif graphique des jointures en SQL.
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 tout 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 methode 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.
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-taggué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.
Voici les liens que j’ai utilisé pour m’aider dans la rédaction de cet article.
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é, n’hésitez pas à les mentionner en commentaire pour que je puisse mettre à jour l’article.
L’équipe Synbioz.
Libres d’être ensemble.
336 avenue de Dunkerque
59130 Lambersart
5 rue de douai
75009 Paris
1 rue du Guesclin
44000 Nantes
Nos conseils et ressources pour vos développements produit.