3 Replies - 203 Views - Last Post: 18 May 2019 - 11:29 AM

#1 martymcfly   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 55
  • Joined: 09-July 16

I want to get one more data from a related table.

Posted 18 May 2019 - 09:27 AM

Hi i am getting following users' posts and the current user's posts which has a id greater than a specified id.
I also want to know the current user liked the post or not.

Existing query:
SELECT userPosts.*, users.full_name, users.photo, users.username 
    FROM userPosts INNER JOIN users ON users.uid = userPosts.uid  WHERE (userPosts.uid = %s OR
    userPosts.uid IN (SELECT flwdid FROM followers WHERE flwrid = %s)) AND upid > %s 
    ORDER BY time ASC
:code:

Table structures:
users(uid,.......)
userPosts(upid, uid, .....)
followers(flwrid, flwdid)
userPostLikes(uid, upid)

Thanks in advance.

This post has been edited by modi123_1: 18 May 2019 - 09:29 AM
Reason for edit:: In the future please use the [code] tag button in the editor


Is This A Good Question/Topic? 0
  • +

Replies To: I want to get one more data from a related table.

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15359
  • View blog
  • Posts: 61,583
  • Joined: 12-June 08

Re: I want to get one more data from a related table.

Posted 18 May 2019 - 09:29 AM

Okay, but what is your question?

If there are errors please copy/paste the full messages here. If it is not working as expected please explain what it is happening vs what should be happening.
Was This Post Helpful? 0
  • +
  • -

#3 martymcfly   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 55
  • Joined: 09-July 16

Re: I want to get one more data from a related table.

Posted 18 May 2019 - 09:35 AM

How can i know the current user liked the post? I need an extra column like "isLiked". How can i do that? This query doesn't provide that.

This is the fuction where i've used the above query:

  def getNewFollowingPosts(self, uid, upid):
    connection = self.getConnection() 
    cursor = connection.cursor(dictionary=True)
    query = """SELECT userPosts.*, users.full_name, users.photo, users.username 
    FROM userPosts INNER JOIN users ON users.uid = userPosts.uid  WHERE (userPosts.uid = %s OR
    userPosts.uid IN (SELECT flwdid FROM followers WHERE flwrid = %s)) AND upid > %s 
    ORDER BY time ASC"""
    cursor.execute(query, (uid, uid, upid))
    result = cursor.fetchall()
    cursor.close()
    connection.close()
    return result


Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15359
  • View blog
  • Posts: 61,583
  • Joined: 12-June 08

Re: I want to get one more data from a related table.

Posted 18 May 2019 - 11:29 AM

I would go the route of joining on a sub query.

Example.

I have a table of IDs and names, and a second table tracking which ID liked another one.

create table #USER (ID int, NAME varchar(10))
create table #LIKES (ID int, ID_LIKED INT)

INSERT INTO #USER (ID, NAME) VALUES (1, 'A')
INSERT INTO #USER (ID, NAME) VALUES (2, 'B')
INSERT INTO #USER (ID, NAME) VALUES (3, 'C')

-- TWO IDS THAT LIKED ID 1
INSERT INTO #LIKES (ID, ID_LIKED) VALUES(2, 1)
INSERT INTO #LIKES (ID, ID_LIKED) VALUES(3, 1)
INSERT INTO #LIKES (ID, ID_LIKED) VALUES(3, 2)

SELECT * FROM #USER
SELECT * FROM #LIKES

DROP table #LIKES 
drop table #USER


ID          NAME
----------- ----------
1           A
2           B
3           C

(3 rows affected)

ID          ID_LIKED
----------- -----------
2           1
3           1
3           2


Bottom table - ID 2 like ID 1 and ID 3 like ID 1.. and ID 3 liked ID 2.

If I use a GROUP BY I can get the count of those.

SELECT ID_LIKED, COUNT(*) AS COUNT_OF_LIKES
FROM #LIKES
GROUP BY ID_LIKED

ID_LIKED    COUNT_OF_LIKES
----------- --------------
1           2
2           1



Not bad.. then if I jam that into a subquery to join off of I can get the count of likes for id 1.

SELECT A.ID, A.NAME, isnull(B.COUNT_OF_LIKES, 0) as COUNT_OF_LIKES
FROM #USER A
left JOIN (
	SELECT ID_LIKED, COUNT(*) AS COUNT_OF_LIKES
	FROM #LIKES
	GROUP BY ID_LIKED
) B ON A.ID = B.ID_LIKED
WHERE A.ID = 1



ID          NAME       COUNT_OF_LIKES
----------- ---------- --------------
1           A          2


If I use ID 3, which no one has liked, I get this.

ID          NAME       COUNT_OF_LIKES
----------- ---------- --------------
3           C          0

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1