def get_top_rated(request, *args, **kwargs): """ Returns QuerySet of all material sorted by view count """ per_page = settings.REST_FRAMEWORK['PAGINATE_BY'] sql = """ SELECT m.id, m.material_id, m.title, m.version, m.categories, m.material_type, m.modified, avg(mr.rate) as avg_rate, count(mr.rate) AS rate_count FROM vpr_content_material AS m INNER JOIN vpr_content_materialrating AS mr ON m.id = mr.material_id GROUP BY mr.material_id ORDER BY avg_rate DESC, rate_count DESC LIMIT %d; """ % per_page cur = connection.cursor() res = cur.execute(sql) materials = [] for m in cur: # getting categories cids = models.restoreAssignedCategory(m[4]) categories = [] for ci in range(len(cids)): categories.append((cids[ci], models.getCategoryName(cids[ci]))) # getting authors pids = models.getMaterialPersons(m[0])['author'].split(',') pnames = models.getPersonName(pids) if not isinstance(pnames, list): pnames = [pnames,] authors = [] for pi in range(len(pids)): authors.append((pids[pi], pnames[pi])) s_material = { 'material_id': m[1], 'version': m[3], 'material_type': m[5], 'title': m[2], 'categories': categories, 'rating': m[7], 'rating_count': m[8], 'author': authors, 'modified': m[6], } materials.append(s_material) return Response(materials)
def queryMaterialByViewCount(request, *args, **kwargs): """ Returns QuerySet of all material sorted by view count """ #per_page = settings.REST_FRAMEWORK['PAGINATE_BY'] per_page = 50 sql = """SELECT m.id, m.material_id, m.version, m.material_type, m.title, m.categories, m.description, m.modified, mv.count FROM vpr_content_material as m INNER JOIN vpr_content_materialviewcount as mv ON (m.id=mv.material_id) ORDER BY mv.count DESC LIMIT %d; """ % (per_page) materials = [] for m in models.Material.objects.raw(sql): # getting categories cids = models.restoreAssignedCategory(m.categories) categories = [] for ci in range(len(cids)): categories.append((cids[ci], models.getCategoryName(cids[ci]))) # getting authors pids = models.getMaterialPersons(m.id)['author'].split(',') pnames = models.getPersonName(pids) if not isinstance(pnames, list): pnames = [pnames,] authors = [] for pi in range(len(pids)): authors.append((pids[pi], pnames[pi])) s_material = { 'material_id': m.material_id, 'version': m.version, 'material_type': m.material_type, 'title': m.title, 'categories': categories, 'count': m.count, 'author': authors, 'modified': m.modified, } materials.append(s_material) return Response(materials)
def get_person_favs(request, *args, **kwargs): """ Returns list of materials which are selected as favorite of person """ pid = kwargs.get('pk', None) # get paging infomation per_page = settings.REST_FRAMEWORK['PAGINATE_BY'] page = int(request.GET.get('page', 1)) start_on = (page-1)*per_page # get the favs qset = models.MaterialFavorite.objects.filter(person_id=pid).order_by('id') fav_count = qset.count() qset = qset[start_on:start_on+per_page].values_list('material_id') res = [item[0] for item in qset] # now extract material info in list mats = models.Material.objects.filter(id__in=res).values(*MAT_BASIC_FIELDS) for material in mats: material['categories'] = models.restoreAssignedCategory( material['categories']) # get related persons mroles = models.getMaterialPersons(material['id']) material['author'] = mroles.get('author', None) del material['id'] # build the result page page_total = math.ceil(fav_count*1.0/per_page) page_prev, page_next = buildPageURLs(request, page_total) # alter serialization data page_result = { 'next': page_next, 'previous': page_prev, 'results': mats, 'count': fav_count, } return Response(page_result)