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 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