def get_event_counts(self, filter=False): where = '' if filter: where = 'where not e.hidden' cur.execute('''select e.*, count(p.id) from event e join photo p on e.id = p.taken_during %s group by e.id, e.name, e.description, e.start_date, e.end_date, e.hidden order by e.start_date''' % where) return [(worm.make(Event, row[0:6]), row[6]) for row in cur.fetchall()]
def get_category_counts(self, filter = False): where = '' if filter: where = 'where not p.hidden' cur.execute('''select c.*, count(p.id) from category c join in_category inc on inc.category = c.id join photo p on inc.photo = p.id %s group by c.id, c.name, c.hidden, c.description order by c.name''' % where) return [(worm.make(Category, row[0 : 4]), row[4]) for row in cur.fetchall()]
def get_event_counts(self, filter = False): where = '' if filter: where = 'where not e.hidden' cur.execute('''select e.*, count(p.id) from event e join photo p on e.id = p.taken_during %s group by e.id, e.name, e.description, e.start_date, e.end_date, e.hidden order by e.start_date''' % where) return [(worm.make(Event, row[0 : 6]), row[6]) for row in cur.fetchall()]
def get_category_counts(self, filter=False): where = '' if filter: where = 'where not p.hidden' cur.execute('''select c.*, count(p.id) from category c join in_category inc on inc.category = c.id join photo p on inc.photo = p.id %s group by c.id, c.name, c.hidden, c.description order by c.name''' % where) return [(worm.make(Category, row[0:4]), row[4]) for row in cur.fetchall()]
def get_person_counts(self, filter=False): where = '' if filter: where = 'where not p.hidden' cur.execute('''select p.*, count(f.id) from person p join depicted_in d on d.person = p.id join photo f on d.photo = f.id %s group by p.id, p.name, p.hidden, p.username, p.password order by p.name''' % where) return [(worm.make(Person, row[0:-1]), row[-1]) for row in cur.fetchall()]
def get_person_counts(self, filter = False): where = '' if filter: where = 'where not p.hidden' cur.execute('''select p.*, count(f.id) from person p join depicted_in d on d.person = p.id join photo f on d.photo = f.id %s group by p.id, p.name, p.hidden, p.username, p.password order by p.name''' % where) return [(worm.make(Person, row[0 : -1]), row[-1]) for row in cur.fetchall()]
def get_search_results(self, search, filter=False): if filter: filter = ' and ' + SQL_PHOTO_FILTER else: filter = '' query = ''' select t1.*, ts_rank_cd(to_tsvector(t1.title || ' ' || coalesce(t1.description, '')), query) AS rank from photo t1, to_tsquery(%%s) query where query @@ to_tsvector(t1.title || ' ' || coalesce(t1.description, '')) %s order by rank desc limit 50''' % filter cur.execute(query, (tokenize_for_search(search), )) return [worm.make(Photo, row[:1]) for row in cur.fetchall()]
def get_search_results(self, search, filter = False): if filter: filter = ' and ' + SQL_PHOTO_FILTER else: filter = '' query = ''' select t1.*, ts_rank_cd(to_tsvector(t1.title || ' ' || coalesce(t1.description, '')), query) AS rank from photo t1, to_tsquery(%%s) query where query @@ to_tsvector(t1.title || ' ' || coalesce(t1.description, '')) %s order by rank desc limit 50''' % filter cur.execute(query, (tokenize_for_search(search), )) return [worm.make(Photo, row[ : 1]) for row in cur.fetchall()]
def get_best_photos(self, offset=0, filter=False): if filter: filter = ' where ' + SQL_PHOTO_FILTER else: filter = '' cur.execute('''select t1.*, avg(score) as a from photo t1 join photo_score on t1.id = photo %s group by t1.id, t1.title, t1.description, t1.taken_at, t1.taken_during, t1.taken_by, t1.time_taken, t1.hidden order by a desc limit 50 offset %s''' % (filter, offset)) return [worm.make(Photo, row[:-1]) for row in cur.fetchall()]
def get_best_photos(self, offset = 0, filter = False): if filter: filter = ' where ' + SQL_PHOTO_FILTER else: filter = '' cur.execute('''select t1.*, avg(score) as a from photo t1 join photo_score on t1.id = photo %s group by t1.id, t1.title, t1.description, t1.taken_at, t1.taken_during, t1.taken_by, t1.time_taken, t1.hidden order by a desc limit 50 offset %s''' % (filter, offset)) return [worm.make(Photo, row[ : -1]) for row in cur.fetchall()]