How to formulate a query over two models?

[Mark Phillips]

I have two models:

# MetaData

class MetaData(models.Model):

    metadata_id = models.AutoField(primary_key = True)

    name = models.CharField('metadata name', max_length=200, unique=True)

    description = models.TextField('description')

    def __str__(self):

        return self.name

# MetaData Value

class MetaDataValue(models.Model):

    metadata_id = models.ForeignKey(MetaData, on_delete=models.CASCADE,)

    value = models.CharField('value', max_length=200, unique=True)

    def __str__(self):

        return self.value

I want to construct a query set such that I get a dictionary back that looks like this:

{name1: [value1, value2,..], name2:[value3, value4,..]..}

In other words, a dictionary that has the metadata name as the key and the associated values for that name in a list.

I have been playing around with MetaData.objects.all(), but I don't see how to relate the two models and get something close to what I want.

Thanks!

Mark

[Christophe Pettus]

> On Apr 11, 2018, at 16:05, Mark Phillips <ma...@phillipsmarketing.biz> wrote:
>
> I have two models:
>
> # MetaData

> # MetaData Value

First, you might want to make sure this is *really* the best way of representing your data:

http://karwin.blogspot.com/2009/05/eav-fail.html

That being said, you might consider getting rid of the MetaDataValue table, and putting the values in as a JSON field on MetaData.

--
-- Christophe Pettus
x...@thebuild.com

[Mark Phillips]

Thanks for the link - I have read it before. I need to stay with these models, so is there a simple query to get my result, or do I have to make multiple queries and combine the data in python?

Thanks,

Mark

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8B07A737-9B74-4389-8423-A6CD3C7BCA58%40thebuild.com.
For more options, visit https://groups.google.com/d/optout.

[Christophe Pettus]

> On Apr 11, 2018, at 16:19, Mark Phillips <ma...@phillipsmarketing.biz> wrote:
>
> Thanks for the link - I have read it before. I need to stay with these models, so is there a simple query to get my result, or do I have to make multiple queries and combine the data in python?

If you are using PostgreSQL, you can do it as a raw SQL query; otherwise, you'll need to transform the data in Python.

[Simon Charette]

Hello there,

If you are using PostgreSQL you can achieve it using ArrayAgg[0]

queryset = MetaData.objects.annotate(values=ArrayAgg('metadatavalue__value'))
map = dict(queryset.values_list('name', 'values'))

Cheers,
Simon

[0] https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/aggregates/