CODEWITHSUNDEEP
djangodjango-admin
tigerJK
tigerJK

Reputation: 35

Django ORM SELECT with join sql

I'm trying to get the data by doing a join with django ORM. But there is a problem as below.

Models in django:

class LawyerCounsel(models.Model):
    lawyer_idx = models.AutoField(primary_key=True)
    lawyer_name = models.CharField(max_length=50)
    lawyer_status = models.CharField(max_length=1)
    register_date = models.DateTimeField()
    lawyeridx = models.ForeignKey(CounselAnswer, to_field='lawyer_idx',
unique='true', db_column="counsel_answer_idx", on_delete="models.DO_NOTHING")

        class Meta:
            managed = False
            db_table = 'lawyer'


class CounselAnswer(models.Model):
    counsel_answer_idx = models.AutoField(primary_key=True)
    counsel_idx = models.IntegerField()
    lawyer_idx = models.IntegerField()
    counsel_answer_title = models.CharField(max_length=255)
    counsel_answer_contents = models.TextField()
    counsel_seleted = models.CharField(max_length=1)
    counsel_answer_agree = models.IntegerField()
    lawyer_ip = models.CharField(max_length=20, blank=True, null=True)
    counsel_answer_delay_time = models.IntegerField()
    register_date = models.DateTimeField()
    update_date = models.DateTimeField()

    class Meta:
        managed = False
        db_table = 'counsel_answer'

I need to select from DB

stats_data_test = LawyerCounsel.objects.filter(register_date=yearmonth).order_by("cnt")

May I use django ORM to make this selection? (for example in SQL select looks like this)

select * from (
            select 
                y.lawyer_name,
                DATE_FORMAT(y.register_date, '%Y-%m-%d') as reg_date,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903') as cnt,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 1) as cnt1,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 2) as cnt2,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 3) as cnt3,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 4) as cnt4,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 5) as cnt5,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 6) as cnt6
            from lawyer y
        where y.lawyer_status = 'N'
        ) A order by A.cnt desc;

Upvotes: 0

Views: 109

Answers (1)

Joshua Taylor Eppinette
Joshua Taylor Eppinette

Reputation: 812

Yes, you can do this. You need to annotate those select subqueries onto your model using the Django QuerySet .annotate functionality.

After the value is annotated onto the QuerySet, it will be accessible to .order_by.

Upvotes: 1

Related Questions