Trouver les enregistrements du modèle par ID dans l'ordre où le tableau d'ID a été donné.

Question

J'ai une requête pour obtenir les ID des personnes dans un ordre particulier, par exemple : ids = [1, 3, 5, 9, 6, 2]

Je veux ensuite récupérer ces personnes par Person.find(ids)

Mais ils sont toujours récupérés dans l'ordre numérique, je le sais par expérience :

people = Person.find(ids).map(&:id)
 => [1, 2, 3, 5, 6, 9]

Comment puis-je exécuter cette requête de manière à ce que l'ordre soit le même que celui du tableau des identifiants ?

J'ai rendu cette tâche plus difficile car je ne voulais exécuter la requête pour récupérer les personnes qu'une seule fois, à partir des ID donnés. Il est donc hors de question d'effectuer plusieurs requêtes.

J'ai essayé quelque chose comme :

ids.each do |i|
  person = people.where('id = ?', i)

Mais je ne pense pas que cela fonctionne.

Answer 1

Vous pouvez obtenir les utilisateurs triés par id asc de la base de données, puis les réorganiser dans l'application comme vous le souhaitez. Regardez ça :

ids = [1, 3, 5, 9, 6, 2]
users = ids.sort.map {|i| {id: i}} # Or User.find(ids) or another query

# users sorted by id asc (from the query)
users # => [{:id=>1}, {:id=>2}, {:id=>3}, {:id=>5}, {:id=>6}, {:id=>9}]

users.sort_by! {|u| ids.index u[:id]} 

# users sorted as you wanted
users # => [{:id=>1}, {:id=>3}, {:id=>5}, {:id=>9}, {:id=>6}, {:id=>2}]

L'astuce ici est de trier le tableau par une valeur artificielle : l'index de l'id de l'objet dans un autre tableau.

Answer 2

Je résume ici les solutions, en plus d'ajouter une solution récente (9.4+) spécifique à PostgreSQL. Ce qui suit est basé sur Rails 6.1 et PostgreSQL 12. Bien que je mentionne des solutions pour des versions antérieures de Rails et PostgreSQL, je ne les ai pas réellement testées avec des versions antérieures.

À titre de référence, cette Question "ORDER BY la liste des valeurs IN". donne différentes manières de trier/ordonner avec la base de données.

Ici, je suppose que le modèle est garanti d'avoir tous les enregistrements spécifiés par le tableau d'IDs, ids . Sinon, une exception du type ActiveRecord::RecordNotFound peut être relevé (ou non, selon la manière).

Ce qui ne fonctionne PAS

Person.where(id: ids)

L'ordre de la relation renvoyée est soit arbitraire, soit celui des valeurs numériques des identifiants primaires ; quel que soit l'ordre, il ne correspond généralement pas à celui de la relation. ids .

Solution simple pour obtenir un tableau

(Rails 5+ uniquement( ?))

Person.find ids

qui renvoie un tableau Ruby de modèles de personnes dans l'ordre de l'adresse donnée. ids .

L'inconvénient est que vous ne pouvez pas modifier le résultat avec SQL.

Dans Rails 3, la méthode suivante est apparemment la bonne, bien qu'elle puisse ne pas fonctionner (certainement pas dans Rails 6) dans les autres versions de Rails.

Person.find_all_by_id ids

Solution purement Ruby pour obtenir un tableau

Deux voies. L'une ou l'autre fonctionne indépendamment des versions de Rails (je pense).

Person.where(id: ids).sort_by{|i| ids.index(i.id)}
Person.where(id: ids).index_by(&:id).values_at(*ids)

qui renvoie un tableau Ruby de modèles de personnes dans l'ordre de l'adresse donnée. ids .

Solution au niveau de la base de données pour obtenir une relation

Tous les éléments suivants reviennent Person::ActiveRecord_Relation à laquelle vous pouvez appliquer d'autres filtres si vous le souhaitez.

Dans les solutions suivantes, tous les enregistrements sont préservés, y compris ceux dont les ID ne sont pas inclus dans le tableau donné. ids . Vous pouvez les filtrer à tout moment en ajoutant where(id: ids) (ce type de flexibilité est une des caractéristiques de la ActiveRecord_Relation ).

