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