从第三个中获取条件时从两个表中选择单个值

I have a MySQL query that is baffling me. I am trying to grab specific pieces of information from two different tables (blurbs and users), while restricting it to only people who are following.

我有一个令我困惑的MySQL查询。我试图从两个不同的表(模糊和用户)中获取特定信息,同时将其限制为仅关注的人。

I have the following query:

我有以下查询:

SELECT DISTINCT blurbs.text, blurbs.timestamp, users.name, 
     users.username, users.profilepic, users.id 
FROM blurbs,users 
LEFT OUTER JOIN follows 
ON blurbs.uid = follows.following AND follows.follower = ? 
WHERE (blurbs.uid = $user_id OR follows.following IS NOT NULL) 
     AND (LOWER(blurbs.text) LIKE '%$query%' OR LOWER(users.name) LIKE '%$query%') 
     AND blurbs.is_private=0 AND blurbs.uid=users.id 
LIMIT 0,30

It's not working properly, but I am getting overly confused because of the joins.

它不能正常工作,但由于连接,我变得过于困惑。

What should I do to remedy this?

我应该怎么做才能解决这个问题?

2 个解决方案

#1

Try using all the join condition through where clause only e.g.

尝试仅通过where子句使用所有连接条件,例如

      select a.*, b.*     //<-- Selectign data from first two tables
      from table1 a, table2 b, table1 c
       where a.id = b.id         //<-- joining condition for first two tables
       and a.xxx= c.yyyyy        //some relation with third table
       and c.aaa =....           //filter conditions using third table
       and c.bbb =...            //filter conditions using third table

Trying to rephrase your query as below:

尝试重新编写您的查询,如下所示:

   SELECT DISTINCT blurbs.text, blurbs.timestamp, users.name, 
       users.username, users.profilepic, users.id 
   FROM blurbs,users, follows
   WHERE blurbs.uid=users.id
     AND blurbs.uid = follows.following 
     AND follows.follower = ?
     AND (blurbs.uid = $user_id OR follows.following IS NOT NULL) 
     AND (LOWER(blurbs.text) LIKE '%$query%' OR LOWER(users.name) LIKE '%$query%') 
     AND blurbs.is_private=0
   LIMIT 0,30

Having done this, I hope you can better control/manage the desired filter conditions.

完成此操作后,我希望您可以更好地控制/管理所需的过滤条件。

#2

While I am not completely sure if this is exactly what you are looking for, let me explain what I have done based on my understanding of your question. Firstly, an INNER JOIN between blurbs and users instead of your cross join assuming that there will be a user for a blurb Also, since you said that you want to restrict it only to those who are following, i think you could possibly replace the LEFT OUTER with an INNER JOIN to help filter out the non-followers.

虽然我不完全确定这是否正是您正在寻找的,但让我根据我对您的问题的理解来解释我的所作所为。首先,在blurbs和用户之间进行INNER JOIN,而不是你的交叉连接,假设有一个用户可以使用blurb另外,因为你说你想把它限制在那些跟随的人,我想你可能会替换LEFT使用INNER JOIN进行外部操作以帮助过滤掉非关注者。

SELECT DISTINCT 
     blurbs.text, 
     blurbs.timestamp, 
     users.name, 
     users.username, 
     users.profilepic, 
     users.id 
FROM blurbs 
INNER JOIN users 
     ON blurbs.uid = users.id 
INNER JOIN follows 
     ON blurbs.uid = follows.following 
     AND follows.follower = users.id 
WHERE blurbs.uid = $user_id 
     AND (LOWER(blurbs.text) LIKE '%$query%' OR LOWER(users.name) LIKE '%$query%') 
     AND blurbs.is_private=0 
LIMIT 0,30

#1