How do i batch multiple select query calls into a single mysql query or procedure-CodePudding

I have this function which checks friendship status between 2 users using their userIds and it is working fine.

function checkFriendShipBetweenUsers(user1Id, user2Id) {

    var checkFriendShipBetweenUsersQuery = "SELECT status  FROM friends WHERE (user1Id=? AND user2Id =?) OR (user1Id=? AND user2Id =?)"
    var queryParameterList = [user1Id, user2Id, user2Id, user1Id]

}

I have a case in which i need to check friendship status between a user and other 3 users. I can call above function 3 times, one for each other user to get desired result but i would like to make it with a single db call using a single query or using a mysql procedure.

function checkFriendShipBetweenUsers(user1Id, userIdList) {

    var checkFriendShipBetweenUsersQuery = ""
    var queryParameterList = []

}

So this query/procedure call should return 3 integers indicating user1's friendship status with users in userIdList.

Here is an example db fiddle: db-fiddle.com/f/p5RP61V3AcawRgJcogeXey/1

given user1Id : 'a8t57h6p8n2efden' and

userIdList : ['typ3vg6xb1vt7nw2', 'cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl', '0bw87kprb97pes1crom8ceodi07r2kd0']

How do i write such query or procedure?

CodePudding user response：

DEMO

-- source data
CREATE TABLE test (
  id INT,
  user1Id VARCHAR(100),
  user2Id VARCHAR(100),
  status INT
);
INSERT INTO test (id,user1Id,user2Id,status) VALUES 
  (1,'a8t57h6p8n2efden','typ3vg6xb1vt7nw2',0),
  (2,'cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl','a8t57h6p8n2efden',1),
  (3,'0bw87kprb97pes1crom8ceodi07r2kd0','a8t57h6p8n2efden',2),
  (4,'a8t57h6p8n2efden','ap21wzbew0bprt5t',0);
SELECT * FROM test;

id	user1Id	user2Id	status
1	a8t57h6p8n2efden	typ3vg6xb1vt7nw2	0
2	cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl	a8t57h6p8n2efden	1
3	0bw87kprb97pes1crom8ceodi07r2kd0	a8t57h6p8n2efden	2
4	a8t57h6p8n2efden	ap21wzbew0bprt5t	0

-- searching parameters
SET @user1Id := 'a8t57h6p8n2efden';
SET @userIdList := '[
  "typ3vg6xb1vt7nw2",
  "cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl", 
  "0bw87kprb97pes1crom8ceodi07r2kd0", 
  "absent value"
  ]';

SELECT jsontable.userid, test.status
FROM JSON_TABLE( @userIdList,
                 '$[*]' COLUMNS ( rowid FOR ORDINALITY,
                                  userid VARCHAR(255) PATH '$'
                                  )) jsontable
LEFT JOIN test
  ON (@user1Id, jsontable.userid) IN ( (test.user1Id, test.user2Id), 
                                       (test.user2Id, test.user1Id)
                                       )

userid	status
typ3vg6xb1vt7nw2	0
cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl	1
0bw87kprb97pes1crom8ceodi07r2kd0	2
absent value	null

fiddle

If you do not need status value for the IDs which are not found then use INNER JOIN.

If you want to receive the output as one solid value then add according GROUP BY and aggregation. Use jsontable.rowid for to provide needed values ordering.

PS. If you won't use an aggregation then you may do not obtain rowid value - simply remove rowid FOR ORDINALITY, in this case.