Querying

One of the more powerful aspects of the ORB framework is it's querying syntax. This language allows you to programmatically define complex queries for the system to process later.

Structure

The structure of a Query is as follows:

from orb import Query as Q
Q({column}) {op} {value}

The {column} value is the string name or field of a column, the {op} is the operator for the query, and the {value} is the value to compare.

Where possible, we use standard logical operators for the query class, which is instantiated with the Query class, commonly imported at Q.

Basic Operations

>>> from orb import Query as Q

>>> # common operators (available for all columns)
>>> Q(column) == value           # column equals value
>>> Q(column) != value           # column does not equal value
>>> Q(column) == None            # column is NULL
>>> Q(column) != None            # column is not NULL
>>> Q(column).in_((a, b, ..))    # column is in a list of values
>>> Q(column).notIn((a, b, ..))  # column not in a list of values

>>> # comparison operators (works for string, date, and numeric)
>>> Q(column) > value        # column greater than value
>>> Q(column) >= value       # column greater than or equal to value
>>> Q(column) < value        # column less than value
>>> Q(column) <= value       # column less than or equal to value
>>> Q(column).after(value)   # column comes after (gt) value
>>> Q(column).before(value)  # column comes before (lt) value
>>> Q(column).between(a, b)  # column is between a and b

>>> # string operators
>>> Q(column).contains(text)        # text is in column
>>> Q(column).doesNotContain(text)  # text is not in column
>>> Q(column).startswith(text)      # column starts with text
>>> Q(column).endswith(text)        # column ends with text
>>> Q(column).matches(exp)          # column regex matches text
>>> Q(column).doesNotMatch(exp)     # does not match regex

Query Functions

If you need to dynamically modify the column value during the query comparison, you can easily do that with ORB. These are functions that can be performed on a query object.

>>> Q(column).lower() == value.lower()  # lowercase the column value before comparing
>>> Q(column).upper() == value.upper()  # uppercase the column value before comparing
>>> abs(Q(column)) == value  # compare an absolute value of the column
>>> Q(column).asString() == value  # cast the column to a string value

You can also combine functions together:

>>> abs(Q(column)).asString() == '10'

Query Math

Similar to the query functions, you can also perform query math. This will do mathematical operations to the column during the query:

>>> Q(column) + offset == value  # add an offset to the column before comparing
>>> Q(column) - offset == value  # subtract an offset to the column before comparing
>>> Q(column) * offset == value  # multiply an offset to the column before comparing
>>> Q(column) / offset == value  # divide an offset to the column before comparing
>>> Q(column) & check == value  # perform a bitwise AND before comparing
>>> Q(column) | check == value  # perform a bitwise OR before comparing

A common example scenario of when to use query math is checking for whether or not a flag is set on an enumerated column:

>>> Q('flags') & Flags.Unique != 0

Query Compounds

Queries can also be logically strung together to make more complex lookups. This is done using the OR and AND logical operators.

General syntax is:

{query_a} & {query_b}   # AND
{query_b} | {query_b}   # OR

Some examples of how this can be put together are:

>>> from orb import Query as Q
>>> from intro import *

>>> # create some example users
>>> User.ensureExists({'username': 'jdoe'}, defaults={'password': '1', 'first_name': 'John', 'last_name': 'Doe'})
>>> User.ensureExists({'username': 'jadoe'}, defaults={'password': '2', 'first_name': 'Jane', 'last_name': 'Doe'})
>>> User.ensureExists({'username': 'jhancock'}, defaults={'password': '3', 'first_name': 'John', 'last_name': 'Hancock'}) 

>>> # create some example base queries
>>> john = Q('first_name') == 'John'
>>> jane = Q('first_name') == 'Jane'
>>> doe = Q('last_name') == 'Doe'

>>> # create some compound queries
>>> # john & doe => first_name is John and last_name is Doe
>>> User.select(where=john & doe).values('username')
['jdoe']

>>> # jane & doe => first_name is Jane and last_name is Doe
>>> User.select(where=jane & doe).values('username')
['jadoe']

You can invert a query using the ~ operator. This will do a mathematical inversion:

>>> # john & ~doe => first_name is John and last_name is not Doe
>>> User.select(where=john & ~doe).values('username')
['jhancock']

You can do multiple compounds together as well. Like math, parenthesis placement is important, as that will drive what gets calculated first:

>>> # (john | jane) & doe => first_name is John or Jane, and last_name is Doe
>>> User.select(where=(john | jane) & doe).values('username')
['jdoe', 'jadoe']

>>> # john | (jane & doe) => first_name is John, or first_name is Jane and last_name is Doe
>>> User.select(where=john | (jane & doe)).values('username')
['jdoe', 'jhancock', 'jadoe']

