junal on the run

Searching for a place that has already been found

Archive for the ‘GQL’ category

Getting Distinct Result From Google App Engine GQL Query

October 24th, 2009

One of the big limitations of Google App Engine is query syntax. Like SQL we don’t have all freedom in GQL. As an example, we cannot get DISTINCT result like following SQL query…

“Select DISTINCT name from users”

Let’s say we want to select distinct city from our datastore in Google App Engine application

How can we do it ?

One of the solutions could be using set() function

Example:

>>> basket = ['apple', 'orange', 'apple', 'pear', 'orange', 'banana']
>>> fruit = set(basket) # create a set without duplicates
>>> fruit
set(['orange', 'pear', 'apple', 'banana'])

This is possible in Python and GAE….but remember first you have to create an array which required a loop to run and then use the set() function. Certainly, it can’t be a good solution.

Here is a better way, let’s create a custom function that will return unique result or distinct result.

  def unique_result(array):
   unique_results = []
   for obj in array:
   if obj.city not in unique_results:
   unique_results.append(obj.city)
   return unique_results

This function will return distinct result from an array result.

Example : (how to call it…)

result = db.GqlQuery(“SELECT * FROM table_name order by date DESC limit x”)
distinct_result = unique_result(result)

Well, so far i think this is the better way to get distinct data in GAE GQL query. Let me know if you have any better way for it. I will update it here