In the Google App Engine, how can I find the last order for a customer in GQL?

Question

If I have two tables, clients and orders, and I want to find the last order for a client, how can I do this in Google App Engine using GQL?

I usually join these two tables through the foreign key customer_id, which exists in the order table.

select orders.* from customers, orders where customers.customer_id = orders.customer_id and orders.order_id = (select top 1 sub_orders.order_id from orders sub_orders where sub_orders.customer_id = orders.customer_id order by sub_orders.order_date desc) 

However, since merging does not seem possible in the Google App Engine, I'm not sure how to get around this limitation. Any suggestions would be appreciated.

Answer 1

The DataStore in the Google App Engine is really very different from a relational database. There are several similarities, but it is important to understand the differences when developing a data model.

As you usually define this type of relationship, use reference properties:

 class Customer(db.Model): name = db.StringProperty() class Order(db.Model): customer = db.ReferenceProperty( Customer, collection_name = 'orders' ) 

The ReferenceProperty in the definition of the order object leads to the creation of a property in the Customer object with the name "orders", so if the "customer" is an instance of Customer, you can find all orders by contacting the "customer". orders.

For instance:

 customer = Customer.gql("WHERE name = :1", "Bob")[0] # Returns the first customer named Bob order1 = customer.orders[0] order2 = customer.orders.order("date")[0] # Sorts the Orders by date and gets the first one 

Reference properties are documented here.

Another important concept to understand is the idea of ​​Entity groups. Objects in Entity Groups are stored in the same node, and therefore they can be stored and retrieved more efficiently. They are also crucial to the use of transactions.