Warning:

You need to be careful with Python's & and | operators. They are processed first within an equation, and can cause trouble when building a query. For instance: q = Q('first_name') == 'John' & Q('last_name') == 'Doe' will actually raise an error, because Python will interpret this as: q = Q('first_name') == ('John' & Q('last_name')) == 'Doe', which will be an invalid query.

If you are doing in-line queries, make sure to contain them within parentheses: q = (Q('first_name') == 'John') & (Q('last_name') == 'Doe'))

Building Compounds

This flexible syntax provides a lot of power. However, it can be cumbersome for simple comparisons. If you want to just build a query with a key/value pair, you can use the Query.build classmethod:

>>> from orb import Query as Q

>>> # building the query manually
>>> q = (Q('first_name') == 'John') & (Q('last_name') == 'Doe')

>>> # using the build method
>>> q = Q.build(first_name='John', last_name='Doe')

Joining Models

Sometimes you will need to reference other tables within a query. For instance, let's consider the following structure:

import orb

class Login(orb.Table):
  id = orb.IdColumn()
  username = orb.StringColumn()
  password = orb.StringColumn()
  profile = orb.ReferenceColumn(reference='Profile')

class Profile(orb.Table):
  id = orb.IdColumn()
  first_name = orb.StringColumn()
  last_name = orb.StringColumn()
  department = orb.StringColumn()
  title = orb.StringColumn()

In the above example, we have a one-to-many relationship between Profile and Login -- where one user profile could have multiple logins associated with it.

Let's say we wanted to lookup all logins where their associated Profile's title is "Manager".

How would we build that query?

Joining via Shortcuts

The easiest way to do this is through a dot-notated shortcut. You have actually already seen it in use when defining ReverseLookup and Pipe classes. This is the easiest and preferred method for joining. You simply traverse the column links until you get to the column you want to filter on:

>>> from orb import Query as Q
>>> q = Q('profile.title') == 'Manager'
>>> Login.select(where=q)

The profile column on the Login model is a reference to a Profile object. You could query by any column of the Profile object (profile.first_name, profile.last_name, etc.).

Querying using collections

Another approach to this same problem is to query using collections. Say for instance, we already have a lookup for Profile records, and now we want to know what Login's are associated with them. For instance:

>>> profiles = Profile.select(where=Q('title') == 'Manager')
>>> logins = Login.select(where=Q('profile').in_(profiles))

Normally, the in_ method will convert the value to a list for the backend to compare. When the value is actually a collection it can be optimized a bit more because we know how to populate that collection on the backend.

Tip:

When using in_ or notIn on a collection of records, it is best to pass in the collection itself so that ORB can optimize the lookup.

Joining via Model-Specific Queries

Another approach to joining is to create model-specific queries. By default, a query is created by simply providing a column name. It won't actually be associated to a model until that query is applied within a select. In this way, you can actually build a query that could be reused across multiple models, as long as they each have the same column. (Think if you are searching through multiple model's that have a 'title' column).

Model-specific queries are created by providing both the model and the column to lookup. You can manually build joins in this fashion by using the output of one query to be the value of another.

Consider this model structure:

import orb

class User(orb.Table):
  id = orb.IdColumn()
  username = orb.StringColumn()
  password = orb.StringColumn()

  groups = orb.Pipe(through_path='GroupUser.user.group')

class GroupUser(orb.Table):
  id = orb.IdColumn()
  user = orb.ReferenceColumn(reference='User')
  group = orb.ReferenceColumn(reference='Group')

class Group(orb.Table):
  id = orb.IdColumn()
  name = orb.StringColumn()

  users = orb.Pipe(through_path='GroupUser.group.user')

Now, if I want to lookup all users who are a member of the 'admin' or 'editor' group -- how would I do this?

>>> from orb import Query as Q
>>> q = Q(User, 'id') == Q(GroupUser, 'user')
>>> q &= Q(GroupUser, 'group') == Q(Group, 'id')
>>> q &= Q(Group, 'name').in_(('admin', 'editor'))
>>> User.select(where=q)

The traversal here reads "select from User where the User 'id' equals the GroupUser 'user' column. Then look for where the GroupUser 'group' column equals the Group 'id' column. Finally, look for the Group 'name' if it is in the tuple."

This is a bit more complicated, but also more flexible when building complex queries.

But wait, why can't I just use a dot-notated shortcut? Actually, you can. We just wanted to show you how to do it manually...

This query can actually also be represented as:

>>> from orb import Query as Q
>>> q = Q('groups.name').in_(('admin', 'editor'))
>>> User.select(where=q)

As you can see, you can also traverse collectors. In this case, a User's groups pipe will already do the traversal through the intermediary table. From there you just need to filter on the group's name property.