Question 361563 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Django filter in other model without foreign key

I have two different models:

class AggUii(Model):
    year = models.IntegerField(blank=True, null=True)
    month = models.CharField(max_length=2, blank=True, null=True)
    country = models.CharField(max_length=7, blank=True, null=True)
    service = models.CharField(max_length=16, blank=True, null=True)
    doc_id = models.CharField(max_length=100, blank=True, null=True)
    counts = models.IntegerField(blank=True, null=True)
    data_type = models.CharField(max_length=200, blank=True, null=True)
    section_type = models.CharField(max_length=200, blank=True, null=True)
    yop = models.CharField(db_column='YOP', max_length=4, blank=True, null=True)  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'agg_uii'

class HierarchieIndexDocid(Model):
    ancestor = models.CharField(max_length=255)
    descendant_docid = models.CharField(max_length=255, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'hierarchie_index_docid'

As you can see, these tables aren’t connected by a Foreign Key. The field doc_id in the AggUii Model isn’t unique. And yet the below Mysql query is working, giving me the correct results.

Is there any way, I can rebuild this MySQL statement in Django ORM? The meta_id is passed as a parameter.

SELECT agg_uii.year, SUM(agg_uii.counts) FROM agg_uii,hierarchie_index_doc_id WHERE (agg_uii.doc_id=hierarchie_index_doc_id.descendant_docid and hierarchie_index_docid.ancestor="meta_id") GROUP BY agg_uii.year;

Thank you!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This is how I solved it:

    from django.db.models import Q, Subquery

    I made the following query:

    doc_id_hier_qs = HierarchieIndexDocid.objects.filter(~Q(descendant_docid=""),ancestor=meta_id).values('descendant_docid')

    After that, I used Django's Subquery method:

    invyearsqs_sq = AggUii.objects.force_index("idx_doc_id").filter(doc_id__in=Subquery(doc_id_hier_qs.values("descendant_docid"))).values('year').annotate(invSumYr = Sum('counts'))

    Works like a charm!


  2. 0 
    class AggUii(Model):
    # .
    doc_id = models.CharField(max_length=100, blank=True, null=True, unique=True)
    # .

    class Meta:
        managed = False
        db_table = 'agg_uii'


class HierarchieIndexDocid(Model):
    ancestor = models.CharField(max_length=255)
    descendant = models.ForeignKey(
        AggUii,
        to_field='doc_id',
        db_column='descendant_docid',
        blank=True,
        null=True,
        on_delete=models.PROTECT,
    )

    class Meta:
        managed = False
        db_table = 'hierarchie_index_docid'

    Then we can query with:

    from django.db.models import Sum

AggUii.objects.filter(hierarchieindexdocid__ancestor='meta_id').values(
    'year'
).annotate(total=Sum('counts')).order_by('year')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search