How to rewrite legacy raw sql query on django orm?

Question

Django

There is a legacy base, there is no possibility to remake it. I want to make a REST API for it on django.
With the help of inspectdb, the following models were obtained:

class Alert(models.Model):
    id = models.BigAutoField(primary_key=True)
    server_id = models.IntegerField()
    rule_id = models.BigIntegerField()
    level = models.SmallIntegerField(blank=True, null=True)
    timestamp = models.BigIntegerField()

    class Meta:
        managed = False
        db_table = 'alert'
        unique_together = (('id', 'server_id'),)


class Category(models.Model):
    cat_id = models.AutoField(primary_key=True)
    cat_name = models.CharField(unique=True, max_length=32)

    class Meta:
        managed = False
        db_table = 'category'


class Signature(models.Model):
    rule_id = models.BigIntegerField(unique=True)
    level = models.IntegerField(blank=True, null=True)
    description = models.CharField(max_length=255)

    class Meta:
        managed = False
        db_table = 'signature'


class SignatureCategoryMapping(models.Model):
    rule_id = models.BigIntegerField()
    # cat_id = models.IntegerField()
    cat_id = models.ForeignKey(Category, to_field='cat_id', 
                 db_column='cat_id', null=True, on_delete=models.SET_NULL, db_constraint=False)

    class Meta:
        managed = False
        db_table = 'signature_category_mapping'
        unique_together = (('id', 'rule_id', 'cat_id'),)

'
There is such a query to these tables:

a = Alert.objects.raw('''SELECT a.id, a.level, a.rule_id, q.cat_id, q.cat_name FROM 
        (SELECT rule_id, cat_name, scm.cat_id FROM signature_category_mapping AS scm, category AS c WHERE scm.cat_id = c.cat_id) 
        AS q, alert AS a WHERE (q.rule_id = a.rule_id) ORDER BY a.timestamp DESC LIMIT 10;''')

Events are selected from the alert table, then by rule_id in signature_category_mapping and from it to by cat_id in category to get cat_name.
The signature_category_mapping table contains rule_id and cat_id pairs, several cat_ids can belong to one rule_id, for example (1, 1) (1, 2), etc.
I failed to create a many to many relationship, django swears that rule_id is not a unique key.
Can you please tell me if it is possible to compose this request using django orm?

Answer 1

Yes, you can. Check out nested queries on django orm here and here