Pour toute base de données

Sur la base de La réponse de user3033467 mais mis à jour pour fonctionner avec Rails 6 (qui a désactivé certaines fonctionnalités avec order() en raison d'un problème de sécurité ; voir " Mises à jour pour l'injection SQL dans Rails 6.1 " par Justin pour le fond).

order_query = <<-SQL
  CASE musics.id 
    #{ids.map.with_index { |id, index| "WHEN #{id} THEN #{index}" } .join(' ')}
    ELSE #{ids.length}
  END
SQL

Person.order(Arel.sql(order_query))

Pour MySQL

De La réponse de Koen (Je ne l'ai pas testé).

Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)

Pour PostgreSQL

PostgreSQL 9.4 et plus

join_sql = "INNER JOIN unnest('{#{ids.join(',')}}'::int[]) WITH ORDINALITY t(id, ord) USING (id)"
Person.joins(join_sql).order("t.ord")

PostgreSQL 8.2 et plus

Sur la base de Réponse de Jerph pero LEFT JOIN est remplacé par INNER JOIN :

val_ids = ids.map.with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
Person.joins("INNER JOIN (VALUES #{val_ids}) AS persons_id_order(id, ordering) ON persons.id = persons_id_order.id")
  .order("persons_id_order.ordering")

Pour obtenir des objets de niveau inférieur

Voici des solutions pour obtenir des objets de niveau inférieur. Dans la grande majorité des cas, les solutions décrites ci-dessus doivent être supérieures à celles-ci, mais je les mets ici par souci d'exhaustivité (et pour mémoire avant que je ne trouve de meilleures solutions)

Dans les solutions suivantes, les enregistrements qui ne correspondent pas aux IDs dans ids sont filtrés, contrairement aux solutions décrites dans la section précédente (où l'on peut choisir de conserver tous les enregistrements).

Pour obtenir un ActiveRecord::Result

C'est une solution pour obtenir ActiveRecord::Result avec PostgreSQL 9.4+.

ActiveRecord::Result est similaire à un tableau de hachage.

str_sql = "select persons.* from persons INNER JOIN unnest('{#{ids.join(',')}}'::int[]) WITH ORDINALITY t(id, ord) USING (id) ORDER BY t.ord;"
Person.connection.select_all(str_sql)

Person.connection.exec_query renvoie le même (alias ?).

Pour obtenir un PG::Result

C'est une solution pour obtenir PG::Result avec PostgreSQL 9.4+. Très similaire au précédent, mais remplace exec_query con execute (la première ligne est identique à la solution ci-dessus) :

str_sql = "select persons.* from persons INNER JOIN unnest('{#{ids.join(',')}}'::int[]) WITH ORDINALITY t(id, ord) USING (id) ORDER BY t.ord;"
Person.connection.execute(str_sql)

Answer 3

Vieille question, mais le tri peut être fait par ordre en utilisant le SQL FIELD fonction. (Je n'ai testé cela qu'avec MySQL).

Donc, dans ce cas, quelque chose comme ceci devrait fonctionner :

Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)

Ce qui donne le SQL suivant :

SELECT * FROM people
  WHERE id IN (1, 3, 5, 9, 6, 2)
  ORDER BY FIELD(id, 1, 3, 5, 9, 6, 2)

Answer 4

La plupart des autres solutions ne vous permettent pas de filtrer davantage la requête résultante. La réponse de Koen .

Similaire à cette réponse mais pour Postgres, j'ajoute cette fonction à mon ApplicationRecord (Rails 5+) ou à tout modèle (Rails 4) :

def self.order_by_id_list(id_list)
  values_clause = id_list.each_with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
  joins("LEFT JOIN (VALUES #{ values_clause }) AS #{ self.table_name}_id_order(id, ordering) ON #{ self.table_name }.id = #{ self.table_name }_id_order.id")
    .order("#{ self.table_name }_id_order.ordering")
end

La solution de la requête est de cette question .

Answer 5

Cette tâche est plus efficacement gérée en SQL via ActiveRecord et non en Ruby.

ids = [3,1,6,7,12,2]
Post.where(id: ids).order("FIELD(id, #{ids.join(',')})")