def get_popular_projects() -> ProjectSearchResultsDTO:
""" Get all projects ordered by task_history """
rate_func = func.count(TaskHistory.user_id) / extract(
"epoch", func.sum(cast(TaskHistory.action_date, Time)))
query = TaskHistory.query.with_entities(
TaskHistory.project_id.label("id"), rate_func.label("rate"))
# Implement filters.
query = (query.filter(
TaskHistory.action_date >= date.today() - timedelta(days=90)
).filter(
or_(
TaskHistory.action == TaskAction.LOCKED_FOR_MAPPING.name,
TaskHistory.action == TaskAction.LOCKED_FOR_VALIDATION.name,
)).filter(TaskHistory.action_text is not None).filter(
TaskHistory.action_text != ""))
# Group by and order by.
sq = (query.group_by(TaskHistory.project_id).order_by(
desc("rate")).limit(10).subquery())
projects_query = ProjectSearchService.create_search_query()
projects = projects_query.filter(Project.id == sq.c.id)
# Get total contributors.
contrib_counts = ProjectSearchService.get_total_contributions(projects)
zip_items = zip(projects, contrib_counts)
dto = ProjectSearchResultsDTO()
dto.results = [
ProjectSearchService.create_result_dto(p, "en", t)
for p, t in zip_items
]
return dto
def get_recommended_projects(user_name: str, preferred_locale: str):
""" Gets all projects a user has mapped or validated on """
from backend.services.project_search_service import ProjectSearchService
limit = 20
user = (
User.query.with_entities(User.id, User.mapping_level)
.filter(User.username == user_name)
.one_or_none()
)
if user is None:
raise NotFound()
# Get all projects that the user has contributed
sq = (
TaskHistory.query.with_entities(TaskHistory.project_id.label("project_id"))
.distinct(TaskHistory.project_id)
.filter(TaskHistory.user_id == user.id)
.subquery()
)
# Get all campaigns for all contributed projects.
campaign_tags = (
Project.query.with_entities(Project.campaign.label("tag"))
.filter(or_(Project.author_id == user.id, Project.id == sq.c.project_id))
.subquery()
)
# Get projects with given campaign tags but without user contributions.
query = ProjectSearchService.create_search_query()
projs = (
query.filter(Project.campaign.any(campaign_tags.c.tag)).limit(limit).all()
)
# Get only user mapping level projects.
len_projs = len(projs)
if len_projs < limit:
remaining_projs = (
query.filter(Project.mapper_level == user.mapping_level)
.limit(limit - len_projs)
.all()
)
projs.extend(remaining_projs)
dto = ProjectSearchResultsDTO()
# Get all total contributions for each paginated project.
contrib_counts = ProjectSearchService.get_total_contributions(projs)
zip_items = zip(projs, contrib_counts)
dto.results = [
ProjectSearchService.create_result_dto(p, "en", t) for p, t in zip_items
]
return dto
def get_featured_projects(preferred_locale):
""" Sets project as featured """
query = ProjectSearchService.create_search_query()
projects = query.filter(Project.featured == true()).group_by(Project.id).all()
# Get total contributors.
contrib_counts = ProjectSearchService.get_total_contributions(projects)
zip_items = zip(projects, contrib_counts)
dto = ProjectSearchResultsDTO()
dto.results = [
ProjectSearchService.create_result_dto(p, preferred_locale, t)
for p, t in zip_items
]
return dto