def single_tag(id):
tag = get_object_or_404(Tag.select(), Tag.id == id)
items = (Item
.select()
.join(ItemTag)
.join(Tag)
.where(Tag.id == id)
.order_by(Item.created_on.desc()))
return object_list('items.html', items,
banner="Items with tag '%s'" % (tag.name,),
paginate_by=app.config['ITEMS_PER_PAGE'])
def untagged_items():
# This does:
# SELECT *
# FROM items i
# LEFT JOIN itemtags t ON (i.id = t.id)
# WHERE t.id IS NULL
# Effectively, we perform a left outer join, which results in NULLS for
# fields that don't match, and then select just those items (i.e. all
# the items with no matching ItemTag).
untagged = Item.select().join(ItemTag, JOIN_LEFT_OUTER).where(ItemTag.id >> None)
return object_list('items.html', untagged, banner='Untagged Items',
paginate_by=app.config['ITEMS_PER_PAGE'])
def search():
# Parse form inputs
allOf = request.args.get('allOf') or ''
anyOf = request.args.get('anyOf') or ''
noneOf = request.args.get('noneOf') or ''
allOfL = split_tags(allOf)
anyOfL = split_tags(anyOf)
noneOfL = split_tags(noneOf)
# Make sure that we have some valid query.
if len(allOfL) + len(anyOfL) + len(noneOfL) == 0:
return render_template('search.html')
# SELECT *
# FROM Item
# JOIN ItemTag
# ON Item.hash = ItemTag.item_id
# JOIN Tag
# ON ItemTag.tag_id = Tag.id
# GROUP BY Item.hash
# HAVING SUM(CASE WHEN Tag.name IN (required) THEN 1 ELSE 0 END) = N
# AND SUM(CASE WHEN Tag.name IN (any of ) THEN 1 ELSE 0 END) >= 0
# AND SUM(CASE WHEN Tag.name IN (none of ) THEN 1 ELSE 0 END) = 0
clauses = []
if len(allOfL) > 0:
clauses.append(fn.Sum(Tag.name << allOfL) == len(allOfL))
if len(anyOfL) > 0:
clauses.append(fn.Sum(Tag.name << anyOfL) >= 0)
if len(noneOfL) > 0:
clauses.append(fn.Sum(Tag.name << noneOfL) == 0)
query = (Item
.select()
.join(ItemTag)
.join(Tag)
.group_by(Item.hash)
.having(
*clauses
))
return object_list('search.html', query,
allOf=allOf, anyOf=anyOf, noneOf=noneOf,
paginate_by=app.config['ITEMS_PER_PAGE'])
def single_group(id):
group = get_object_or_404(Group.select(), Group.id == id)
items = Item.select().join(Group).where(Group.id == id)
return object_list('items.html', items,
banner="Items in group '%s'" % (group.name,),
paginate_by=app.config['ITEMS_PER_PAGE'])
def all_items():
items = Item.select().order_by(Item.created_on.desc())
return object_list('items.html', items, banner='All Items',
paginate_by=app.config['ITEMS_PER_PAGE'])
