def find_submissions(joined_tables=None, where_clauses=[], tag_string=None, page_num=1, page_size=None): """Does all the grunt work for finding specific submissions, including tag filtering (plus the user's defaults) and pagination and such. Returns a two-element tuple. The first is an array of Submission objects with some useful stuff eager-loaded. The second is the total number of submissions found. """ # Some defaults.. if not joined_tables: joined_tables = model.Submission.__table__ \ .join(model.UserSubmission.__table__) # XXX admins can see more than this where_clauses.append(model.UserSubmission.deletion_id == None) ### Tag filtering # Construct a list of required and excluded tags # XXX user default tags required_tags = [] excluded_tags = [] invalid_tags = [] (required_tag_names, excluded_tag_names) \ = tagging.break_apart_tag_string(tag_string, include_negative=True) for tag_list, tag_name_list in (required_tags, required_tag_names), \ (excluded_tags, excluded_tag_names): for tag_name in tag_name_list: tag = model.Tag.get_by_text(tag_name) if tag: tag_list.append(tag) else: invalid_tags.append(tag_name) # Error on invalid tags if invalid_tags: raise NoSuchTagsException(*invalid_tags) # Require tags via simple INNER JOINs for tag in required_tags: alias = model.SubmissionTag.__table__.alias() joined_tables = joined_tables.join( alias, and_( model.Submission.id == alias.c.submission_id, alias.c.tag_id == tag.id, )) # Exclude tags via LEFT JOIN .. WHERE IS NULL excluded_aliases = [] for tag in excluded_tags: alias = model.SubmissionTag.__table__.alias() joined_tables = joined_tables.outerjoin( alias, and_( model.Submission.id == alias.c.submission_id, alias.c.tag_id == tag.id, )) where_clauses.append(alias.c.tag_id == None) # Run query, fetching submission ids so we can get objects from orm q = sql.select([model.Submission.id], and_(*where_clauses), from_obj=joined_tables) \ .order_by(model.Submission.time.desc()) \ .limit(page_size).offset((page_num - 1) * page_size) if pylons.config['sqlalchemy.url'][0:5] == 'mysql': q = q.prefix_with('SQL_CALC_FOUND_ROWS') submission_ids = [row.id for row in model.Session.execute(q)] # Fetch the total number of matching submissions if pylons.config['sqlalchemy.url'][0:5] == 'mysql': submission_ct = model.Session.execute( sqlalchemy.sql.text('SELECT FOUND_ROWS()')) \ .fetchone()[0] else: submission_ct = model.Session.execute( sql.select( [sql.func.count(model.Submission.id)], and_(*where_clauses), from_obj=joined_tables, ) ) \ .fetchone()[0] # Actually fetch submissions submissions = model.Session.query(model.Submission) \ .filter(model.Submission.id.in_(submission_ids)) \ .order_by(model.Submission.time.desc()) \ .options( eagerload('primary_artist'), ) \ .all() return submissions, submission_ct
class GalleryController(BaseController): def _generic_gallery(self, joined_tables=None, where_clauses=[]): """Generic backend for viewing a gallery. Handles default tag filtering, as well as a set of default controls like further filtering, sorting, and pagination. Pass a pre-joined `joined_tables` sqla.sql object to filter further before this method does its mucking around.""" # Some defaults if not joined_tables: joined_tables = model.Submission.__table__ \ .join(model.UserSubmission.__table__) # Form validation validator = model.form.TagFilterForm() try: form_data = validator.to_python(request.params) except formencode.Invalid, error: c.form = FormGenerator(form_error=error) return render('gallery/index.mako') c.form = FormGenerator() ### SQL # Some defaults.. # XXX admins can see more than this where_clauses.append(model.UserSubmission.deletion_id == None) ### Tag filtering # Construct a list of required and excluded tags required_tags = [] excluded_tags = [] invalid_tags = [] (required_tag_names, excluded_tag_names) \ = tagging.break_apart_tag_string(form_data['tags'], include_negative=True) for tag_list, tag_name_list in (required_tags, required_tag_names), \ (excluded_tags, excluded_tag_names): for tag_name in tag_name_list: tag = model.Tag.get_by_text(tag_name) if tag: tag_list.append(tag) else: invalid_tags.append(tag_name) # Error on invalid tags if invalid_tags: c.form.errors['tags'] = 'No such tags: ' + ', '.join(invalid_tags) return render('gallery/index.mako') # Require tags via simple INNER JOINs for tag in required_tags: alias = model.SubmissionTag.__table__.alias() joined_tables = joined_tables.join( alias, and_( model.Submission.id == alias.c.submission_id, alias.c.tag_id == tag.id, )) # Exclude tags via LEFT JOIN .. WHERE IS NULL excluded_aliases = [] for tag in excluded_tags: alias = model.SubmissionTag.__table__.alias() joined_tables = joined_tables.outerjoin( alias, and_( model.Submission.id == alias.c.submission_id, alias.c.tag_id == tag.id, )) where_clauses.append(alias.c.tag_id == None) # Pagination pageno = form_data['page'] or 1 perpage = form_data['perpage'] or \ pylons.config.get('gallery.default_perpage', 12) c.form.defaults['perpage'] = perpage try: (c.submissions, submission_ct) = find_submissions( joined_tables=joined_tables, where_clauses=where_clauses, tag_string=form_data['tags'], page_num=pageno, page_size=perpage, ) except NoSuchTagsException, e: c.form.errors['tags'] = 'No such tags: ' + ', '.join(e.tags) return render('gallery/index.mako')
def find_submissions(joined_tables=None, where_clauses=[], tag_string=None, page_num=1, page_size=None): """Does all the grunt work for finding specific submissions, including tag filtering (plus the user's defaults) and pagination and such. Returns a two-element tuple. The first is an array of Submission objects with some useful stuff eager-loaded. The second is the total number of submissions found. """ # Some defaults.. if not joined_tables: joined_tables = model.Submission.__table__ \ .join(model.UserSubmission.__table__) # XXX admins can see more than this where_clauses.append(model.UserSubmission.deletion_id == None) ### Tag filtering # Construct a list of required and excluded tags # XXX user default tags required_tags = [] excluded_tags = [] invalid_tags = [] (required_tag_names, excluded_tag_names) \ = tagging.break_apart_tag_string(tag_string, include_negative=True) for tag_list, tag_name_list in (required_tags, required_tag_names), \ (excluded_tags, excluded_tag_names): for tag_name in tag_name_list: tag = model.Tag.get_by_text(tag_name) if tag: tag_list.append(tag) else: invalid_tags.append(tag_name) # Error on invalid tags if invalid_tags: raise NoSuchTagsException(*invalid_tags) # Require tags via simple INNER JOINs for tag in required_tags: alias = model.SubmissionTag.__table__.alias() joined_tables = joined_tables.join(alias, and_( model.Submission.id == alias.c.submission_id, alias.c.tag_id == tag.id, ) ) # Exclude tags via LEFT JOIN .. WHERE IS NULL excluded_aliases = [] for tag in excluded_tags: alias = model.SubmissionTag.__table__.alias() joined_tables = joined_tables.outerjoin(alias, and_( model.Submission.id == alias.c.submission_id, alias.c.tag_id == tag.id, ) ) where_clauses.append(alias.c.tag_id == None) # Run query, fetching submission ids so we can get objects from orm q = sql.select([model.Submission.id], and_(*where_clauses), from_obj=joined_tables) \ .order_by(model.Submission.time.desc()) \ .limit(page_size).offset((page_num - 1) * page_size) if pylons.config['sqlalchemy.url'][0:5] == 'mysql': q = q.prefix_with('SQL_CALC_FOUND_ROWS') submission_ids = [row.id for row in model.Session.execute(q)] # Fetch the total number of matching submissions if pylons.config['sqlalchemy.url'][0:5] == 'mysql': submission_ct = model.Session.execute( sqlalchemy.sql.text('SELECT FOUND_ROWS()')) \ .fetchone()[0] else: submission_ct = model.Session.execute( sql.select( [sql.func.count(model.Submission.id)], and_(*where_clauses), from_obj=joined_tables, ) ) \ .fetchone()[0] # Actually fetch submissions submissions = model.Session.query(model.Submission) \ .filter(model.Submission.id.in_(submission_ids)) \ .order_by(model.Submission.time.desc()) \ .options( eagerload('primary_artist'), ) \ .all() return submissions, submission_ct
self._process_form_data_files(c.submission, form_data) # Clear out derived submissions. We have to regenerate them. for i in storage_derived.items_by_prefix("%d/" % c.submission.id): del (storage_derived[i]) # Tag shuffle #tag_list = tagging.TagList() #tag_list.parse_tag_string(form_data['tags']) #submission.tags = tag_list.get_positive_tag_object_array() #for x in submission.tags: # x.cache_me() old = list(set([str(x) for x in c.submission.tags])) new = list(set(tagging.break_apart_tag_string(form_data['tags']))) to_append = [] for x in c.submission.tags: if str(x) not in new: c.submission.tags.remove(x) for x in new: if x not in old: c.submission.tags.append(model.Tag.get_by_text(x, create=True)) ''' form_data['tags'] = tagging.get_tags_from_string(form_data['tags']) for tag_object in submission.tags: if not (tag_object.text in form_data['tags']): submission.tags.remove(tag_object) #model.Session.delete(submission_tag_object) else:
# Clear out derived submissions. We have to regenerate them. for i in storage_derived.items_by_prefix("%d/"%c.submission.id): del(storage_derived[i]) # Tag shuffle #tag_list = tagging.TagList() #tag_list.parse_tag_string(form_data['tags']) #submission.tags = tag_list.get_positive_tag_object_array() #for x in submission.tags: # x.cache_me() old = list(set([str(x) for x in c.submission.tags])) new = list(set(tagging.break_apart_tag_string(form_data['tags']))) to_append = [] for x in c.submission.tags: if str(x) not in new: c.submission.tags.remove(x) for x in new: if x not in old: c.submission.tags.append(model.Tag.get_by_text(x, create=True)) ''' form_data['tags'] = tagging.get_tags_from_string(form_data['tags']) for tag_object in submission.tags: if not (tag_object.text in form_data['tags']): submission.tags.remove(tag_object)