def _table_without_any_of_mutations(self):
"""
Returns a Table containing the item_id from the table genomes that do not match the given mutations.
:param select_columns selects only the column names in this collection. If None, selects all the columns from genomes.
"""
mutations = self.region_attrs.without_variants
if len(mutations) == 0:
raise ValueError('function argument *mutations cannot be empty')
else:
# create table for the result
t_name = utils.random_t_name_w_prefix('without_any_of_mut')
query_mutations = self._stmt_where_region_is_any_of_mutations(
*mutations,
from_table=regions,
select_expression=select([regions.c.item_id]),
only_item_id_in_table=self.my_meta_t)
stmt_as = except_(select([self.my_meta_t.c.item_id]),
query_mutations)
stmt_create_table = utils.stmt_create_table_as(
t_name, stmt_as, default_schema_to_use_name)
if self.log_sql_commands:
utils.show_stmt(
self.connection, stmt_create_table, self.logger.debug,
'CREATE TABLE WITHOUT ANY OF THE {} MUTATIONS'.format(
len(mutations)))
self.connection.execute(stmt_create_table)
return Table(t_name,
db_meta,
autoload=True,
autoload_with=self.connection,
schema=default_schema_to_use_name)
def as_clause_base(self,
db,
discussion_id,
include_breakpoints=False,
include_deleted=False):
"""Express collection as a SQLAlchemy query clause.
:param bool include_breakpoints: Include posts where
a threadbreak happens
:param include_deleted: Include posts in deleted_publication_states.
True means only deleted posts, None means all posts,
False means only live posts or deleted posts with live descendants.
"""
assert self.reduced
def base_query(labeled=False):
post = with_polymorphic(Post, [],
Post.__table__,
aliased=False,
flat=True)
content = with_polymorphic(Content, [],
Content.__table__,
aliased=False,
flat=True)
if labeled:
query = db.query(post.id.label("post_id"))
else:
query = db.query(post.id)
query = query.join(content, (content.id == post.id) &
(content.discussion_id == discussion_id))
if include_deleted is not None:
if include_deleted:
query = query.filter(
post.publication_state.in_(deleted_publication_states))
else:
query = query.filter(content.tombstone_date == None)
return post, query
if not self.paths:
post, q = base_query(True)
return q.filter(False).subquery("relposts")
includes_by_level = [[]]
excludes_by_level = [[]]
ancestry = []
for path in self.paths:
while ancestry:
if not path.post_path.startswith(ancestry[-1].post_path):
ancestry.pop()
else:
break
level = len(ancestry) // 2
if path.positive:
while len(includes_by_level) <= level:
includes_by_level.append([])
includes_by_level[level].append(path)
else:
while len(excludes_by_level) <= level:
excludes_by_level.append([])
excludes_by_level[level].append(path)
ancestry.append(path)
max_level = max(len(includes_by_level), len(excludes_by_level))
q = None
for level in range(max_level):
condition = None
# with use_labels, name of final column determined by first query
post, q2 = base_query(level == 0)
includes = (includes_by_level[level]
if level < len(includes_by_level) else [])
excludes = (excludes_by_level[level]
if level < len(excludes_by_level) else [])
include_ids = [path.last_id for path in includes]
exclude_ids = [path.last_id for path in excludes]
if include_breakpoints:
include_ids.extend(exclude_ids)
exclude_ids = None
if len(includes):
ancestry_regex = '^(%s)' % ('|'.join(path.post_path
for path in includes))
condition = or_(post.id.in_(include_ids),
post.ancestry.op('~', 0, True)(ancestry_regex))
if level == 0:
q = q2.filter(condition)
else:
assert condition is not None
q2 = q2.filter(condition)
# works in postgres, more efficient
q = union(q, q2, use_labels=True)
# rather than
# q = q.union(q2)
condition = None
post, q2 = base_query()
if len(excludes):
ancestry_regex = '^(%s)' % ('|'.join(path.post_path
for path in excludes))
condition = post.ancestry.op('~', 0, True)(ancestry_regex)
if exclude_ids:
condition = post.id.in_(exclude_ids) | condition
q = except_(q, q2.filter(condition), use_labels=True)
# q = q.except_(q2.filter(condition))
condition = None
if getattr(q, "c", None) is None:
# base query
c = q._entities[0]
q = q.with_entities(c.expr.label("post_id"))
q = q.subquery("relposts")
else:
# compound query, already has columns
q = q.alias("relposts")
return q
def as_clause_base(self, db, include_breakpoints=False):
assert self.reduced
def base_query(labeled=False):
post = with_polymorphic(
Post, [], Post.__table__,
aliased=False, flat=True)
if labeled:
return post, db.query(post.id.label("post_id"))
else:
return post, db.query(post.id)
if not self.paths:
post, q = base_query(True)
return q.filter(False).subquery("relposts")
includes_by_level = [[]]
excludes_by_level = [[]]
ancestry = []
for path in self.paths:
while ancestry:
if not path.post_path.startswith(ancestry[-1].post_path):
ancestry.pop()
else:
break
level = len(ancestry) // 2
if path.positive:
while len(includes_by_level) <= level:
includes_by_level.append([])
includes_by_level[level].append(path)
else:
while len(excludes_by_level) <= level:
excludes_by_level.append([])
excludes_by_level[level].append(path)
ancestry.append(path)
max_level = max(len(includes_by_level), len(excludes_by_level))
q = None
for level in range(max_level):
condition = None
# with use_labels, name of final column determined by first query
post, q2 = base_query(level == 0)
includes = (includes_by_level[level]
if level < len(includes_by_level) else [])
excludes = (excludes_by_level[level]
if level < len(excludes_by_level) else [])
include_ids = [path.last_id for path in includes]
exclude_ids = [path.last_id for path in excludes]
if include_breakpoints:
include_ids.extend(exclude_ids)
exclude_ids = None
if len(includes):
condition = or_(
post.id.in_(include_ids),
*[post.ancestry.like(path.post_path + "%")
for path in includes])
if level == 0:
q = q2.filter(condition)
else:
assert condition is not None
q2 = q2.filter(condition)
# works in postgres, more efficient
q = union(q, q2, use_labels=True)
# rather than
# q = q.union(q2)
condition = None
post, q2 = base_query()
if len(excludes):
condition = or_(
*[post.ancestry.like(path.post_path + "%")
for path in excludes])
if exclude_ids:
condition = post.id.in_(exclude_ids) | condition
q = except_(q, q2.filter(condition), use_labels=True)
# q = q.except_(q2.filter(condition))
condition = None
if getattr(q, "c", None) is None:
# base query
c = q._entities[0]
q = q.with_entities(c.expr.label("post_id"))
q = q.subquery("relposts")
else:
# compound query, already has columns
q = q.alias("relposts")
return q
def as_clause_base(self,
db,
include_breakpoints=False,
include_deleted=False,
include_moderating=None,
user_id=None):
"""Express collection as a SQLAlchemy query clause.
:param bool include_breakpoints: Include posts where
a threadbreak happens
:param include_deleted: Include posts in deleted_publication_states.
True means only deleted posts, None means all posts,
False means only live posts or deleted posts with live descendants.
:param include_moderating: Include posts in SUBMITTED_AWAITING_MODERATION.
True means include all those posts, Falsish means none of those posts,
a "mine" value means only those belonging to this user.
There is not currently a way to only get those posts. (todo?)
NOTE: that parameter is interpreted differently in Idea.get_related_posts_query
"""
assert self.reduced
def base_query(labeled=False):
post = with_polymorphic(Post, [],
Post.__table__,
aliased=False,
flat=True)
content = with_polymorphic(Content, [],
Content.__table__,
aliased=False,
flat=True)
if labeled:
query = db.query(post.id.label("post_id"))
else:
query = db.query(post.id)
query = query.join(content, content.id == post.id)
states = set(countable_publication_states) # Or just published?
states.update(deleted_publication_states)
if include_deleted is not None:
if include_deleted is True:
states = set(deleted_publication_states)
else:
query = query.filter(
content.tombstone_date == None) # noqa: E711
if include_moderating is True:
states.add(PublicationStates.SUBMITTED_AWAITING_MODERATION)
state_condition = post.publication_state.in_(states)
if user_id:
if include_moderating == "mine":
state_condition = state_condition | (
post.publication_state.in_([
PublicationStates.SUBMITTED_AWAITING_MODERATION,
PublicationStates.DRAFT
]) & (post.creator_id == user_id))
else:
state_condition = state_condition | (
(post.publication_state == PublicationStates.DRAFT) &
(post.creator_id == user_id))
query = query.filter(state_condition)
return post, query
if not self.paths:
post, q = base_query(True)
return q.filter(False).subquery("relposts")
includes_by_level = [[]]
excludes_by_level = [[]]
ancestry = []
for path in self.paths:
while ancestry:
if not path.post_path.startswith(ancestry[-1].post_path):
ancestry.pop()
else:
break
level = len(ancestry) // 2
if path.positive:
while len(includes_by_level) <= level:
includes_by_level.append([])
includes_by_level[level].append(path)
else:
while len(excludes_by_level) <= level:
excludes_by_level.append([])
excludes_by_level[level].append(path)
ancestry.append(path)
max_level = max(len(includes_by_level), len(excludes_by_level))
q = None
for level in range(max_level):
condition = None
# with use_labels, name of final column determined by first query
post, q2 = base_query(level == 0)
includes = (includes_by_level[level]
if level < len(includes_by_level) else [])
excludes = (excludes_by_level[level]
if level < len(excludes_by_level) else [])
include_ids = [path.last_id for path in includes]
exclude_ids = [path.last_id for path in excludes]
if include_breakpoints:
include_ids.extend(exclude_ids)
exclude_ids = None
if len(includes):
ancestry_regex = '^(%s)' % ('|'.join(path.post_path
for path in includes))
condition = or_(post.id.in_(include_ids),
post.ancestry.op('~', 0, True)(ancestry_regex))
if level == 0:
q = q2.filter(condition)
else:
assert condition is not None
q2 = q2.filter(condition)
# works in postgres, more efficient
q = union(q, q2, use_labels=True)
# rather than
# q = q.union(q2)
condition = None
post, q2 = base_query()
if len(excludes):
ancestry_regex = '^(%s)' % ('|'.join(path.post_path
for path in excludes))
condition = post.ancestry.op('~', 0, True)(ancestry_regex)
if exclude_ids:
condition = post.id.in_(exclude_ids) | condition
q = except_(q, q2.filter(condition), use_labels=True)
# q = q.except_(q2.filter(condition))
condition = None
if getattr(q, "c", None) is None:
# base query
c = q._entities[0]
q = q.with_entities(c.expr.label("post_id"))
q = q.subquery("relposts")
else:
# compound query, already has columns
q = q.alias("relposts")
return q
def as_clause_base(self, db, include_breakpoints=False,
include_deleted=False, include_moderating=None,
user_id=None):
"""Express collection as a SQLAlchemy query clause.
:param bool include_breakpoints: Include posts where
a threadbreak happens
:param include_deleted: Include posts in deleted_publication_states.
True means only deleted posts, None means all posts,
False means only live posts or deleted posts with live descendants.
:param include_moderating: Include posts in SUBMITTED_AWAITING_MODERATION.
True means include all those posts, Falsish means none of those posts,
a "mine" value means only those belonging to this user.
There is not currently a way to only get those posts. (todo?)
NOTE: that parameter is interpreted differently in Idea.get_related_posts_query
"""
assert self.reduced
def base_query(labeled=False):
post = with_polymorphic(
Post, [], Post.__table__,
aliased=False, flat=True)
content = with_polymorphic(
Content, [], Content.__table__,
aliased=False, flat=True)
if labeled:
query = db.query(post.id.label("post_id"))
else:
query = db.query(post.id)
query = query.join(content, content.id == post.id)
states = set(countable_publication_states) # Or just published?
states.update(deleted_publication_states)
if include_deleted is not None:
if include_deleted is True:
states = set(deleted_publication_states)
else:
query = query.filter(content.tombstone_date == None) # noqa: E711
if include_moderating is True:
states.add(PublicationStates.SUBMITTED_AWAITING_MODERATION)
state_condition = post.publication_state.in_(states)
if user_id:
if include_moderating == "mine":
state_condition = state_condition | (
post.publication_state.in_([
PublicationStates.SUBMITTED_AWAITING_MODERATION,
PublicationStates.DRAFT]) &
(post.creator_id == user_id))
else:
state_condition = state_condition | (
(post.publication_state == PublicationStates.DRAFT) &
(post.creator_id == user_id))
query = query.filter(state_condition)
return post, query
if not self.paths:
post, q = base_query(True)
return q.filter(False).subquery("relposts")
includes_by_level = [[]]
excludes_by_level = [[]]
ancestry = []
for path in self.paths:
while ancestry:
if not path.post_path.startswith(ancestry[-1].post_path):
ancestry.pop()
else:
break
level = len(ancestry) // 2
if path.positive:
while len(includes_by_level) <= level:
includes_by_level.append([])
includes_by_level[level].append(path)
else:
while len(excludes_by_level) <= level:
excludes_by_level.append([])
excludes_by_level[level].append(path)
ancestry.append(path)
max_level = max(len(includes_by_level), len(excludes_by_level))
q = None
for level in range(max_level):
condition = None
# with use_labels, name of final column determined by first query
post, q2 = base_query(level == 0)
includes = (includes_by_level[level]
if level < len(includes_by_level) else [])
excludes = (excludes_by_level[level]
if level < len(excludes_by_level) else [])
include_ids = [path.last_id for path in includes]
exclude_ids = [path.last_id for path in excludes]
if include_breakpoints:
include_ids.extend(exclude_ids)
exclude_ids = None
if len(includes):
ancestry_regex = '^(%s)' % ('|'.join(
path.post_path for path in includes))
condition = or_(
post.id.in_(include_ids),
post.ancestry.op('~', 0, True)(ancestry_regex))
if level == 0:
q = q2.filter(condition)
else:
assert condition is not None
q2 = q2.filter(condition)
# works in postgres, more efficient
q = union(q, q2, use_labels=True)
# rather than
# q = q.union(q2)
condition = None
post, q2 = base_query()
if len(excludes):
ancestry_regex = '^(%s)' % ('|'.join(
path.post_path for path in excludes))
condition = post.ancestry.op('~', 0, True)(ancestry_regex)
if exclude_ids:
condition = post.id.in_(exclude_ids) | condition
q = except_(q, q2.filter(condition), use_labels=True)
# q = q.except_(q2.filter(condition))
condition = None
if getattr(q, "c", None) is None:
# base query
c = q._entities[0]
q = q.with_entities(c.expr.label("post_id"))
q = q.subquery("relposts")
else:
# compound query, already has columns
q = q.alias("relposts")
return q