On Google AppEngine what is the best way to merge two tables?

Question

If I have two tables, Company and Sales, and I want to display both sets of data in a single list, how would I do this on Google App Engine using GQL?

The models are:

class Company(db.Model):

   companyname = db.StringProperty()          
   companyid = db.StringProperty()
   salesperson = db.StringProperty()

class Sales(db.Model):

   companyid = db.StringProperty()

   weeklysales = db.StringProperty()

   monthlysales = db.StringProperty()

The views are:

def company(request): 
  companys = db.GqlQuery("SELECT * FROM Company")   
  sales = db.GqlQuery("SELECT * FROM Sales") 
  template_values = {
    'companys' : companys,
    'sales' : sales
  } 
  return respond(request, 'list', template_values)

List html includes:

{%for company in companys%}  
  {% for sale in sales %}    
    {% ifequal company.companyid sales.companyid %} 

    {{sales.weeklysales}}
    {{sales.monthlysales}}

    {% endifequal %}
     {% endfor %}

          {{company.companyname}}
          {{company.companyid}}
          {{company.salesperson}}

{%endfor%}

Any help would be greatly appreciated.

Answer 1

You should use a ReferenceProperty in your Sales model:

company = db.ReferenceProperty(Company)

An example on how to iterate through the sales for a given company:

company = db.GqlQuery("SELECT * FROM Company").fetch(1)[0]
for sale in company.sales_set:
    #Do something ...

Answer 2

{%for company in companys%}  
  {% for sale in sales %}    
    {% ifequal company.companyid sales.companyid %}

that code is problematic. If you have 200 companies and 1000 sales, you will be running that ifequal statement 200000 times!

In general, if you have more than 1000 sales or companies, this won't work at all, because you can only get 1000 items at a time from the datastore. (and if you're not planning on having more than 1000 items, app engine is probably overkill for your project)

I think your first goal should be to figure out how you want to break your list up into pages. Do you want to display 50 sales per page? or maybe 10 companies per page, along with all their respective sales? Once you decide on that, you can query for just the information you need.

Answer 3

You've said in a comment that there's a 1-1 relationship between sales and companies. So you could get the data in the same order:

def company(request): 
  companys = db.GqlQuery("SELECT * FROM Company ORDER BY companyid").fetch(1000)
  sales = db.GqlQuery("SELECT * FROM Sales ORDER BY companyid").fetch(1000)
  template_values = {
    'companys' : companys,
    'sales' : sales
  } 
  return respond(request, 'list', template_values)

{%for company in companys%}  
    {{sales[forloop.counter0].weeklysales}}
    {{sales[forloop.counter0].monthlysales}}

    {{company.companyname}}
    {{company.companyid}}
    {{company.salesperson}}
{%endfor%}

That's still not a great solution, though. If you're confident that the 1-1 relationship is correct, then I would just have a single entity containing all the information. If nothing else, it saves you worrying about database inconsistency where you create a company, but your attempt to create the corresponding sales data entity fails for some reason.