Making queries

You have the ability to query collections object via a the filter() method. This method accept a chain of arguments that conditionally return matching objects that can be ordered by fields using the order_by argument.

The filter() method can be used on users, flows, and table('table_name') in the exact same way.

📘

String filters are case sensitive.

# add some orders to the "order" table, then query the collection
import time
orders = [
  {
    'item': 'Fish burrito',
    'category': 'special',
    'price': 8.99,
    'created_at': time.time(),
    'user_id': self.db.user.id
	},
  {
    'item': 'Chicken quesadilla',
    'category': 'regular'
    'price': 6.99,
    'created_at': time.time(),
    'user_id': self.db.user.id
	}
]
# iterate over the list and add each order to the database
for order in orders:
	self.db.table('orders').add(order)

# retrieve the user's orders, ordered by recency
orders = self.db.table('orders').filter(user_id=self.db.user.id,
                                        category='special',
                                        price__lt=10,
                                        order_by=['-created_at'])

[
  {
    "category": "special",
    "user_id": "Uxxxxxxxx",
    "created_at": 1461852493.507162,
    "id": "Oxxxxxxxxxx",
    "item": "Fish burrito",
    "price": 8.99,
    "bot_id": "_Bxxxxxxxxx"
  }
]

You can also filter your users collection for some useful results.

🚧

We are manually adding users in this example for demonstration purposes, but typically these users would be added automatically as they use your bot.

# build a sample database
users = [
  {
    'name': "Elon",
    'skills': ["Python", "ML"]
  },
  {
    'name': "Bill",
    'skills': ["C#", ".NET"]
  },
  {
    'name': "Guido",
    'skills': ["Python"]
  }
]
for user in users:
  self.db.users.add(user)

# query the user database for users with Python skills
users = self.db.users.filter(skills__contains="Python")

[
  {
    "skills": [
      "Python"
    ],
    "id": "U111111111",
    "name": "Guido",
    "bot_id": "Bxxxxxxxx"
  },
  {
    "skills": [
      "Python",
      "ML"
    ],
    "id": "U222222222",
    "name": "Elon",
    "bot_id": "Bxxxxxxxx"
  }
]

Conditional expressions

When building a filter, you will chain a list of conditions, there is the most basic condition field=value, however, you can use more advanced expressions like field__lt=value to find matching objects less than (ie. field < value).

The usage for conditional expressions is field__condition. So for example, price__lt=100 finds all rows where price < 100. price is the field and lt is the expression. See the full list of available expressions below:

📘

You can simply write field=value, which return the exact match. Equivalent to field__eq=value.

Ordering results

You can pass an optional parameter order_by that lists a series of field names to order your result. Pass the field name as a string and prepend with "-"

📘

Prepend your filed with "-" to reverse your sort. Example: order_by=['-price'] will return the most expensive items first.

# orders by "age" DESC then by "name" ASC
self.db.users.filter(age__gt=19, order_by=['-age', 'name'])