def buildSQLExpression(self, sql_catalog, column_map, only_group_columns,
group):
"""
Render RelatedKey's ZSQLMethod by providing it table aliases from
ColumnMap.
sql_catalog (SQLCatalog)
column_map (ColumnMap)
group (string)
only_group_columns (bool)
Ignored.
"""
related_key = getattr(sql_catalog, self.related_key_id)
# related key is defined in catalog
related_table_list, destination_table = self.table_list[:
-1], self.table_list[
-1]
# method caching
getTableAlias = column_map.getTableAlias
getRelatedKeyGroup = column_map.getRelatedKeyGroup
# table aliases for related tables
table_alias_list = [
(getTableAlias(related_table,
group=getRelatedKeyGroup(index,
group)), related_table)
for (index, related_table) in enumerate(related_table_list)
]
# table alias for destination table
table_alias_list.append(
(getTableAlias(destination_table, group=group), destination_table))
# map aliases to use in ZSQLMethod.
table_alias_dict = dict(
('table_%s' % (index, ), table_alias)
for (index, (table_alias,
table_name)) in enumerate(table_alias_list))
assert len(table_alias_list) == len(table_alias_dict)
query_table = column_map.getCatalogTableAlias()
rendered_related_key = related_key(
query_table=query_table,
RELATED_QUERY_SEPARATOR=RELATED_QUERY_SEPARATOR,
src__=1,
**table_alias_dict)
join_condition_list = rendered_related_key.split(
RELATED_QUERY_SEPARATOR)
# Important:
# Previously the catalog separated join condition from the related query.
# Example:
# ComplexQuery(Query(title="foo"),
# Query(subordination_title="bar")
# , operator='OR')
# Former catalog rendering (truncated where-expression):
# AND ((catalog.title LIKE '%foo%') OR
# (related_catalog_1.title LIKE '%bar%'))
# AND (related_catalog_1.uid = related_category_0.category_uid AND
# related_category_0.base_category_uid = 873 AND
# related_category_0.uid = catalog.uid)
# As you can see, the part of the query joining the tables is *out* of the
# OR expression, and therefor applies to the entire query.
# This was done on purpose, because doing otherwise gives very poor
# performances (on a simple data set, similar query can take *minutes* to
# execute - as of MySQL 5.x).
#
# Because of this, we never return an SQLExpression here, as it
# would mix join definition with column condition in the body of
# the WHERE clause. Instead we explicitly define a Join to the
# catalog. The ColumnMap defines whether this is an Inner Join or
# a Left Outer Join. Notice that if an Inner Join is decided,
# objects lacking a relationship will never appear in the result.
if len(join_condition_list) == len(table_alias_list):
# Good! we got a compatible method that splits the join
# conditions according to the related tables.
#
# Add a join on this related key, based on the chain of
# inner-joins of the related key tables.
query_table_join_condition = join_condition_list.pop()
right_side = self.stitchJoinDefinition(table_alias_list,
join_condition_list,
column_map)
column_map.addRelatedKeyJoin(self.column,
right_side=right_side,
condition=query_table_join_condition)
else:
# Method did not render the related key condition with the
# appropriate separators so we could split it
# XXX: Can we try to parse rendered_related_key to select which
# conditions go with each table? Maybe we could still use
# explicit joins this way...
msg = RELATED_KEY_MISMATCH_MESSAGE % (
self.related_key_id, self.column, table_alias_list,
rendered_related_key)
if BACKWARD_COMPATIBILITY:
# BBB: remove this branch of the condition, and the above
# constant, when all zsql_methods have been adapted to return
# the join queries properly separated by the
# RELATED_QUERY_SEPARATOR.
# The rendered related key doesn't have the separators for each
# joined table, so we revert to doing implicit inner joins:
log.warning(msg + "\n\nAdding an Implicit Join Condition...")
column_map._addJoinQueryForColumn(
self.column, SQLQuery(rendered_related_key))
else:
raise RuntimeError(msg)
return None
def _extendJoinConditionQueryList(self, query_list):
self.left_tabledef._extendJoinConditionQueryList(query_list)
self.right_tabledef._extendJoinConditionQueryList(query_list)
query_list.append(SQLQuery(self.condition))