Getting data from the database with python(on Django framework)

Question

ordinarily if I were writing a sql statement to this I would do something like this,

    SELECT * FROM (django_baseaccount LEFT JOIN django_account ON django_baseaccount.user_id = django_account.baseaccount_ptr_id)
LEFT JOIN django_address ON django_account.baseaccount_ptr_id = django_address.user_id;name 

how do I put this into the Djagno way of querying the database using the API, i.e.

TradeDownloads.objects.filter(online=1)[:6]

My models BASE ACCOUNT

class BaseAccount(models.Model):
user = models.ForeignKey(User, unique=True)

def __unicode__(self):
    """
    Return the unicode representation of this customer, which is the user's
    full name, if set, otherwise, the user's username
    """
    fn = self.user.get_full_name()
    if fn:
        return fn
    return self.user.username

def user_name(self):
    """
    Returns the full name of the related user object
    """
    return self.user.get_full_name()

def email(self):
    """
    Return the email address of the related user object
    """
    return self.user.email

ACCOUNT

class Account(BaseAccount):
"""
The account is an extension of the Django user and serves as the profile
object in user.get_profile() for shop purchases and sessions
"""
telephone = models.CharField(max_length=32)
default_address = models.ForeignKey(Address, related_name='billing_account', blank=True, null=True)
security_question = models.ForeignKey(SecurityQuestion)
security_answer = models.CharField(max_length=200)
how_heard = models.CharField("How did you hear about us?", max_length=100)
feedback = models.TextField(blank=True)
opt_in = models.BooleanField("Subscribe to mailing list", help_text="Please tick here if you would like to receive updates from %s" % Site.objects.get_current().name)
temporary = models.BooleanField()

def has_placed_orders(self):
    """
    Returns True if the user has placed at least one order, False otherwise
    """
    return self.order_set.count() > 0

def get_last_order(self):
    """
    Returns the latest order that this customer has placed. If no orders
    have been placed, then None is returned
    """
    try:
        return self.order_set.all().order_by('-date')[0]
    except IndexError:
        return None

def get_currency(self):
    """
    Get the currency for this customer. If global currencies are enabled
    (settings.ENABLE_GLOBAL_CURRENCIES) then this function will return
    the currency related to their default address, otherwise, it returns
    the site default
    """
    if settings.ENABLE_GLOBAL_CURRENCIES:
        return self.default_address.country.currency
    return Currency.get_default_currency()
currency = property(get_currency)

def get_gateway_currency(self):
    """
    Get the currency that an order will be put through protx with. If protx
    currencies are enabled (settings.ENABLE_PROTX_CURRENCIES), then the
    currency will be the same returned by get_currency, otherwise, the
    site default is used
    """
    if settings.ENABLE_PROTX_CURRENCIES and settings.ENABLE_GLOBAL_CURRENCIES:
        return self.currency
    return Currency.get_default_currency()
gateway_currency = property(get_gateway_currency)

ADDRESS

class Address(models.Model):
"""
This class encapsulates the data required for postage and payment mechanisms
across the site. Each address is associated with a single store account
"""
trade_user = models.BooleanField("Are you a stockist of N  Products", help_text="Please here if you are a  Stockist")
company_name = models.CharField(max_length=32, blank=True)
line1 = models.CharField(max_length=200)
line2 = models.CharField(max_length=200, blank=True)
line3 = models.CharField(max_length=200, blank=True)
city = models.CharField(max_length=32)
county = models.CharField(max_length=32)
postcode = models.CharField(max_length=12)
country = models.ForeignKey(Country)
account = models.ForeignKey('Account')

class Meta:
    """
    Django meta options

    verbose_name_plural = "Addresses"
    """
    verbose_name_plural = "Addresses"

def __unicode__(self):
    """
    The unicode representation of this address, the postcode plus the county
    """
    return ', '.join((self.postcode, str(self.county)))

def line_list(self):
    """
    Return a list of all of this objects address lines that are not blank,
    in the natural order that you'd expect to see them. This is useful for
    outputting to a template with the aid of python String.join()
    """
    return [val for val in (self.line1, self.line2, self.line3, self.city, self.county, self.postcode, self.country.name) if val]

Answer 1

Forget the SQL. What do you want to achieve from this query? What do you want to do with the results?

You haven't posted your models. Do they have the foreign keys defined? Can you just do a simple query and use select_related() to get the joined objects?

Edited to add What was wrong with the answer given the previous time you asked this question?

Edited again but everyone has shown you how to get the item via the foreign key! Forget the id, you don't need it. If you have an Account object a, you just do a.default_address to get the actual Address object that is related. If that doesn't work, then you're not posting the right models, as that will definitely work with the models you have posted.

Answer 2

"ordinarily if I were writing a sql statement"

Welcome to ORM. You're not writing SQL so remove this from the question. Do not ever post SQL and ask how to translate SQL into ORM. Translating SQL limits your ability to learn. Stop doing it.

Write down what the result is supposed to be.

It appears that you are getting all Account objects. Period. At some point in a view function or template you want to get an Address, also.

for a in Account.objects.all():
    a.default_address # this is the address that SQL brought in via a "join".

That's it. Please actually do all the examples in the Django tutorial. Actually type the code from the examples and see how it works.

All "join" operations are SQL workarounds. They're a weird SQL-ism, and have nothing to do with the underlying objects. So stop using SQL terminology to describe what you want.

Answer 3

Django provides a clean way to fall-back to native SQL for complex queries see the official documentation: Performing raw SQL queries