如何在我的sql查询中获得正确的用户ID？

Question

我的节点应用程序中有以下查询:

select
  *
from
  messengerList
where
  messenger_id = ${u_id}
  and not status = "rejected"
  or messenged_id = ${u_id}
  and not status = "rejected"

它在函数中如下所示

let chatIdList = await sqlSrv.getSqlResults(`select * from messengerList where messenger_id=${u_id} and not status="rejected" or messenged_id=${u_id} and not status="rejected"`)
    .catch(error => {throw error});

我不想全选，我只想选择id和Messenger_ID或Messenged_ID，其中id不等于U_ID。
因此，ChatidList应如下所示:

[{id: 1, messenger_id: 3}, {id: 2, messenged_id: 5}] // messenger_id != messenged_id != u_id

我可以通过更改查询来实现这一点吗？如果是，你能提供这样一个查询或一个例子吗？

Answer 1

基本上，您所做的除了添加另一个条件来消除那些您不想显示的:

select
  *
from
  messengerList
where
not status = "rejected"
and messenger_id <> messenged_id
  and (
       messenger_id = ${u_id}
       or messenged_id = ${u_id}
  )

Answer 2

如果我理解正确的话。首先，列出所有数据，然后用if查询

简单代码

SELECT * FROM messengerList

然后，

  if(res.rows[i].messenger_id==u_id){
          //....
     }else{
          //----
     }