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'])