[Sqlalchemy-commits] [1452] sqlalchemy/branches/schema/test: refactored select_by to be simpler and
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1452] sqlalchemy/branches/schema/test: refactored select_by to be simpler and probably less buggy.
|
From: <co...@sq...> - 2006-05-13 08:01:09
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head><style type="text/css"><!-- #msg dl { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; } #msg dt { float: left; width: 6em; font-weight: bold; } #msg dt:after { content:':';} #msg dl, #msg dt, #msg ul, #msg li { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; } #msg dl a { font-weight: bold} #msg dl a:link { color:#fc3; } #msg dl a:active { color:#ff0; } #msg dl a:visited { color:#cc6; } h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; } #msg pre { overflow: auto; background: #ffc; border: 1px #fc0 solid; padding: 6px; } #msg ul, pre { overflow: auto; } #patch { width: 100%; } #patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;} #patch .propset h4, #patch .binary h4 {margin:0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;} #patch .propset .diff, #patch .binary .diff {padding:10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;} #patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;} #patch .lines, .info {color:#888;background:#fff;} --></style> <title>[1452] sqlalchemy/branches/schema/test: refactored select_by to be simpler and probably less buggy.</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1452</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-05-13 03:00:52 -0500 (Sat, 13 May 2006)</dd> </dl> <h3>Log Message</h3> <pre>refactored select_by to be simpler and probably less buggy. added join_by, join_via and join_to functions which provide more modular components of the select_by operation.</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemybranchesschemadocbuildcontentdatamappingtxt">sqlalchemy/branches/schema/doc/build/content/datamapping.txt</a></li> <li><a href="#sqlalchemybranchesschemalibsqlalchemyormpropertiespy">sqlalchemy/branches/schema/lib/sqlalchemy/orm/properties.py</a></li> <li><a href="#sqlalchemybranchesschemalibsqlalchemyormquerypy">sqlalchemy/branches/schema/lib/sqlalchemy/orm/query.py</a></li> <li><a href="#sqlalchemybranchesschematestmapperpy">sqlalchemy/branches/schema/test/mapper.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemybranchesschemadocbuildcontentdatamappingtxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/datamapping.txt (1451 => 1452)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/datamapping.txt 2006-05-13 01:18:04 UTC (rev 1451) +++ sqlalchemy/branches/schema/doc/build/content/datamapping.txt 2006-05-13 08:00:52 UTC (rev 1452) </span><span class="lines">@@ -382,9 +382,9 @@ </span><span class="cx"> </span><span class="cx"> print repr(a) </span><span class="cx"> </span><del>-##### Useful Feature: Creating Joins via select_by {@name=relselectby} </del><ins>+##### Useful Feature: Creating Joins Across Relations {@name=relselectby} </ins><span class="cx"> </span><del>-For mappers that have relationships, the `select_by` method of the Query object and its cousins include special functionality that can be used to create joins. Just specify a key in the argument list which is not present in the primary mapper's list of properties or columns, but *is* present in the property list of one of its relationships: </del><ins>+For mappers that have relationships, the `select_by` method of the Query object can create queries that include automatically created joins. Just specify a key in the argument list which is not present in the primary mapper's list of properties or columns, but *is* present in the property list of one of its relationships: </ins><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> {sql}l = session.query(User).select_by(street='123 Green Street') </span><span class="lines">@@ -404,6 +404,34 @@ </span><span class="cx"> Address.c.street=='123 Green Street') </span><span class="cx"> ) </span><span class="cx"> </span><ins>+All keyword arguments sent to `select_by` are used to create query criterion. This means that familiar `select` keyword options like `order_by` and `limit` are not directly available. To enable these options with `select_by`, you can try the `SelectResults` extension which offers methods off the result of a `select_by` such as "order_by" as well as array slicing functions that generate queries. Or you can use `select` in conjunction with `join_to`. + +The `join_to` method of Query is a component of the `select_by` operation, and is given a keyname in order to return a "join path" from the Query's mapper to the mapper which contains the property of the given name: + + {python} + >>> q = session.query(User) + >>> j = q.join_to('street') + >>> print j + users.user_id=addresses.user_id + +Also available is the `join_via` function, which is similar to `join_to`, except instead of traversing through all properties to find a path to the given key, its given an explicit path to the target property: + + {python} + >>> q = session.query(User) + >>> j = q.join_via(['orders', 'items']) + >>> print j + users.c.user_id==orders.c.user_id AND orders.c.item_id==items.c.item_id + +Expressions produced by `join_to` and `join_via` can be used with `select` to create query criterion: + + >>> l = q.select( + (addresses_table.c.street=='some address') & + (items_table.c.item_name=='item #4') & + q.join_to('address') & + q.join_via(['orders', 'items']) + ) + + </ins><span class="cx"> #### Selecting from Relationships: Eager Load {@name=eagerload} </span><span class="cx"> </span><span class="cx"> With just a single parameter `lazy=False` specified to the relation object, the parent and child SQL queries can be joined together. </span></span></pre></div> <a id="sqlalchemybranchesschemalibsqlalchemyormpropertiespy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/lib/sqlalchemy/orm/properties.py (1451 => 1452)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/lib/sqlalchemy/orm/properties.py 2006-05-13 01:18:04 UTC (rev 1451) +++ sqlalchemy/branches/schema/lib/sqlalchemy/orm/properties.py 2006-05-13 08:00:52 UTC (rev 1452) </span><span class="lines">@@ -39,6 +39,7 @@ </span><span class="cx"> statement.append_column(c) </span><span class="cx"> def do_init(self, key, parent): </span><span class="cx"> self.key = key </span><ins>+ self.parent = parent </ins><span class="cx"> # establish a SmartProperty property manager on the object for this key </span><span class="cx"> if parent._is_primary_mapper(): </span><span class="cx"> #print "regiser col on class %s key %s" % (parent.class_.__name__, key) </span><span class="lines">@@ -293,34 +294,11 @@ </span><span class="cx"> else: </span><span class="cx"> self.foreigntable = dependent[0] </span><span class="cx"> </span><del>- - def get_criterion(self, query, key, value): - """given a key/value pair, determines if this PropertyLoader's mapper contains a key of the - given name in its property list, or if this PropertyLoader's association mapper, if any, - contains a key of the given name in its property list, and returns a WHERE clause against - the given value if found. - - this is called by a mappers select_by method to formulate a set of key/value pairs into - a WHERE criterion that spans multiple tables if needed.""" - # TODO: optimization: change mapper to accept a WHERE clause with separate bind parameters - # then cache the generated WHERE clauses here, since the creation + the copy_container - # is an extra expense - if self.mapper.props.has_key(key): - if self.secondaryjoin is not None: - c = (self.mapper.props[key].columns[0]==value) & self.primaryjoin & self.secondaryjoin - else: - c = (self.mapper.props[key].columns[0]==value) & self.primaryjoin - return c.copy_container() - elif self.mapper.mapped_table.c.has_key(key): - if self.secondaryjoin is not None: - c = (self.mapper.mapped_table.c[key].columns[0]==value) & self.primaryjoin & self.secondaryjoin - else: - c = (self.mapper.mapped_table.c[key].columns[0]==value) & self.primaryjoin - return c.copy_container() - elif self.association is not None: - c = query._get_criterion(self.mapper, key, value) & self.primaryjoin - return c.copy_container() - return None </del><ins>+ def get_join(self): + if self.secondaryjoin is not None: + return self.primaryjoin & self.secondaryjoin + else: + return self.primaryjoin </ins><span class="cx"> </span><span class="cx"> def execute(self, session, instance, row, identitykey, imap, isnew): </span><span class="cx"> if self.is_primary(): </span></span></pre></div> <a id="sqlalchemybranchesschemalibsqlalchemyormquerypy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/lib/sqlalchemy/orm/query.py (1451 => 1452)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/lib/sqlalchemy/orm/query.py 2006-05-13 01:18:04 UTC (rev 1451) +++ sqlalchemy/branches/schema/lib/sqlalchemy/orm/query.py 2006-05-13 08:00:52 UTC (rev 1452) </span><span class="lines">@@ -65,7 +65,7 @@ </span><span class="cx"> </span><span class="cx"> e.g. u = usermapper.get_by(user_name = 'fred') </span><span class="cx"> """ </span><del>- x = self.select_whereclause(self._by_clause(*args, **params), limit=1) </del><ins>+ x = self.select_whereclause(self.join_by(*args, **params), limit=1) </ins><span class="cx"> if x: </span><span class="cx"> return x[0] </span><span class="cx"> else: </span><span class="lines">@@ -75,6 +75,7 @@ </span><span class="cx"> """returns an array of object instances based on the given clauses and key/value criterion. </span><span class="cx"> </span><span class="cx"> *args is a list of zero or more ClauseElements which will be connected by AND operators. </span><ins>+ </ins><span class="cx"> **params is a set of zero or more key/value parameters which are converted into ClauseElements. </span><span class="cx"> the keys are mapped to property or column names mapped by this mapper's Table, and the values </span><span class="cx"> are coerced into a WHERE clause separated by AND operators. If the local property/column </span><span class="lines">@@ -87,8 +88,73 @@ </span><span class="cx"> ret = self.extension.select_by(self, *args, **params) </span><span class="cx"> if ret is not mapper.EXT_PASS: </span><span class="cx"> return ret </span><del>- return self.select_whereclause(self._by_clause(*args, **params)) </del><ins>+ return self.select_whereclause(self.join_by(*args, **params)) </ins><span class="cx"> </span><ins>+ def join_by(self, *args, **params): + """like select_by, but returns a ClauseElement representing the WHERE clause that would normally + be sent to select_whereclause by select_by.""" + clause = None + for arg in args: + if clause is None: + clause = arg + else: + clause &= arg + + for key, value in params.iteritems(): + (keys, prop) = self._locate_prop(key) + c = (prop.columns[0]==value) & self.join_via(keys) + if clause is None: + clause = c + else: + clause &= c + return clause + + def _locate_prop(self, key): + import properties + keys = [] + def search_for_prop(mapper): + if mapper.props.has_key(key): + return mapper.props[key] + else: + for prop in mapper.props.values(): + if not isinstance(prop, properties.PropertyLoader): + continue + x = search_for_prop(prop.mapper) + if x: + keys.insert(0, prop.key) + return x + else: + return None + p = search_for_prop(self.mapper) + if p is None: + raise exceptions.InvalidRequestError("Cant locate property named '%s'" % key) + return [keys, p] + + def join_to(self, key): + """given the key name of a property, will recursively descend through all child properties + from this Query's mapper to locate the property, and will return a ClauseElement + representing a join from this Query's mapper to the endmost mapper.""" + [keys, p] = self._locate_prop(key) + return self.join_via(keys) + + def join_via(self, keys): + """given a list of keys that represents a path from this Query's mapper to a related mapper + based on names of relations from one mapper to the next, returns a + ClauseElement representing a join from this Query's mapper to the endmost mapper. + """ + mapper = self.mapper + clause = None + for key in keys: + prop = mapper.props[key] + if clause is None: + clause = prop.get_join() + else: + clause &= prop.get_join() + mapper = prop.mapper + + return clause + + </ins><span class="cx"> def selectfirst_by(self, *args, **params): </span><span class="cx"> """works like select_by(), but only returns the first result by itself, or None if no </span><span class="cx"> objects returned. Synonymous with get_by()""" </span><span class="lines">@@ -96,7 +162,7 @@ </span><span class="cx"> </span><span class="cx"> def selectone_by(self, *args, **params): </span><span class="cx"> """works like selectfirst_by(), but throws an error if not exactly one result was returned.""" </span><del>- ret = self.select_whereclause(self._by_clause(*args, **params), limit=2) </del><ins>+ ret = self.select_whereclause(self.join_by(*args, **params), limit=2) </ins><span class="cx"> if len(ret) == 1: </span><span class="cx"> return ret[0] </span><span class="cx"> raise exceptions.InvalidRequestError('Multiple rows returned for selectone_by') </span><span class="lines">@@ -104,7 +170,7 @@ </span><span class="cx"> def count_by(self, *args, **params): </span><span class="cx"> """returns the count of instances based on the given clauses and key/value criterion. </span><span class="cx"> The criterion is constructed in the same way as the select_by() method.""" </span><del>- return self.count(self._by_clause(*args, **params)) </del><ins>+ return self.count(self.join_by(*args, **params)) </ins><span class="cx"> </span><span class="cx"> def selectfirst(self, *args, **params): </span><span class="cx"> """works like select(), but only returns the first result by itself, or None if no </span><span class="lines">@@ -182,25 +248,6 @@ </span><span class="cx"> finally: </span><span class="cx"> result.close() </span><span class="cx"> </span><del>- def _by_clause(self, *args, **params): - clause = None - for arg in args: - if clause is None: - clause = arg - else: - clause &= arg - for key, value in params.iteritems(): - if value is False: - continue - c = self._get_criterion(self.mapper, key, value) - if c is None: - raise exceptions.InvalidRequestError("Cant find criterion for property '"+ key + "'") - if clause is None: - clause = c - else: - clause &= c - return clause - </del><span class="cx"> def _get(self, key, ident=None, reload=False): </span><span class="cx"> if not reload and not self.always_refresh: </span><span class="cx"> try: </span><span class="lines">@@ -278,18 +325,3 @@ </span><span class="cx"> value.setup(key, statement, **kwargs) </span><span class="cx"> return statement </span><span class="cx"> </span><del>- def _get_criterion(self, mapper, key, value): - """used by select_by to match a key/value pair against - local properties, column names, or a matching property in this mapper's - list of relations.""" - if mapper.props.has_key(key): - return mapper.props[key].columns[0] == value - elif mapper.select_table.c.has_key(key): - return mapper.select_table.c[key] == value - else: - for prop in mapper.props.values(): - c = prop.get_criterion(self, key, value) - if c is not None: - return c - else: - return None </del></span></pre></div> <a id="sqlalchemybranchesschematestmapperpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/test/mapper.py (1451 => 1452)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/test/mapper.py 2006-05-13 01:18:04 UTC (rev 1451) +++ sqlalchemy/branches/schema/test/mapper.py 2006-05-13 08:00:52 UTC (rev 1452) </span><span class="lines">@@ -211,6 +211,25 @@ </span><span class="cx"> l = create_session().query(User).select(users.c.user_name.endswith('ed')) </span><span class="cx"> self.assert_result(l, User, *user_result[1:3]) </span><span class="cx"> </span><ins>+ def testjoinvia(self): + m = mapper(User, users, properties={ + 'orders':relation(mapper(Order, orders, properties={ + 'items':relation(mapper(Item, orderitems)) + })) + }) + + q = create_session().query(m) + + l = q.select((orderitems.c.item_name=='item 4') & q.join_via(['orders', 'items'])) + self.assert_result(l, User, user_result[0]) + + l = q.select_by(item_name='item 4') + self.assert_result(l, User, user_result[0]) + + l = q.select((orderitems.c.item_name=='item 4') & q.join_to('item_name')) + self.assert_result(l, User, user_result[0]) + + </ins><span class="cx"> def testorderby(self): </span><span class="cx"> # TODO: make a unit test out of these various combinations </span><span class="cx"> # m = mapper(User, users, order_by=desc(users.c.user_name)) </span><span class="lines">@@ -469,6 +488,7 @@ </span><span class="cx"> ("SELECT orders.order_id AS orders_order_id, orders.user_id AS orders_user_id, orders.description AS orders_description, orders.isopen AS orders_isopen FROM orders ORDER BY %s" % orderby, {}), </span><span class="cx"> ]) </span><span class="cx"> </span><ins>+ </ins><span class="cx"> def testdeepoptions(self): </span><span class="cx"> m = mapper(User, users, properties={ </span><span class="cx"> 'orders':relation(mapper(Order, orders, properties={ </span></span></pre> </div> </div> </body> </html> |
