CODEWITHSUNDEEP
djangodjango-models
azeemshaikh38
azeemshaikh38

Reputation: 191

Django: join two tables

Here's what I currently have:

class Sessions(models.Model):
    sessionId = models.AutoField(primary_key=True)

class Ip(models.Model):
    ipId = models.AutoField(primary_key=True)

class Affiliation(models.Model):
    affiliationId = models.AutoField(primary_key=True)
    ip = models.ForeignKey("Ip", null=False, db_column="ipId")
    session = models.ForeignKey("Sessions", null=False, db_column="sessionId")

Now I want to find the Sessions QuerySet which returns ipId=x. Basically this is what I'm trying:

Sessions.objects.filter(affiliation__ip=x)

Is there any way I can do this in django?

Upvotes: 4

Views: 26057

Answers (4)

Viraj Wadate
Viraj Wadate

Reputation: 6133

I want to show a report where I want customer_name, mobile_no, from Details table and status from Status table. You can see my model

class Details(models.Model):
    customer_name = models.CharField(max_length=100,unique=True)
    mobile_no = models.IntegerField()

class Status(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    status = models.CharField(max_length=25)
    purchase = models.ForeignKey(Details, null=True, on_delete=models.SET_NULL)

Solution

Step1: Add the following code to your view. [views.py]

def customer_report(request):
    status_obj = Status.objects.all()
    return render('reports/customer_report.html', {'status_obj':status_obj})

Step2: Add the following code at your template side [report.html]

<table class="table">
    <thead>
      <tr>
        <th>Name</th>
        <th>Mobile No</th>
        <th>Status</th>
      </tr>
    </thead>
    <tbody>
        {% for data in status_obj  %}
          <tr>
            <td>{{data.purchase.customer_name}}</td>
            <td>{{data.purchase.mobile_no}}</td>
            <td>{{data.status}}</td>
          </tr>
        {% endfor %}
    </tbody>
</table>

Upvotes: 2

Nuno Salgado
Nuno Salgado

Reputation: 46

I think if you use a related_name in Affiliation model like this:

class Sessions(models.Model):
    sessionId = models.AutoField(primary_key=True)

class Ip(models.Model):
    ipId = models.AutoField(primary_key=True)

class Affiliation(models.Model):
    affiliationId = models.AutoField(primary_key=True)
    ip = models.ForeignKey("Ip", null=False, db_column="ipId")
    session = models.ForeignKey(
        "Sessions", null=False, 
        db_column="sessionId", 
        related_name="affiliation_session"
    )

Now you can filter:

Sessions.objects.filter(affiliation_session__ip_id=X)

You can use select_related for optimization if necessary

Upvotes: 2

azeemshaikh38
azeemshaikh38

Reputation: 191

Found the answer!

psobjs = Affiliation.objects.filter(ipId=x)
queryset = Sessions.objects.filter(sessionId__in=psobjs.values('sessionId'))

Upvotes: 11

Yeo
Yeo

Reputation: 11784

Refactor your code

Apologize, Please bear with me and learn from it.

You don't have to explicitly specify the id in Django. unless your class is unmanaged. Django automatically use id or pk to refer to it.

class Sessions(models.Model):
    pass

Model naming usually Singular Session.

class Session(models.Model):
    pass

Watch out for conflict model name, Session is already used internally by Django django.contrib.sessions.models.Session. Use alternative name such as ClientSession would be better.

class ClientSession(models.Model):
    pass

Use ManyToManyField (optional), it is just a helper to simplify your queryset.

class ClientSession(models.Model):
    client_source = models.ManyToManyField('IP', through='Affiliation')

Use GenericIPAddressField

class IP(models.Model):
    address = models.GenericIPAddressField()

You have no additional attribute in Affiliation, thus you can consider removing it.

class ClientSession(models.Model):
    client_source = models.ManyToManyField('IP')

Final Model

class ClientSession(models.Model):
    client_source = models.ManyToManyField('IP')

class IP(models.Model):
    address = models.GenericIPAddressField()

Answer

Querying Session is very straight forward with the current Model.

x = 1 # Some ID
ClientSession.objects.filter(ips__id=x)

Upvotes: 8

Related Questions