def get(review_id, limit=1, offset=0):
"""Get revisions on a review ordered by the timestamp
Args:
review_id (uuid): ID of review
limit (int): The number of revisions to return(default=1)
offset (int): The number of revisions to skip from top(default=0)
Returns:
List of dictionaries of revisions of the review
with the following structure:
{
"id": (int),
"review_id": (uuid),
"timestamp": (datetime),
"text": (string),
"rating": (int),
"votes_positive": (int),
"votes_negative": (int),
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT id,
review_id,
timestamp,
text,
rating,
SUM(
CASE WHEN vote='t' THEN 1 ELSE 0 END
) AS votes_positive,
SUM(
CASE WHEN vote='f' THEN 1 ELSE 0 END
) AS votes_negative
FROM revision
LEFT JOIN vote
ON vote.revision_id = revision.id
WHERE review_id = :review_id
GROUP BY revision.id
ORDER BY timestamp DESC
OFFSET :offset
LIMIT :limit
"""), {
"review_id": review_id,
"offset": offset,
"limit": limit
})
rows = result.fetchall()
if not rows:
raise db_exceptions.NoDataFoundException(
"Cannot find specified review.")
rows = [dict(row) for row in rows]
# Convert ratings to values on a scale 1-5
for row in rows:
row["rating"] = RATING_SCALE_1_5.get(row["rating"])
return rows
def get(review_id, limit=1, offset=0):
"""Get revisions on a review ordered by the timestamp
Args:
review_id (uuid): ID of review
limit (int): The number of revisions to return(default=1)
offset (int): The number of revisions to skip from top(default=0)
Returns:
List of dictionaries of revisions of the review
with the following structure:
{
"id": (int),
"review_id": (uuid),
"timestamp": (datetime),
"text": (string),
"rating": (int),
"votes_positive": (int),
"votes_negative": (int),
}
"""
with db.engine.connect() as connection:
result = connection.execute(sqlalchemy.text("""
SELECT id,
review_id,
timestamp,
text,
rating,
SUM(
CASE WHEN vote='t' THEN 1 ELSE 0 END
) AS votes_positive,
SUM(
CASE WHEN vote='f' THEN 1 ELSE 0 END
) AS votes_negative
FROM revision
LEFT JOIN vote
ON vote.revision_id = revision.id
WHERE review_id = :review_id
GROUP BY revision.id
ORDER BY timestamp DESC
OFFSET :offset
LIMIT :limit
"""), {
"review_id": review_id,
"offset": offset,
"limit": limit
})
rows = result.fetchall()
if not rows:
raise db_exceptions.NoDataFoundException("Cannot find specified review.")
rows = [dict(row) for row in rows]
# Convert ratings to values on a scale 1-5
for row in rows:
row["rating"] = RATING_SCALE_1_5.get(row["rating"])
return rows
def get_popular(limit=None):
"""Get a list of popular reviews.
Popularity is determined by 'popularity' of a particular review. popularity is a
difference between positive votes and negative. In this case only votes
from the last month are used to calculate popularity to make results more
varied.
Args:
limit (int): Maximum number of reviews to return.
Returns:
Randomized list of popular reviews which are converted into
dictionaries using to_dict method.
"""
cache_key = cache.gen_key("popular_reviews", limit)
reviews = cache.get(cache_key, REVIEW_CACHE_NAMESPACE)
defined_limit = 4 * limit if limit else None
if not reviews:
with db.engine.connect() as connection:
results = connection.execute(
sqlalchemy.text("""
SELECT review.id,
review.entity_id,
review.entity_type,
review.user_id,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
SUM(
CASE WHEN vote = 't' THEN 1
WHEN vote = 'f' THEN -1
WHEN vote IS NULL THEN 0 END
) AS popularity,
latest_revision.id AS latest_revision_id,
latest_revision.timestamp AS latest_revision_timestamp,
latest_revision.text AS text,
latest_revision.rating AS rating
FROM review
JOIN revision ON revision.review_id = review.id
LEFT JOIN (
SELECT revision_id, vote
FROM vote
WHERE rated_at > :last_month
) AS votes_last_month
ON votes_last_month.revision_id = revision.id
JOIN (
revision JOIN (
SELECT review.id AS review_uuid,
MAX(timestamp) AS latest_timestamp
FROM review
JOIN revision ON review.id = review_id
GROUP BY review.id
) AS latest ON latest.review_uuid = revision.review_id
AND latest.latest_timestamp = revision.timestamp
) AS latest_revision
ON review.id = latest_revision.review_id
WHERE entity_id
IN (
SELECT DISTINCT entity_id
FROM (
SELECT entity_id
FROM review
ORDER BY RANDOM()
) AS randomized_entity_ids
)
AND latest_revision.text IS NOT NULL
GROUP BY review.id, latest_revision.id
ORDER BY popularity
LIMIT :limit
"""), {
"limit": defined_limit,
"last_month": datetime.now() - timedelta(weeks=4)
})
reviews = results.fetchall()
reviews = [dict(review) for review in reviews]
if reviews:
for review in reviews:
review["rating"] = RATING_SCALE_1_5.get(review["rating"])
review["last_revision"] = {
"id": review.pop("latest_revision_id"),
"timestamp": review.pop("latest_revision_timestamp"),
"text": review["text"],
"rating": review["rating"],
"review_id": review["id"],
}
reviews = [
to_dict(review, confidential=True) for review in reviews
]
cache.set(cache_key, reviews, 1 * 60 * 60,
REVIEW_CACHE_NAMESPACE) # 1 hour
shuffle(reviews)
return reviews[:limit]
def get_by_id(review_id):
"""Get a review by its ID.
Args:
review_id (uuid): ID of the review.
Returns:
Dictionary with the following structure
{
"id": uuid,
"entity_id": uuid,
"entity_type": str,
"user_id": uuid,
"user": dict,
"edits": int,
"is_draft": bool,
"is_hidden": bool,
"language": str,
"license_id": str,
"source": str,
"source_url": str,
"last_revision: dict,
"votes": dict,
"popularity": int,
"rating": int,
"text": str,
"created": datetime,
"license": dict,
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT review.id AS id,
review.entity_id,
review.entity_type,
review.user_id,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
revision.id AS last_revision_id,
revision.timestamp,
revision.text,
revision.rating,
"user".email,
"user".created as user_created,
"user".display_name,
"user".show_gravatar,
"user".musicbrainz_id,
"user".is_blocked,
created_time.created,
license.full_name,
license.info_url
FROM review
JOIN revision ON revision.review_id = review.id
JOIN "user" ON "user".id = review.user_id
JOIN license ON license.id = license_id
JOIN (
SELECT review.id,
timestamp AS created
FROM review
JOIN revision ON review.id = revision.review_id
WHERE review.id = :review_id
ORDER BY revision.timestamp ASC
LIMIT 1
) AS created_time
ON created_time.id = review.id
ORDER BY timestamp DESC
"""), {
"review_id": review_id,
})
review = result.fetchone()
if not review:
raise db_exceptions.NoDataFoundException(
"Can't find review with ID: {id}".format(id=review_id))
review = dict(review)
review["rating"] = RATING_SCALE_1_5.get(review["rating"])
review["last_revision"] = {
"id": review.pop("last_revision_id"),
"timestamp": review.pop("timestamp"),
"text": review.get("text"),
"rating": review.get("rating"),
"review_id": review.get("id"),
}
review["user"] = User({
"id":
review["user_id"],
"display_name":
review.pop("display_name", None),
"is_blocked":
review.pop("is_blocked", False),
"show_gravatar":
review.pop("show_gravatar", False),
"musicbrainz_username":
review.pop("musicbrainz_id"),
"email":
review.pop("email"),
"created":
review.pop("user_created"),
})
review["license"] = {
"id": review["license_id"],
"info_url": review["info_url"],
"full_name": review["full_name"],
}
votes = db_revision.votes(review["last_revision"]["id"])
review["votes"] = {
"positive": votes["positive"],
"negative": votes["negative"],
}
review["popularity"] = review["votes"]["positive"] - review["votes"][
"negative"]
return review
def list_reviews(*,
inc_drafts=False,
inc_hidden=False,
entity_id=None,
entity_type=None,
license_id=None,
user_id=None,
language=None,
exclude=None,
sort=None,
limit=20,
offset=None):
"""Get a list of reviews.
This function provides several filters that can be used to select a subset of reviews.
Args:
entity_id (uuid): ID of the entity that has been reviewed.
entity_type (str): Type of the entity that has been reviewed.
user_id (uuid): ID of the author.
sort (str): Order of the returned reviews. Can be either "popularity" (order by difference in +/- votes),
or "created" (order by creation time), or "random" (order randomly).
limit (int): Maximum number of reviews to return.
offset (int): Offset that can be used in conjunction with the limit.
language (str): Language code of reviews.
license_id (str): License ID that reviews are associated with.
inc_drafts (bool): True if reviews marked as drafts should be included, False if not.
inc_hidden (bool): True if reviews marked as hidden should be included, False if not.
exclude (list): List of reviews (their IDs) to exclude from results.
Returns:
Tuple with two values:
1. list of reviews as dictionaries,
2. total number of reviews that match the specified filters.
"""
filters = []
filter_data = {}
if not inc_drafts:
filters.append("is_draft = :is_draft")
filter_data["is_draft"] = False
if not inc_hidden:
filters.append("is_hidden = :is_hidden")
filter_data["is_hidden"] = False
# FILTERING
if entity_id is not None:
filters.append("entity_id = :entity_id")
filter_data["entity_id"] = entity_id
if entity_type is not None:
filters.append("entity_type = :entity_type")
filter_data["entity_type"] = entity_type
if license_id is not None:
filters.append("license_id = :license_id")
filter_data["license_id"] = license_id
if user_id is not None:
filters.append("review.user_id = :user_id")
filter_data["user_id"] = user_id
if exclude is not None:
filters.append("review.id NOT IN :exclude")
filter_data["exclude"] = tuple(exclude)
if language is not None:
filters.append("language = :language")
filter_data["language"] = language
filterstr = " AND ".join(filters)
if filterstr:
filterstr = " WHERE " + filterstr
query = sqlalchemy.text("""
SELECT COUNT(*)
FROM review
{filterstr}
""".format(filterstr=filterstr))
with db.engine.connect() as connection:
result = connection.execute(query, filter_data)
count = result.fetchone()[0]
order_by_clause = str()
if sort == "popularity":
order_by_clause = """
ORDER BY popularity DESC
"""
elif sort == "created":
order_by_clause = """
ORDER BY created DESC
"""
elif sort == "random":
order_by_clause = """
ORDER BY RANDOM()
"""
# Note that all revisions' votes are considered in this popularity
query = sqlalchemy.text("""
SELECT review.id,
review.entity_id,
review.entity_type,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
review.user_id,
"user".display_name,
"user".show_gravatar,
"user".is_blocked,
"user".email,
"user".created as user_created,
"user".musicbrainz_id,
MIN(revision.timestamp) as created,
SUM(
CASE WHEN vote='t' THEN 1 ELSE 0 END
) AS votes_positive_count,
SUM(
CASE WHEN vote='f' THEN 1 ELSE 0 END
) AS votes_negative_count,
SUM(
CASE WHEN vote = 't' THEN 1
WHEN vote = 'f' THEN -1 WHEN vote IS NULL THEN 0 END
) AS popularity,
latest_revision.id as latest_revision_id,
latest_revision.timestamp as latest_revision_timestamp,
latest_revision.text as text,
latest_revision.rating as rating,
license.full_name,
license.info_url
FROM review
JOIN revision ON review.id = revision.review_id
LEFT JOIN vote ON vote.revision_id = revision.id
JOIN "user" ON review.user_id = "user".id
JOIN license ON license.id = license_id
JOIN (
revision
JOIN (
SELECT review.id AS review_uuid,
MAX(timestamp) AS latest_timestamp
FROM review
JOIN revision ON review.id = review_id
GROUP BY review.id
) AS latest
ON latest.review_uuid = revision.review_id
AND latest.latest_timestamp = revision.timestamp
) AS latest_revision ON review.id = latest_revision.review_id
{where_clause}
GROUP BY review.id, latest_revision.id, "user".id, license.id
{order_by_clause}
LIMIT :limit
OFFSET :offset
""".format(where_clause=filterstr, order_by_clause=order_by_clause))
filter_data["limit"] = limit
filter_data["offset"] = offset
with db.engine.connect() as connection:
results = connection.execute(query, filter_data)
rows = results.fetchall()
rows = [dict(row) for row in rows]
# Organise last revision info in reviews
if rows:
for row in rows:
row["rating"] = RATING_SCALE_1_5.get(row["rating"])
row["last_revision"] = {
"id": row.pop("latest_revision_id"),
"timestamp": row.pop("latest_revision_timestamp"),
"text": row["text"],
"rating": row["rating"],
"review_id": row["id"],
}
row["user"] = User({
"id":
row["user_id"],
"display_name":
row.pop("display_name"),
"show_gravatar":
row.pop("show_gravatar"),
"is_blocked":
row.pop("is_blocked"),
"musicbrainz_username":
row.pop("musicbrainz_id"),
"email":
row.pop("email"),
"created":
row.pop("user_created"),
})
return rows, count
def get_reviews_list(connection,
*,
inc_drafts=False,
inc_hidden=False,
entity_id=None,
entity_type=None,
license_id=None,
user_id=None,
language=None,
exclude=None,
sort=None,
limit=20,
offset=None):
"""
helper function for list_reviews() that extends support for execution within a transaction by directly receiving the
connection object
"""
filters = []
filter_data = {}
if not inc_drafts:
filters.append("is_draft = :is_draft")
filter_data["is_draft"] = False
if not inc_hidden:
filters.append("is_hidden = :is_hidden")
filter_data["is_hidden"] = False
# FILTERING
if entity_id is not None:
filters.append("entity_id = :entity_id")
filter_data["entity_id"] = entity_id
if entity_type is not None:
filters.append("entity_type = :entity_type")
filter_data["entity_type"] = entity_type
if license_id is not None:
filters.append("license_id = :license_id")
filter_data["license_id"] = license_id
if user_id is not None:
filters.append("review.user_id = :user_id")
filter_data["user_id"] = user_id
if exclude is not None:
filters.append("review.id NOT IN :exclude")
filter_data["exclude"] = tuple(exclude)
if language is not None:
filters.append("language = :language")
filter_data["language"] = language
filterstr = " AND ".join(filters)
if filterstr:
filterstr = " WHERE " + filterstr
query = sqlalchemy.text("""
SELECT COUNT(*)
FROM review
{filterstr}
""".format(filterstr=filterstr))
result = connection.execute(query, filter_data)
count = result.fetchone()[0]
order_by_clause = str()
if sort == "popularity":
order_by_clause = """
ORDER BY popularity DESC
"""
elif sort == "published_on":
order_by_clause = """
ORDER BY review.published_on DESC
"""
elif sort == "random":
order_by_clause = """
ORDER BY RANDOM()
"""
# Note that all revisions' votes are considered in this popularity
query = sqlalchemy.text("""
SELECT review.id,
review.entity_id,
review.entity_type,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
review.user_id,
"user".display_name,
"user".show_gravatar,
"user".is_blocked,
"user".email,
"user".created as user_created,
"user".musicbrainz_id,
review.published_on,
MIN(revision.timestamp) as created,
SUM(
CASE WHEN vote='t' THEN 1 ELSE 0 END
) AS votes_positive_count,
SUM(
CASE WHEN vote='f' THEN 1 ELSE 0 END
) AS votes_negative_count,
SUM(
CASE WHEN vote = 't' THEN 1
WHEN vote = 'f' THEN -1 WHEN vote IS NULL THEN 0 END
) AS popularity,
latest_revision.id as latest_revision_id,
latest_revision.timestamp as latest_revision_timestamp,
latest_revision.text as text,
latest_revision.rating as rating,
license.full_name,
license.info_url
FROM review
JOIN revision ON review.id = revision.review_id
LEFT JOIN vote ON vote.revision_id = revision.id
JOIN "user" ON review.user_id = "user".id
JOIN license ON license.id = license_id
JOIN (
revision
JOIN (
SELECT review.id AS review_uuid,
MAX(timestamp) AS latest_timestamp
FROM review
JOIN revision ON review.id = review_id
GROUP BY review.id
) AS latest
ON latest.review_uuid = revision.review_id
AND latest.latest_timestamp = revision.timestamp
) AS latest_revision ON review.id = latest_revision.review_id
{where_clause}
GROUP BY review.id, latest_revision.id, "user".id, license.id
{order_by_clause}
LIMIT :limit
OFFSET :offset
""".format(where_clause=filterstr, order_by_clause=order_by_clause))
filter_data["limit"] = limit
filter_data["offset"] = offset
results = connection.execute(query, filter_data)
rows = results.fetchall()
rows = [dict(row) for row in rows]
# Organise last revision info in reviews
if rows:
for row in rows:
row["rating"] = RATING_SCALE_1_5.get(row["rating"])
row["last_revision"] = {
"id": row.pop("latest_revision_id"),
"timestamp": row.pop("latest_revision_timestamp"),
"text": row["text"],
"rating": row["rating"],
"review_id": row["id"],
}
row["user"] = User({
"id":
row["user_id"],
"display_name":
row.pop("display_name"),
"show_gravatar":
row.pop("show_gravatar"),
"is_blocked":
row.pop("is_blocked"),
"musicbrainz_username":
row.pop("musicbrainz_id"),
"email":
row.pop("email"),
"created":
row.pop("user_created"),
})
return rows, count
def get_popular(limit=None):
"""Get a list of popular reviews.
Popularity is determined by 'popularity' of a particular review. popularity is a
difference between positive votes and negative. In this case only votes
from the last month are used to calculate popularity to make results more
varied.
Args:
limit (int): Maximum number of reviews to return.
Returns:
Randomized list of popular reviews which are converted into
dictionaries using to_dict method.
"""
cache_key = cache.gen_key("popular_reviews", limit)
reviews = cache.get(cache_key, REVIEW_CACHE_NAMESPACE)
defined_limit = 4 * limit if limit else None
if not reviews:
with db.engine.connect() as connection:
results = connection.execute(sqlalchemy.text("""
SELECT review.id,
review.entity_id,
review.entity_type,
review.user_id,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
SUM(
CASE WHEN vote = 't' THEN 1
WHEN vote = 'f' THEN -1
WHEN vote IS NULL THEN 0 END
) AS popularity,
latest_revision.id AS latest_revision_id,
latest_revision.timestamp AS latest_revision_timestamp,
latest_revision.text AS text,
latest_revision.rating AS rating
FROM review
JOIN revision ON revision.review_id = review.id
LEFT JOIN (
SELECT revision_id, vote
FROM vote
WHERE rated_at > :last_month
) AS votes_last_month
ON votes_last_month.revision_id = revision.id
JOIN (
revision JOIN (
SELECT review.id AS review_uuid,
MAX(timestamp) AS latest_timestamp
FROM review
JOIN revision ON review.id = review_id
GROUP BY review.id
) AS latest ON latest.review_uuid = revision.review_id
AND latest.latest_timestamp = revision.timestamp
) AS latest_revision
ON review.id = latest_revision.review_id
WHERE entity_id
IN (
SELECT DISTINCT entity_id
FROM (
SELECT entity_id
FROM review
ORDER BY RANDOM()
) AS randomized_entity_ids
)
AND latest_revision.text IS NOT NULL
AND review.is_hidden = 'f'
AND review.is_draft = 'f'
GROUP BY review.id, latest_revision.id
ORDER BY popularity
LIMIT :limit
"""), {
"limit": defined_limit,
"last_month": datetime.now() - timedelta(weeks=4)
})
reviews = results.fetchall()
reviews = [dict(review) for review in reviews]
if reviews:
for review in reviews:
review["rating"] = RATING_SCALE_1_5.get(review["rating"])
review["last_revision"] = {
"id": review.pop("latest_revision_id"),
"timestamp": review.pop("latest_revision_timestamp"),
"text": review["text"],
"rating": review["rating"],
"review_id": review["id"],
}
reviews = [to_dict(review, confidential=True) for review in reviews]
cache.set(cache_key, reviews, 1 * 60 * 60, REVIEW_CACHE_NAMESPACE) # 1 hour
shuffle(reviews)
return reviews[:limit]
def get_by_id(review_id):
"""Get a review by its ID.
Args:
review_id (uuid): ID of the review.
Returns:
Dictionary with the following structure
{
"id": uuid,
"entity_id": uuid,
"entity_type": str,
"user_id": uuid,
"user": dict,
"edits": int,
"is_draft": bool,
"is_hidden": bool,
"language": str,
"license_id": str,
"source": str,
"source_url": str,
"last_revision: dict,
"votes": dict,
"popularity": int,
"rating": int,
"text": str,
"license": dict,
"published_on": datetime,
}
"""
with db.engine.connect() as connection:
result = connection.execute(sqlalchemy.text("""
SELECT review.id AS id,
review.entity_id,
review.entity_type,
review.user_id,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
review.published_on,
revision.id AS last_revision_id,
revision.timestamp,
revision.text,
revision.rating,
"user".email,
"user".created as user_created,
"user".display_name,
"user".show_gravatar,
"user".musicbrainz_id,
"user".is_blocked,
license.full_name,
license.info_url
FROM review
JOIN revision ON revision.review_id = review.id
JOIN "user" ON "user".id = review.user_id
JOIN license ON license.id = license_id
WHERE review.id = :review_id
ORDER BY timestamp DESC
"""), {
"review_id": review_id,
})
review = result.fetchone()
if not review:
raise db_exceptions.NoDataFoundException("Can't find review with ID: {id}".format(id=review_id))
review = dict(review)
review["rating"] = RATING_SCALE_1_5.get(review["rating"])
review["last_revision"] = {
"id": review.pop("last_revision_id"),
"timestamp": review.pop("timestamp"),
"text": review.get("text"),
"rating": review.get("rating"),
"review_id": review.get("id"),
}
review["user"] = User({
"id": review["user_id"],
"display_name": review.pop("display_name", None),
"is_blocked": review.pop("is_blocked", False),
"show_gravatar": review.pop("show_gravatar", False),
"musicbrainz_username": review.pop("musicbrainz_id"),
"email": review.pop("email"),
"created": review.pop("user_created"),
})
review["license"] = {
"id": review["license_id"],
"info_url": review["info_url"],
"full_name": review["full_name"],
}
votes = db_revision.votes(review["last_revision"]["id"])
review["votes"] = {
"positive": votes["positive"],
"negative": votes["negative"],
}
review["popularity"] = review["votes"]["positive"] - review["votes"]["negative"]
return review
def get_reviews_list(connection, *, inc_drafts=False, inc_hidden=False, entity_id=None,
entity_type=None, license_id=None, user_id=None, language=None,
exclude=None, sort=None, limit=20, offset=None):
"""
helper function for list_reviews() that extends support for execution within a transaction by directly receiving the
connection object
"""
filters = []
filter_data = {}
if not inc_drafts:
filters.append("is_draft = :is_draft")
filter_data["is_draft"] = False
if not inc_hidden:
filters.append("is_hidden = :is_hidden")
filter_data["is_hidden"] = False
# FILTERING
if entity_id is not None:
filters.append("entity_id = :entity_id")
filter_data["entity_id"] = entity_id
if entity_type is not None:
filters.append("entity_type = :entity_type")
filter_data["entity_type"] = entity_type
if license_id is not None:
filters.append("license_id = :license_id")
filter_data["license_id"] = license_id
if user_id is not None:
filters.append("review.user_id = :user_id")
filter_data["user_id"] = user_id
if exclude is not None:
filters.append("review.id NOT IN :exclude")
filter_data["exclude"] = tuple(exclude)
if language is not None:
filters.append("language = :language")
filter_data["language"] = language
filterstr = " AND ".join(filters)
if filterstr:
filterstr = " WHERE " + filterstr
query = sqlalchemy.text("""
SELECT COUNT(*)
FROM review
{filterstr}
""".format(filterstr=filterstr))
result = connection.execute(query, filter_data)
count = result.fetchone()[0]
order_by_clause = str()
if sort == "popularity":
order_by_clause = """
ORDER BY popularity DESC
"""
elif sort == "published_on":
order_by_clause = """
ORDER BY review.published_on DESC
"""
elif sort == "random":
order_by_clause = """
ORDER BY RANDOM()
"""
# Note that all revisions' votes are considered in this popularity
query = sqlalchemy.text("""
SELECT review.id,
review.entity_id,
review.entity_type,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
review.user_id,
"user".display_name,
"user".show_gravatar,
"user".is_blocked,
"user".email,
"user".created as user_created,
"user".musicbrainz_id,
review.published_on,
MIN(revision.timestamp) as created,
SUM(
CASE WHEN vote='t' THEN 1 ELSE 0 END
) AS votes_positive_count,
SUM(
CASE WHEN vote='f' THEN 1 ELSE 0 END
) AS votes_negative_count,
SUM(
CASE WHEN vote = 't' THEN 1
WHEN vote = 'f' THEN -1 WHEN vote IS NULL THEN 0 END
) AS popularity,
latest_revision.id as latest_revision_id,
latest_revision.timestamp as latest_revision_timestamp,
latest_revision.text as text,
latest_revision.rating as rating,
license.full_name,
license.info_url
FROM review
JOIN revision ON review.id = revision.review_id
LEFT JOIN vote ON vote.revision_id = revision.id
JOIN "user" ON review.user_id = "user".id
JOIN license ON license.id = license_id
JOIN (
revision
JOIN (
SELECT review.id AS review_uuid,
MAX(timestamp) AS latest_timestamp
FROM review
JOIN revision ON review.id = review_id
GROUP BY review.id
) AS latest
ON latest.review_uuid = revision.review_id
AND latest.latest_timestamp = revision.timestamp
) AS latest_revision ON review.id = latest_revision.review_id
{where_clause}
GROUP BY review.id, latest_revision.id, "user".id, license.id
{order_by_clause}
LIMIT :limit
OFFSET :offset
""".format(where_clause=filterstr, order_by_clause=order_by_clause))
filter_data["limit"] = limit
filter_data["offset"] = offset
results = connection.execute(query, filter_data)
rows = results.fetchall()
rows = [dict(row) for row in rows]
# Organise last revision info in reviews
if rows:
for row in rows:
row["rating"] = RATING_SCALE_1_5.get(row["rating"])
row["last_revision"] = {
"id": row.pop("latest_revision_id"),
"timestamp": row.pop("latest_revision_timestamp"),
"text": row["text"],
"rating": row["rating"],
"review_id": row["id"],
}
row["user"] = User({
"id": row["user_id"],
"display_name": row.pop("display_name"),
"show_gravatar": row.pop("show_gravatar"),
"is_blocked": row.pop("is_blocked"),
"musicbrainz_username": row.pop("musicbrainz_id"),
"email": row.pop("email"),
"created": row.pop("user_created"),
})
return rows, count