CODEWITHSUNDEEP
djangodjango-models
sherpaurgen
sherpaurgen

Reputation: 3274

how to write join query in django?

I have googled several hours but couldn't understand how to write sql join(raw or ORM) related queries.

Below is my model with two tables sandBox1 and licenseType where they will have common item "email" on which join will be performed

class sandBox1(models.Model):
    email = models.EmailField(unique=True)
    name = models.CharField(max_length=200)
    website = models.TextField(validators=[URLValidator()])
    comment = models.TextField(default='-')
    gender = models.CharField(max_length=6)
    def __str__(self):
        return self.email

class licenseType(models.Model):
    #1=other, 2=two-wheeler 4=four-wheeler
    licenseId = models.IntegerField()
    email = models.EmailField()

template file : index.html

<html><form id="form1" method="post" action="{% url "sandbox" %}">
   {% csrf_token %}
  Name: <input type="text" name="name" >
  <br><br>
  E-mail: <input type="text" name="email">
  <br><br>
  Website: <input type="text" name="website" >
  <span class="error"></span>
  <br><br>
  Comment: <textarea name="comment" rows="5" cols="40"></textarea>
  <br><br>
  Gender:
  <input type="radio" name="gender" value="female">Female
  <input type="radio" name="gender" value="male">Male
    <hr>Check the license type you have:-<br>
  <input type="checkbox" name="license[]" value=2 > 2 wheeler<br>
  <input type="checkbox" name="license[]" value=4 > 4 wheeler<br>
  <input type="checkbox" name="license[]" value=1 > Other <br>
  <br>
  <input type="submit" name="submit" value="Submit">
</form>
<div>
    {% for obj in sandBoxObj %}
        <p>
        {{ obj.name }}<br>
        {{ obj.email }}<br>
        {{ obj.website }}<br>
        {{ obj.gender }}<br>
        {{ obj.comment }}<br>
    {% endfor %}
</div>
</html>

here is a view file that needs correction. I want to show the result of this sql query: 

select sandBox1.email,sandBox1.name,licenseType.licenseId from sandBox1 
innerjoin licenseType on sandBox1.email=licenseType.email;

View file

def sandbox(request):
template_name='domdom.html'
sandBoxObj = sandBox1.objects.all()
context = { 'sandBoxObj':sandBoxObj }
print request.POST
if request.method == 'POST':
    website=request.POST.get('website','')
    comment=request.POST.get('comment','')
    name=request.POST.get('name','')
    gender=request.POST.get('gender','')
    email=request.POST.get('email', '')
    license=request.POST.getlist('license[]')
    for id in license:
        licInst = licenseType(licenseId=id,email=email)
        licInst.save()
    sbinstance = sandBox1(website=website,comment=comment,name=name,gender=gender,email=email)
    sbinstance.save()

    return render(request,template_name,context)

Upvotes: 2

Views: 305

Answers (2)

sahutchi
sahutchi

Reputation: 2233

Sorry if this answers the wrong question, but you may want to consider a different data model/ architecture. You are hardcoding SANDBOX1 which implies that there might be multiple Sandboxes and you are listing email fields which aren't tied to the User object. Some basic abstractions may simplify the work. Maybe something like:

from django.contrib.auth.models import User
...

class LicenseTypes(models.Model):
    name = models.CharField(max_length=500)
class Customer(models.Model):
    name = models.CharField(max_length=500)
    license = models.ForeignKey(LicenseType)
class RegisteredUser(models.Model):
    customer = models.ForeignKey(Customer, on_delete = models.CASCADE)
    user = models.ForeignKey(User)

I like this architecture better because it uses a lot more native django functionality. And makes joins really basic. Check this out in a view:

def django_view(request):
   registered_user = RegisteredUser(user=request.user)
   #example of how to use the join implicitly/ directly
   license = registered_user.customer.license.name

Upvotes: 0

sherpaurgen
sherpaurgen

Reputation: 3274

Raw sql method/ but im still confused on ORM method

def sandbox(request):
    template_name='domdom.html'
    sandBoxObj = sandBox1.objects.all()
    con = sqlite3.connect('/home/user1/PycharmProjects/djrest/invoicesproject/db.sqlite3')    #sqlite database file location
    cursor = con.cursor()
    cursor.execute(''' select todos_sandBox1.email,todos_sandBox1.name,todos_sandBox1.website,todos_sandBox1.comment,todos_sandBox1.gender,todos_licenseType.licenseId from todos_sandBox1 
join todos_licenseType on todos_sandBox1.email=todos_licenseType.email
 ''')  #it looks like django appends app name to table eg. appname = todos
    result = cursor.fetchall()
    #https://www.youtube.com/watch?v=VZMiDEUL0II
    context = { 'result':result }
    print request.POST
    if request.method == 'POST':
        website=request.POST.get('website','')
        comment=request.POST.get('comment','')
        name=request.POST.get('name','')
        gender=request.POST.get('gender','')
        email=request.POST.get('email', '')
        license=request.POST.getlist('license[]')
        for id in license:
            licInst = licenseType(licenseId=id,email=email)
            licInst.save()
        sbinstance = sandBox1(website=website,comment=comment,name=name,gender=gender,email=email)
        sbinstance.save()
    return render(request,template_name,context)

Upvotes: 1

Related Questions