与多个领域的多对多关系是我工作的正确工具？ [英] Is a many-to-many relationship with extra fields the right tool for my job?

问题描述

以前请问一个更具体的这个问题的版本，但是在阐述我的问题时有困难。反思让我怀疑我选择的解决方案是否正确的问题，所以这一次我会解释问题，问我是否正确的轨道，以及b）如果有一种方式在我现在的砖墙。 / p>

我正在构建一个Web界面，使现有的数据库能被（少数）用户询问。从文档的类比中，我有类似于这样的模型：

  class Musician（models.Model）：
 first_name = models.CharField（max_length = 50）
 last_name = models.CharField（max_length = 50）
 dob = models.DateField（）
 
 class Album $ M $）
艺术家= models.ForeignKey（音乐家）
 name = models.CharField（max_length = 100）
 
 class乐器（models.Model）：
艺术家= models.ForeignKey（音乐家）
 name = models.CharField（max_length = 100）
  

我有一个中心表（音乐家）和几个与ForeignKey或OneToOneFields相关联的关联数据表。用户通过创建过滤条件来与数据库进行交互，以便根据主要或相关表上的数据的数据来选择音乐家的一个子集。同样，用户可以选择使用什么样的数据来排列呈现给他们的结果。然后，结果最初被视为一个二维表，每个音乐家都有一行，每列都有选定的数据字段（或聚合）。

给你一些想法规模，数据库拥有〜5,000名音乐家，拥有约20个相关数据领域。

到此为止，我有一个工作实现。然而，重要的是，我有能力让给定的用户上传自己的注释数据集（多于一个），然后以与现有数据相同的方式对其进行过滤和排序。

我试图这样做的方式是添加模型：

  class UserDataSets 
 user = models.ForeignKey（User）
 name = models.CharField（max_length = 100）
 description = models.CharField（max_length = 64）
结果= models.ManyToManyField（Musician，through ='UserData'）
 
 class UserData（models.Model）：
 artist = models.ForeignKey（Musician）
 dataset = models.ForeignKey （UserDataSets）
 score = models.IntegerField（）
 
 class Meta：
 unique_together =（（艺术家，数据集））
  

我有一个简单的上传机制，使用户能够上传一个数据集文件，包括音乐家和他们的得分之间的1到1关系。在给定的用户数据集中，每个艺术家将是独一无二的，但不同的数据集彼此独立，并且通常会包含同一音乐人的条目。

数据，从给定的艺术家开始我可以做这样的事情：

  artist = Musician.objects.get（pk = 1） 
 dataset = UserDataSets.objects.get（pk = 5）
 print artist.userdata_set.get（dataset = dataset.pk）
  

然而，当我根据包含在单个用户数据集中的数据实现对音乐人的查询集的过滤和排序时，这种方法落空了。例如，我可以根据UserData表中的所有数据轻松订购查询集，如下所示：

  artists = Musician .objects.all（）。order_by（userdata__score）
  

但这并不能帮助我按结果排序给定的单个用户数据集。同样，我需要能够根据不同用户数据集中的分数过滤查询集（例如，查找数据集1中得分> 5的所有音乐家和数据集2中的< 2）。

有没有办法这样做，还是整个事情都错了？

解决方案

编辑：nevermind，这是错的。我会保留它，以便您阅读，但之后我会删除。

您好，

如果我理解正确，可以尝试以下方式：

  artists = Musician.objects.select_related（'UserDataSets'）。 （Q（userdata__score_gt = 5，userdata__id = 1）| Q（userdata__sorce_lt = 2，userdata__id = 2）
  

有关如何使用Q的更多信息，请查看：使用Q对象的复杂查询。

Previously had a go at asking a more specific version of this question, but had trouble articulating what my question was. On reflection that made me doubt if my chosen solution was correct for the problem, so this time I will explain the problem and ask if a) I am on the right track and b) if there is a way around my current brick wall.

I am currently building a web interface to enable an existing database to be interrogated by (a small number of) users. Sticking with the analogy from the docs, I have models that look something like this:

class Musician(models.Model):
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    dob = models.DateField()

class Album(models.Model):
    artist = models.ForeignKey(Musician)
    name = models.CharField(max_length=100)

class Instrument(models.Model):
    artist = models.ForeignKey(Musician)
    name = models.CharField(max_length=100)

Where I have one central table (Musician) and several tables of associated data that are related by either ForeignKey or OneToOneFields. Users interact with the database by creating filtering criteria to select a subset of Musicians based on data the data on the main or related tables. Likewise, the users can then select what piece of data is used to rank results that are presented to them. The results are then viewed initially as a 2 dimensional table with a single row per Musician with selected data fields (or aggregates) in each column.

To give you some idea of scale, the database has ~5,000 Musicians with around 20 fields of related data.

Up to here is fine and I have a working implementation. However, it is important that I have the ability for a given user to upload there own annotation data sets (more than one) and then filter and order on these in the same way they can with the existing data.

The way I had tried to do this was to add the models:

class UserDataSets(models.Model):
    user = models.ForeignKey(User)
    name = models.CharField(max_length=100)
    description = models.CharField(max_length=64)
    results = models.ManyToManyField(Musician, through='UserData')

class UserData(models.Model):
    artist = models.ForeignKey(Musician)
    dataset = models.ForeignKey(UserDataSets)
    score = models.IntegerField()

    class Meta:
        unique_together = (("artist", "dataset"),)

I have a simple upload mechanism enabling users to upload a data set file that consists of 1 to 1 relationship between a Musician and their "score". Within a given user dataset each artist will be unique, but different datasets are independent from each other and will often contain entries for the same musician.

This worked fine for displaying the data, starting from a given artist I can do something like this:

artist = Musician.objects.get(pk=1)
dataset = UserDataSets.objects.get(pk=5)
print artist.userdata_set.get(dataset=dataset.pk)

However, this approach fell over when I came to implement the filtering and ordering of query set of musicians based on the data contained in a single user data set. For example, I could easily order the query set based on all of the data in the UserData table like this:

artists = Musician.objects.all().order_by(userdata__score)

But that does not help me order by the results of a given single user dataset. Likewise I need to be able to filter the query set based on the "scores" from different user data sets (eg find all musicians with a score > 5 in dataset1 and < 2 in dataset2).

Is there a way of doing this, or am I going about the whole thing wrong?

解决方案

edit: nevermind, it's wrong. I'll keep it so you can read, but then I'll delete afterward.

Hi,

If I understand correctly, you can try something like this:

artists = Musician.objects.select_related('UserDataSets').filter( Q(userdata__score_gt=5, userdata__id=1) | Q(userdata__sorce_lt=2, userdata__id=2 ) 

For more info on how to use Q, check this: Complex lookups with Q objects.

这篇关于与多个领域的多对多关系是我工作的正确工具？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！