def get_users_who_like_same_restaurants_as_kate(database):
"""The following query returns all user ids who have reviewed restaurants that Kate rates over 3 stars."""
result = ''
start = perf_counter_ns()
if database == "JanusGraph":
result = janus_graph.execute_query(
'g.V().has("User", "user_id", "{}").as("kate").outE("REVIEWS").has("stars", gt(3)).inV().in("REVIEWS")'
'.where(neq("kate")).as("users").out("REVIEWS").out("IN_CATEGORY").has("name", "Restaurants")'
'.select("users").dedup().values("user_id").fold()'.format(kate_id)
)[0]
elif database == "PostgreSQL":
result = postgres.execute_query(
'SELECT DISTINCT OtherReviews.user_id FROM users JOIN review KateReviews '
'ON users.id = KateReviews.user_id AND users.id = \'{}\' AND KateReviews.stars > 3 JOIN business KateBus '
'ON KateReviews.business_id = KateBus.id JOIN review OtherReviews '
'ON OtherReviews.user_id != KateReviews.user_id AND OtherReviews.business_id = KateReviews.business_id '
'JOIN bus_2_cat Bus2Cat ON OtherReviews.business_id = Bus2Cat.business_id '
'JOIN category Categories ON Bus2Cat.category_id = Categories.id '
'AND Categories.name = \'Restaurants\''.format(kate_id), 'yelp'
)
result = [i[0] for i in result]
elif database == "TigerGraph":
result = tigergraph.execute_query("getSimilarUsersBasedOnRestaurants?p={}".format(kate_id))[0]['@@userIds']
time_elapsed = (perf_counter_ns() - start) / 1000000
return result, time_elapsed
def get_recent_reviews_for_user_near_kate(database, user_id):
"""The following query returns a given user's reviews of restaurants near Kate that they have rated over 3 stars"""
result = ''
start = perf_counter_ns()
if database == "JanusGraph":
result = janus_graph.execute_query(
'g.V().has("User", "user_id", "{}").outE("REVIEWS").has("stars", gt(3)).order().by("date", desc)'
'.as("stars", "text").inV().has("location", geoWithin(Geoshape.circle(35.15,-80.79, 5))).as("business_id")'
'.select("stars").limit(10).select("stars", "text", "business_id")'
'.by("stars").by("text").by("business_id")'
.format(user_id)
)
elif database == "PostgreSQL":
result = postgres.execute_query(
'SELECT review.stars, review.text, review.business_id FROM review JOIN business '
'ON review.business_id = business.id '
'AND review.user_id = \'{}\' AND ST_DWithin(location, ST_MakePoint(-80.79, 35.15)::geography, 5000) '
'AND review.stars > 3 ORDER BY review.date DESC LIMIT 10'.format(user_id), 'yelp'
)
elif database == "TigerGraph":
req = tigergraph.execute_query("getRecentGoodReviewsNearUser?p={}".format(user_id))
if req is not None:
result = req[0]['@@finalReviews']
else:
result = req
time_elapsed = (perf_counter_ns() - start) / 1000000
return result, time_elapsed
def query_db(db_name):
"""Submit query to db, return response."""
from providentia.db import janus_graph, postgres
from time import perf_counter_ns
data = request.get_json()
logging.debug('Incoming query for {}: {}'.format(db_name,
str(data['query'])))
query = data['query']
if 'delete' in query.lower() or 'update' in query.lower(
) or 'drop' in query.lower() or 'add' in query.lower():
return Response({"message": "You may only use select-style queries!"},
status=400)
result = ''
start = perf_counter_ns()
if db_name == "JanusGraph":
result = str(janus_graph.execute_query(query))
elif db_name == "PostgreSQL":
result = str(postgres.execute_query(query))
elif db_name == "Cassandra":
pass
time_elapsed = (perf_counter_ns() - start) / 1000000
logging.debug('Query response: {}'.format(result))
return Response(json.dumps({
'result': result,
'time': time_elapsed
}),
status=200,
mimetype='application/json')
def query_database(database):
result = {}
if database == 'JanusGraph':
result = janus_graph.execute_query(
'g.V().hasLabel("Transfer").as("tth")'
'.in("RESPONSE_TRANSFER").as("ttas", "osd")'
'.where(math("ttas + osd + tth")'
'.by(values("time_to_ambulance_starts"))'
'.by(values("on_scene_duration"))'
'.by(values("travel_time_hospital"))'
'.is(gt(15 * 60)))'
'.count()')
result = result[0]
elif database == "PostgreSQL":
result = postgres.execute_query(
'SELECT count(*) '
'FROM transfer TNS '
'JOIN response RES ON TNS.response_id = RES.id '
'WHERE RES.time_to_ambulance_starts '
'+ RES.on_scene_duration '
'+ TNS.travel_time_hospital > 15 * 60;', 'phosim')
result = result[0][0]
elif database == "TigerGraph":
req = tigergraph.execute_query('postSim3')
if req is not None:
result = req[0]['@@totalResponses']
return result
def get_lv_reviews_from_friends(database):
lat, lon = coords
result = []
if database == 'JanusGraph':
result = janus_graph.execute_query(
'g.V().has("User", "user_id", "%s").as("julie")'
'.out("FRIENDS").as("f1").out("FRIENDS").as("f2")'
'.union(select("f1"), select("f2")).dedup().where(neq("julie")).outE("REVIEWS").filter{'
'it.get().value("date").atZone(ZoneId.of("-07:00")).toLocalDate().getMonthValue() >= 11 &&'
'it.get().value("date").atZone(ZoneId.of("-07:00")).toLocalDate().getMonthValue() <= 12}'
'.as("text").as("stars")'
'.inV().has("location", geoWithin(Geoshape.circle(%f, %f, 30)))'
'.select("text", "stars").by("text").by("stars")' % (julie_id, lat, lon))
elif database == "PostgreSQL":
result = postgres.execute_query(
"SELECT DISTINCT R.text, R.stars FROM review R "
"JOIN business B ON R.business_Id = B.id "
"INNER JOIN friends F2 ON R.user_id = F2.friend_id "
"INNER JOIN friends F1 ON F2.user_id = F1.friend_id "
"WHERE F1.user_id = $${}$$ "
"AND F2.user_id <> $${}$$ "
"AND (R.user_id = F1.user_id OR R.user_id = F1.friend_id) "
"AND ST_DWithin(B.location, ST_MakePoint({}, {})::geography, 30000) "
"AND (date_part('month', R.date) >= 11 AND date_part('month', R.date) <= 12)"
.format(julie_id, julie_id, lon, lat), 'yelp')
elif database == "TigerGraph":
req = tigergraph.execute_query('getFriendReviewsInArea?p={}&lat={}&lon={}'.format(julie_id, lat, lon))
if req is not None:
result = req[0]['@@reviews']
return result
def get_business_name(database, business_id):
"""Returns the name of a business given its ID"""
if database == 'JanusGraph':
return janus_graph.execute_query(
'g.V().has("Business", "business_id", "{}").next().values("name")'.format(business_id))[0]
elif database == "PostgreSQL":
return postgres.execute_query('SELECT name FROM business WHERE id = \'{}\''.format(business_id), 'yelp')[0][0]
elif database == "TigerGraph":
result = tigergraph.execute_query("getBusinessName?b={}".format(business_id))
if result is not None:
return result[0]['name']
else:
return result
def get_reviews_from_phoenix_2018(database):
if database == 'JanusGraph':
return janus_graph.execute_query(
'g.V().has("Business", "location", geoWithin(Geoshape.circle(33.45,-112.56, 50))).inE("REVIEWS")'
'.has("date", between(Instant.parse("2018-01-01T00:00:00.00Z"), Instant.parse("2018-12-31T23:59:59.99Z")))'
'.valueMap()')
elif database == "PostgreSQL":
return postgres.execute_query(
'SELECT text, review.stars, cool, funny, useful FROM business JOIN review ON business.id = '
'review.business_id AND ST_DWithin(location, ST_MakePoint(-112.56, 33.45)::geography, 50000) '
'AND date_part(\'year\', date) = 2018', 'yelp')
elif database == "TigerGraph":
req = tigergraph.execute_query('getReviewsFromPhoenix2018')
if req is not None:
return req[0]['@@reviewList']
else:
return []
def query_database(database):
result = {}
if database == 'JanusGraph':
result = janus_graph.execute_query(
'g.V().hasLabel("Priority").has("priority_id", "1")'
'.in("RESPONSE_PRIORITY").hasLabel("Response")'
'.has("destination", geoWithin(Geoshape.circle(63.67, 19.11, 0.5)))'
'.fold().aggregate("avg_ttas")'
'.by(unfold().values("time_to_ambulance_starts").mean())'
'.unfold().out("RESPONSE_TRANSFER")'
'.fold().aggregate("avg_tth")'
'.by(unfold().values("travel_time_hospital").mean())'
'.select("avg_ttas", "avg_tth")')
result = {
'avg_ttas': float(result[0]['avg_ttas'][0]),
'avg_tth': float(result[0]['avg_tth'][0]),
}
elif database == "PostgreSQL":
result = postgres.execute_query(
'SELECT avg(RES.time_to_ambulance_starts) as avg_ttas, '
'avg(TNS.travel_time_hospital) as avg_tth '
'FROM priority PRI '
'JOIN response RES ON RES.id = PRI.response_id '
'JOIN transfer TNS ON RES.id = TNS.response_id '
'WHERE PRI.id = 1 '
'AND ST_DWithin(RES.destination, ST_MakePoint(19.11, 63.67)::geography, 500);',
'phosim')
result = {
'avg_ttas': float(result[0][0]),
'avg_tth': float(result[0][1]),
}
elif database == "TigerGraph":
req = tigergraph.execute_query('postSim1')
if req is not None:
result = {
'avg_ttas': req[0]['@@avgTtas'],
'avg_tth': req[1]['@@avgTth'],
}
return result
def query_database(database):
result = []
if database == 'JanusGraph':
result = janus_graph.execute_query(
'g.V().hasLabel("Resource").has("resource_id", "2")'
'.in("RESPONSE_RESOURCE").hasLabel("Response")'
'.has("destination", geoWithin(Geoshape.circle(63.67, 19.11, 0.5)))'
'.as("RES")'
'.out("RESPONSE_SCENE").as("SCN")'
'.select("RES")'
'.groupCount().by(out("RESPONSE_PRIORITY").values("priority_id"))')
result = result[0]
elif database == "PostgreSQL":
result = postgres.execute_query(
'SELECT count(*) as responses_by_prio '
'FROM resource RSC '
'JOIN response RES ON RSC.response_id = RES.id '
'JOIN on_scene SCN ON SCN.response_id = RES.id '
'JOIN priority PRI ON PRI.response_id = RES.id '
'WHERE RSC.id = 2 '
'AND ST_DWithin(RES.destination, ST_MakePoint(19.11, 63.67)::geography, 500) '
'GROUP BY PRI.id;', 'phosim')
result = {
1: int(result[0][0]),
2: int(result[1][0]),
3: int(result[2][0])
}
elif database == "TigerGraph":
req = tigergraph.execute_query('postSim2')
if req is not None:
result = req[0]['@@group']
result = {
int(result[0]['prio']): int(result[0]['total']),
int(result[1]['prio']): int(result[1]['total']),
int(result[2]['prio']): int(result[2]['total'])
}
return result