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)
def facetSearchView(request, *args, **kwargs):
""" Return the faceted data corresponding to the given query
"""
facet_fields = ('material_type', 'categories', 'language')
query = {}
or_fields = getOrFields(request)
# support both 'type' & 'material_type'
if request.GET.get('type', None):
query['material_type'] = request.GET['type']
allow_model = models.Material
sqs = SearchQuerySet().models(allow_model).filter(**query)
# add custom fileting with categories
sqs = queryCategory(request, sqs, 'categories' in or_fields)
# implement facet
for field in facet_fields:
sqs = sqs.facet(field)
counts = sqs.facet_counts()
# refine the category IDs
cats = counts['fields']['categories']
cat_dict = {}
for cid in range(len(cats)):
raw_id = models.restoreAssignedCategory(cats[cid][0])
if raw_id:
raw_id = str(raw_id[0])
else:
raw_id = '0'
if cat_dict.has_key(raw_id):
cat_dict[raw_id] += cats[cid][1]
else:
cat_dict[raw_id] = cats[cid][1]
cats = []
for key in cat_dict:
cats.append([int(key), cat_dict[key]])
counts['fields']['categories'] = cats
return Response(counts)
