コード例 #1
0
ファイル: post_dao.py プロジェクト: billliu1992/localsocial
def get_post_by_id(current_user_id, post_id):
	cursor = handled_execute(db_conn, """
		SELECT
			postId, authorId, postBody, postDate, privacy, cityName, longitude, latitude,
			eventId, eventName, eventLocation, eventStart, eventEnd,
			imageId,
			firstName, lastName, nickName, portrait, showLastName,
			(SELECT COUNT(likes.likerId) FROM likes WHERE likes.postId = posts.postId GROUP BY likes.postId) AS likeCount,
			(authorId IN (SELECT userId FROM likes WHERE likes.postId = posts.postId)) AS liked,
			(authorId IN (SELECT firstUserId FROM userFriends WHERE secondUserId = %(current_user_id)s)) AS requestPending,
			(%(current_user_id)s IN (SELECT firstUserId FROM userFriends WHERE secondUserId = authorId)) AS requestSent,
			(authorId IN (SELECT firstUserId FROM userFollows WHERE secondUserId = %(current_user_id)s)) AS follower,
			(%(current_user_id)s IN (SELECT firstUserId FROM userFollows WHERE secondUserId = authorId)) AS following
		FROM posts LEFT JOIN users ON posts.authorId = users.userId
		WHERE postId = %(searched_post_id)s
			AND (privacy != 'friends' OR authorId=%(current_user_id)s
				OR (authorId IN (SELECT firstUserId FROM userFriends WHERE secondUserId = %(current_user_id)s)
					AND %(current_user_id)s IN (SELECT firstUserId FROM userFriends WHERE secondUserId = authorId)))
		ORDER BY postId DESC
		LIMIT 1;""", {
			"searched_post_id" : post_id, "current_user_id" : current_user_id
		})

	row = cursor.fetchone()

	(post_id, author_id, post_body, post_date, privacy, city_name,
		longitude, latitude, event_id, event_name, event_location, event_start,
		event_end, image_id, first_name, last_name, nick_name, portrait, show_last_name,
		likes, liked, request_pending, request_sent, follower, following) = row

	if likes == None:
		likes = 0
	are_friends = request_sent and request_pending

	author_name = build_name(first_name, nick_name, last_name, are_friends, show_last_name)
	post_location = build_location(longitude, latitude, city_name, are_friends, privacy != POST_PRIVACY.HIDE_LOCATION)

	if(event_id != None):
		new_post = EventPost(author_id, author_name, portrait, post_body, post_date, 
			privacy, post_location, event_id,
			event_name, event_location, event_start, event_end)

	elif(image_id != None):
		new_post = ImagePost(author_id, author_name, portrait, post_body, post_date, 
			privacy, post_location, image_id)
	else:
		new_post = Post(author_id, author_name, portrait, post_body, post_date, 
			privacy, post_location, likes, liked)

	new_post.post_id = post_id

	replies = reply_dao.get_replies_by_post_id(post_id, current_user_id)
	new_post.replies = replies

	return new_post
コード例 #2
0
ファイル: post_dao.py プロジェクト: billliu1992/localsocial
def get_posts_by_user(current_user_id, searched_user, friends, limit, offset, max_id):
	cursor = handled_execute(db_conn, """
		SELECT
			postId, authorId, postBody, postDate, privacy, cityName, longitude, latitude,
			eventId, eventName, eventLocation, eventStart, eventEnd,
			imageId,
			(SELECT COUNT(likes.likerId) FROM likes WHERE likes.postId = posts.postId GROUP BY likes.postId) AS likeCount,
			(authorId IN (SELECT likerId FROM likes WHERE likes.postId = posts.postId)) AS liked
		FROM posts
		WHERE authorId=%(searched_user_id)s
			AND (privacy != 'friends' OR %(friends)s = True)
			AND (%(max_id)s IS NULL OR postId <= %(max_id)s)
		ORDER BY postId DESC
		LIMIT %(limit)s OFFSET %(offset)s;
		""", { "searched_user_id" : searched_user.user_id, "friends" : friends, "max_id" : max_id, "limit" : limit, "offset" : offset})

	post_rows = cursor.fetchall()

	post_objects = []
	post_ids = []
	for row in post_rows:
		(post_id, author_id, post_body, post_date, privacy, city_name,
			longitude, latitude, event_id, event_name, event_location, event_start,
			event_end, image_id, likes, liked) = row

		if likes == None:
			likes = 0

		author_name = build_name(searched_user.first_name, searched_user.nick_name, searched_user.last_name, friends, searched_user.preferences.show_last_name)
		post_location = build_location(longitude, latitude, city_name, friends, privacy != POST_PRIVACY.HIDE_LOCATION)

		if(event_id != None):
			new_post = EventPost(author_id, author_name, searched_user.portrait, post_body, post_date, 
				privacy, post_location, event_id,
				event_name, event_location, event_start, event_end)

		elif(image_id != None):
			new_post = ImagePost(author_id, author_name, searched_user.portrait, post_body, post_date, 
				privacy, post_location, image_id)
		else:
			new_post = Post(author_id, author_name, searched_user.portrait, post_body, post_date, 
				privacy, post_location, likes, liked)

		new_post.post_id = post_id

		post_ids.append(post_id)
		post_objects.append(new_post)

	build_replies(post_ids, post_objects, current_user_id)

	return post_objects
