How to use left join orm?-CodePudding

My model

class Ad_company(models.Model):
    idx = models.AutoField(primary_key=True)
    subject = models.CharField(max_length=255)
    memo = models.CharField(max_length=255)
    content = models.TextField()
    is_display = models.CharField(max_length=1)
    writer = models.CharField(max_length=255)
    write_date = models.DateTimeField()
    update_date = models.DateTimeField()
    delete_date = models.DateTimeField()
    deadline_date = models.DateTimeField()
    reply = models.IntegerField(blank=True)
    hits = models.IntegerField(blank=True)
    ad_apply = models.IntegerField(blank=True)
    ad_category1 = models.CharField(max_length=255)
    ad_category2 = models.CharField(max_length=255)
    ad_place = models.CharField(max_length=255)
    ad_age = models.CharField(max_length=255)
    ad_sex = models.CharField(max_length=255)
    ad_budget = models.BigIntegerField()
    ad_length = models.CharField(max_length=255)
    is_done = models.CharField(max_length=1)
    is_pay = models.CharField(max_length=1)
    ad_service = models.CharField(max_length=255)
    ad_object = models.CharField(max_length=255)
    is_file = models.CharField(max_length=1)
    ad_require = models.CharField(max_length=255)

    class Meta:
        managed = False
        db_table = 'ad_write_company'


class Ad_company_apply(models.Model):
    idx = models.AutoField(primary_key=True)
    parent_idx = models.IntegerField()
    username = models.CharField(max_length=255)
    content = models.TextField()
    date = models.DateTimeField(default=datetime.now, blank=True)
    budget = models.BigIntegerField()
    term = models.IntegerField()
    is_done = models.CharField(max_length=1)


SELECT * FROM ad_write_company INNER JOIN ad_write_company_apply ON ad_write_company.idx = ad_write_company_apply.parent_idx where ad_write_company_apply.is_done = 1 and ad_write_company_apply.username = 'asdffdsa'

This is my query. but I can not make join query with orm. Sorry for question is too short.

And Is my query right?

I not sure of that. Thanks for answer.

or do you guys have other good idea?

CodePudding user response：

I would advise to work with a ForeignKey from Ad_company_apply to Ad_company. This makes it easier to generate queries in Django and will guarantee referential integrity.

It thus makes sense to rewrite the Ad_company_apply model to:

class Ad_company_apply(models.Model):
    # …
    parent_idx = models.ForeignKey(
        Ad_company,
        db_column='parent_idx',
        on_delete=models.CASCADE
    )
    # …

In that case, you can .filter(…) [Django-doc] with:

Ad_Company.objects.filter(ad_company_appy__isdone=1, ad_company_appy__username='asdffdsa')

Note: Models in Django are written in PascalCase, not snake_case, so you might want to rename the model from ~~Ad_company~~ to AdCompany.