Comment interroger efficacement des enregistrements primaires multiples et leurs enregistrements one-to-many ?

Question

Considérons la structure DB suivante. Le langage de travail actuel est Coldfusion et MSSQL.

Tables
Hotels: - Columns - hotelid, name, company, address, state, city, zip
Media: - Columns - mediaid, label, url
HotelsXMedia: - Columns - xid, hotelid, mediaid

En fait, nous avons une table avec des hôtels, une table qui contient des images et une table croisée pour gérer la relation many to many. La sortie souhaitée est un tableau unique (ou une requête), avec chaque élément ayant toutes les colonnes de la table des hôtels ainsi que toutes les lignes de sa table des médias dans la ligne primaire afin qu'il puisse être transmis à une fonction de thématisation, par exemple.

La méthode actuelle consiste à interroger les hôtels, à les convertir en tableau et à extraire les numéros d'hôtel de l'ensemble des résultats. Interroger les médias en fonction de l'identifiant de l'hôtel. Convertir la requête média résultante en structure où l'index est l'hôtelid. Ensuite, on boucle sur le tableau des hôtels et on assigne les données des médias dans le tableau des hôtels. Puis passer le tableau hotels à l'endroit où il doit aller.

Existe-t-il un moyen plus efficace de procéder ? Existe-t-il un moyen de faire cela sans faire deux requêtes SQL et tous les pliages et boucles qui en résultent ? Presque tous les modules de l'application utilisent la table des médias pour stocker leurs images. Ainsi, pour presque tous les composants de la page, nous devons effectuer ce genre de combinaison et cela semble assez lourd. N'y a-t-il pas un meilleur moyen ? De plus, bien que l'amélioration de cette combinaison de requêtes ne présente qu'un léger avantage, étant donné qu'elle est nécessaire pour 90% des composants, l'avantage de l'amélioration s'étendrait à tous les composants, ce qui entraînerait des gains décents.

Je considérerais que c'est une solution réalisable si une colonne de la requête de résultat était une liste délimitée d'urls MEDIA jointes. Mais malheureusement, en MSSQL, nous ne disposons pas de group_concat(). Si cette méthode était utilisée, y a-t-il un moyen facile de saisir plusieurs colonnes, si par exemple je voulais saisir media:url et media:label ? Existe-t-il un moyen de s'assurer que les valeurs vides ou nulles obtiennent toujours un délimiteur si un enregistrement dans la table media a un url mais pas de label, ce qui fait que les deux listes ne sont pas alignées. Si j'emprunte cette voie, dois-je faire plusieurs chemins XML pour y parvenir ? Tout conseil serait grandement apprécié.

Answer 1

Vérifiez Simuler la fonction MySQL group_concat dans Microsoft SQL Server 2005 ?

Answer 2

Lorsque j'ai été confronté à ce problème, j'ai fini par faire une seule grande requête ( select * from hotelsxmedia join hotels on ... join media on ... ), qui renvoie autant de lignes que de lignes de la table croisée (HotelsXMedia). Veillez à order by hotelid . Ensuite, lorsque vous parcourez les résultats de la requête, vous pouvez effectuer une action pour chaque nouvel identifiant d'hôtel (par exemple, ajouter au tableau) et une action différente pour une ligne qui répète le même identifiant d'hôtel (par exemple, ajouter le nouveau média).

Answer 3

Comme @krubo le suggère, je ferais une grande requête et ensuite un cfoutput en utilisant l'attribut group="hotelid" et en créant votre tableau et vos structures dans ce cadre.

Answer 4

Vous pouvez faire tout cela en une seule requête ordonnée par numéro d'hôtel et construire la structure de données en utilisant un CFOUTPUT groupé et imbriqué.

<cfquery name="myHotels" datasource="HotelStuff" >
  SELECT H.*, M.*
  FROM Hotels H
      JOIN HotelsXMedia HXM ON H.hotelid = HXM.hotelid
      JOIN Media M ON M.mediaid = HXM.mediaid
  ORDER BY H.hotelid
</cfquery>

L'attribut group="" de CFOUTPUT permet de regrouper la boucle en fonction des changements de la valeur du champ hotelid.

<cfoutput query="myHotels" group="hotelid">
  <cfset hotels = {
     hotelid = myHotels.hotelid,
     name = myHotels.name,
     company = myHotels.company,
     address = myHotels.address,
     state = myHotels.state,
     city = myHotels.city,
     zip = myHotels.zip,
     media = arrayNew(1)
  } />

  <cfoutput>
     <cfset thisMedia = { label = myHotels.label, url = myHotels.url } />
     <cfset arrayAppend(hotels.media, thisMedia) />
  </cfoutput>
</cfoutput>

Vous pourriez ordonner par Hotels.name et parfois vous en sortir ; vous devrez vous assurer que deux hôtels n'ont jamais exactement le même nom, sinon ils seront regroupés en un seul groupe. Il est généralement plus sûr d'utiliser un groupe basé sur votre colonne clé primaire.