コード例 #3
0
ファイル: post_dao.py プロジェクト: billliu1992/localsocial
def get_post_feed(current_user_id, current_location, range, limit, skip, max_id=None):
	print limit
	print skip

	current_long = current_location.longitude
	current_lat = current_location.latitude

	cursor = handled_execute(db_conn, """
		SELECT
			postId, authorId, postBody, postDate, privacy, cityName, longitude, latitude,
			eventId, eventName, eventLocation, eventStart, eventEnd,
			imageId,
			firstName, lastName, nickName, portrait, showLastName,
			(SELECT COUNT(likes.likerId) FROM likes WHERE likes.postId = posts.postId GROUP BY likes.postId) AS likeCount,
			(authorId IN (SELECT likerId FROM likes WHERE likes.postId = posts.postId)) AS liked,
			(authorId IN (SELECT firstUserId FROM userFriends WHERE secondUserId = %(current_user_id)s)) AS requestPending,
			(%(current_user_id)s IN (SELECT firstUserId FROM userFriends WHERE secondUserId = authorId)) AS requestSent,
			(authorId IN (SELECT firstUserId FROM userFollows WHERE secondUserId = %(current_user_id)s)) AS follower,
			(%(current_user_id)s IN (SELECT firstUserId FROM userFollows WHERE secondUserId = authorId)) AS following
		FROM posts LEFT JOIN users ON posts.authorId = users.userId
		WHERE sqrt((longitude - %(current_long)s) ^ 2 + (latitude - %(current_lat)s) ^ 2) < %(range)s
			AND (privacy != 'friends' OR authorId=%(current_user_id)s
				OR (authorId IN (SELECT firstUserId FROM userFriends WHERE secondUserId = %(current_user_id)s)
					AND %(current_user_id)s IN (SELECT firstUserId FROM userFriends WHERE secondUserId = authorId)))
			AND (%(max_id)s IS NULL OR postId <= %(max_id)s)
		ORDER BY postId DESC
		LIMIT %(limit)s OFFSET %(skip)s;""", {
			"current_long" : current_long, "current_lat" : current_lat, "range" : range, "current_user_id" : current_user_id,
			"max_id" : max_id, "limit" : limit, "skip" : skip
		})

	post_rows = cursor.fetchall()

	post_objects = []
	post_ids = []
	for row in post_rows:
		(post_id, author_id, post_body, post_date, privacy, city_name,
			longitude, latitude, event_id, event_name, event_location, event_start,
			event_end, image_id, first_name, last_name, nick_name, portrait, show_last_name,
			likes, liked, request_pending, request_sent, follower, following) = row

		if likes == None:
			likes = 0
		are_friends = request_pending and request_sent

		author_name = build_name(first_name, nick_name, last_name, are_friends, show_last_name)
		post_location = build_location(longitude, latitude, city_name, are_friends, privacy != POST_PRIVACY.HIDE_LOCATION)

		if(event_id != None):
			new_post = EventPost(author_id, author_name, portrait, post_body, post_date, 
				privacy, post_location, event_id,
				event_name, event_location, event_start, event_end)

		elif(image_id != None):
			new_post = ImagePost(author_id, author_name, portrait, post_body, post_date, 
				privacy, post_location, image_id)
		else:
			new_post = Post(author_id, author_name, portrait, post_body, post_date, 
				privacy, post_location, likes, liked)

		new_post.post_id = post_id
		new_post.replies = build_replies(post_ids, post_objects, current_user_id)

		post_ids.append(post_id)
		post_objects.append(new_post)

	build_replies(post_ids, post_objects, current_user_id)

	return post_objects