Group-by and serializers

[hard harness]

Hello everyone,

I am trying to do something like a group by in SQL but with the Django ORM.
In addition, I am using Django Rest Framework and I would like the result of the group by be sent to a serializer to create an API.

Here are my models :

class Host(models.Model):
    hostname = models.CharField(max_length=255)
    operating_system = models.CharField(max_length=255, null=True, blank=True)


class Software(models.Model):
    name = models.CharField(max_length=255)
    version = models.CharField(max_length=255, null=True, blank=True)
    host = models.ForeignKey(Host, on_delete=models.CASCADE)

Here are my serializers :

class HostSerializer(serializers.ModelSerializer):
    class Meta:
        model = Host
        exclude = ('id',)

class SoftwareSerializer(serializers.ModelSerializer):
    host = HostSerializer()
    
    class Meta:
        model = Software
        exclude = ('id',)

I would like to retrieve the distinct software (with the version of each one), and the hosts that have these software installed.

I saw somewhere that I may use aggregation. But the aggregation return a dict, not an object.

What I am thinking about is something like that :

class TwickedSoftwareSerializer(serializers.ModelSerializer):
    host = HostSerializer()
    
    class Meta:
        model = Software
        exclude = ('id',)

    def to_representation(self, instance):
        representation = super().to_representation(instance)
        representation['host'] = [ host.hostname for host in Software.objects.filter(name=instance.name, version=instance.version).all()]
        return representation

class SoftwareView(ListAPIView):
    serialization_class = TwickedSoftwareSerializer
    queryset = Software.objects.all().distinct('name', 'version')
    

Regarding the distinct() function, I am using PostgreSQL as a database.
I do not know if this code meet my expectation as I did not test it.
But I think there is a better way as I need to do a lot of database query with this solution. So even if it work, it is not optimized.

What are you thinking about it ? Is there a better way ?

[sick dune]

Software.objects.values("host", "id").order_by("id").values("host__hostname", "name", "version")

can you give this a try, I honestly forget how to do GROUP BY queries with ORM

maybe someone else can help better

[mossy rampart]

Hello everyone. I have a list of incidents in my database, that are linked to a building. I have a view to retrieve them with DRF. Now I would like to provide another view that allows me to retrieve them grouped by name. I mean, if they have the same name and description, I would like to merge them and display all the buildings where the incident occurred . I have a postgresql as a database.

• Is the Building model linked to from anywhere (e.g. a foreign key on Host?)
• Can you provide sample JSON that you'd like as output?

[hard harness]

Just forget the buildings example, and focus on the Host and software example above. The software is linked to a host with a foreign key. As an output, I would like something like :

[
{
"Software": "Discord",
"Version": "12.5.1a-dev",
"Hosts": [
"Foo.bar.lan", "foo2.bar.lan", "foo3.bar.lan"
]
}
]

Sorry I am on my mobile phone

[mossy rampart]

Yeah, so when you have foreign keys (e.g. Host to Software), you can use the reverse relation set in relational serializers. The way your data your data is structured, I think you are indeed better off just building a list yourself.

I'd probably do something like this:

class SoftwareDetails:
    def __init__(self, name, version, hosts):
        self.name = name
        self.version = version
        self.hosts = hosts

class SoftwareDetailsSerializer(serializers.Serializer):
    name = serializers.CharField()
    version = serializers.CharField()
    hosts = serializers.ListField(child=serializers.CharField())

class SoftwareDetailsView(APIView):
    def get(self, request, name, version, format=None):
        name = self.kwargs["name"]
        version = self.kwargs["version"]
        hosts = [sw.host.hostname for sw in Software.objects.filter(name=name, version=version).select_related("host")]
        data = SoftwareDetails(name, version, hosts)
        return Response(SoftwareDetailsSerializer(data).data)