Mémento SQL avec Ruby

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▲

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

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

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

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.

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

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.

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▲

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

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 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▲

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

posts.first.attributes['count']

t = Post.arel_table

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

posts.first.attributes['count']

posts = Post.group_and_count(:author)

posts.first.values[:count]

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

posts.first.values[:count]

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

posts.first.values[:count]

2e exemple▲

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.

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.

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.

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.