Acces to db made easy

- Prev: Browsing data
- Next: Filters

# Constraints

Constraints are a mean to limit browsing of data to a limited set. It’s a different concept from filtering, as this enforces a limitation while filtering allows the user to change the values at will.

## Django-like syntax

A constraint can be as simple as:

    t = SqlTable('movie', dbproxy=db)
    t.add_constraints(actors__country='ITA', genres__name='drama',
                      year__gte=1950, year__lte=1970)

And browsing of movies will be constrained into dramas with at least one Italian actor, produced between 1950 and 1970. The double underscore ‘__’ will allow spanning from one table to a related one (provided a relation was established at sqlalchemy level) and from attribute name to operator to be used.

Note

This syntax is inspired to django‘s ORM syntax. The same syntax can be used to add filters to the sqlwidget, to the completion and when `.reload()` method is used.

## query

limitation acts on `self.query` directly. Self query can be modified in any way accepted by `sqlalchemy` syntax

### operators

these operators are recognized:

    OPERATORS = {
        'notlike'    : 'not like',
        'like'       : 'like',
        'ilike'      : 'ilike',
        'notilike'   : 'not ilike',
        'iregexp'    : '~*',
        'regexp'     : '~',
        'notiregexp' : '!~*',
        'notregexp'  : '!~',
        'lt'         : '<',
        'gt'         :  '>',
        'lte'        : '<=',
        'gte'        : '>=',
        'eq'         : '=',
        'neq'        : '!=',
        'equal'      : '=',
        'in'         : 'IN',
        'notin'      : 'NOT IN',
        'null'       : 'IS NULL',
        'notnull'    : 'IS NOT NULL',
        }

and `icontains` that at the moment map to regexp operators if they exist or like with % on both sides.

### conjunctions

In the example all conditions have been `AND` ed. It’s possible to use `OR` operator adding `or_=True` argument.

### django2sqlalchemy

You can get a sqlalchemy `ClauseList` object starting from a django-like expression using django2sqlalchemy function:

    from sqlkit.db.django_syntax import django2sqlalchemy
    clause_list, join_paths = django2sqlalchemy(mapper, *args)

where `args` can be or_=boolean or any django-like query. Django2sqlalchemy returns a tuple: (clause_list, join_path):

> clause_list: | the list of column expression connected with `AND` or `OR` according to `or_` argument

> join_path:

> the list of path needed to add to query via `query.join()` to have all the fields the query needs to apply the filters in clause_list

> A possibility is to add:

>     for path in join_path:
>        query.join(path).reset_joinpoint()

### Filter simplified syntax

#### Django syntax utilities

This module provides some functions to allow use of django syntax in filters. You can look an introduction on lookup filters on django’s site but mind that we don’t even share the same syntax. We just borrowed the idea that the operator and possibly the join can be wired in the keyword name. The aim is not at all compatibility with django (that has nothing to do with this framework) but a way to:

> - make it easy to add filters and constraints
> - make it easy to store queries (that need to store filters and constraints)

These function are used in sqlfilter and in completion beside `.add_constraint(...)` method of sqlwidget.

You never need to use these functions. If you prefere you can changed the query directly with SQLAlchemy syntax, and surely there are situations where that’s necesssary, nevertheless there are situations where this syntax allows to obtain the same result in less and more readeable code, probably just one line.

All of these functions act on a mapper becouse it has info on PropertyLoaders. In completion, the mapper is used to follow the join_path even if the query (a `sqlalchemy.select`), will not be issued on a `session.query` object

##### supported lookup

like/ilike: | use like operator, ilike if available
notlike/notilike:
| negate like operator, ilike if available
regexp/iregexp: | now ~/~* for postgres and REGEX for mysql like again for the rest
notregexp/notiregexp:
| now !~/!~* (only for postgresql)
lt/gt: | less than / grater than (<, >)
lte/gte: | less than equal/ grater than equal(<=, >=)
eq/neq: | equal / not equal ( = , !=)
in/notin: | IN (argument must be a list)/ negated
null: | IS NULL / IS NOT NULL (depending if arg is True/False)

##### conjunctions

`django2sqlalchemy()` and `django2query()` connect all arguments with AND operator by default. It’s possible to use `OR` operator adding `OR=True` argument.

`sqlkit.db.django_syntax.``django2sqlalchemy`(mapper=None, table=None, OR=False, **kw)

  Return a tuple (ClauseList, join path).

  Parameters:

  - mapper – the mapper that will be used in the query
  - table – the mapper that will be used in the query
  - OR – if True the conditions will be ORed
  - kw – the conditions as per django-like syntax

`sqlkit.db.django_syntax.``django2query`(query, mapper_or_class, OR=False, aliased=False, OUTER=False, **kw)

  return a new query with new constraints expressed in ‘django-style’

  Parameters:

  - query – the query to which filters will be applied
  - mapper_or_class – a mapper or a class from which the class will be devised
  - aliased – passed to `DjangoParser`
  - OUTER – `DjangoParser` will be instantiated as an OUTER join

## Native sqlalchemy constraint

The django syntax is in no way the only possibility. If that is not sufficient to express the constraints you need you can just use any filter on the query directly:

    tk_tbl = ticket.Ticket.__table__
    my_id = self.db.get_session().query(ticket.User).filter_by(username=setup.USERNAME).one().id

    t = sk.SqlTable(ticket.Ticket, field_list=field_list, order_by='priority', **self.meta)
    t.add_filter(status=1)
    t.query = t.query.filter(or_(tk_tbl.c['assigned_by_id'] == my_id ,
                                 tk_tbl.c['assigned_to_id'] == my_id , ))

this example shows how to force a constraint on ticket requiring that the ticket be assigned by or to USERNAME. Any further filter applied interactively will be applied on top of this constraint. This is equivalent to writing:

    t.add_constraint(assigned_by_id=my_id, assigned_to_id=my_id, OR=True)

At the moment of this writing it’s not possible to write:

    t.add_constraint(assigned_by_id__username=USERNAME)

as the foreign key relation is not followed in this context.

### aliased constraints

Sometimes you need to have aliased constraints in order to ha sqlalchemy build an aliased join to related classes on which you may want to set constraints. This may be necessary if you want to use constraints along with order_by and possibly filters on foreign keys.

#### example

Suppose you have an ‘address’ table with a `user_id` field that is a ForeignKey to a `user.id` column. Suppose you want to open a SqlTable on table addresses, ordered on `user_id` (well... surely you don’t want to order by the `id` value, you probably have a `first_name` field that is much more appropriate for sorting).

Now suppose you also have an `active` field on the user_table. You can achieve this (simple) in the following way:

    t = SqlTable('address', order_by='user_id__first_name', ...)
    t.add_constraint(user_id__active=True, aliased=True)

the order_by argument implicitly have built a join with the user table, the same stands for the constraint `user_id__active`. Sqlalchemy would have complained that the table ‘user’ was already present, so you need to alias it. I’ll probably make this the default in a future version.