在单个查询MYSQL中从同一个表中选择两种类型的信息

I have a 'users' table like bellow. When a new user sign in, he has to use one 'sponsor_id' from previous user. Previous user's 'user_name' being used as 'sponsor_id' for a new user. Now, I want to show some 'sponsor_id' details when a new user signed in like, sponsor_id's first_name, last_name, phone etc. All data are saved in 'users' table. What should be the query,Please anyone help me. I am using Laravel5.4. I am trying something like-

我有一个像'bellow'这样的'用户'表。当新用户登录时，他必须使用前一个用户中的一个'sponsor_id'。以前用户的'user_name'被用作新用户的'sponsor_id'。现在，我想在新用户登录时显示一些'sponsor_id'详细信息，例如sponsor_id的first_name，last_name，phone等。所有数据都保存在'users'表中。应该是什么查询，请有人帮助我。我正在使用Laravel5.4。我正在尝试像 -

$sponsor_info = DB::table('users')
            ->select('first_name','last_name','phone')
            ->where('user_name', '=', '$sponsor_id')
            ->first();

3 个解决方案

#1

I dont know laravel but Im giving the solution plz convert it to laravel.

我不知道laravel但我给解决方案plz将其转换为laravel。

select m.*,
       s.first_name,
       s.last_name,
       s.phone 
from user m 
left join user s 
 on m.sponsorid=s.userid

#2

SELECT 
   user.id,
   user.user_name,
   user.sponsor_id,
   sponsoredUser.user_name
   FROM user
   INNER JOIN user AS sponsoredUser ON user.sponsor_id = sponsoredUser.id
WHERE user.id = $id

Here I am using user table 2 times . First is using for that user and the second is using for the getting information of that sponsored user. That's why I given the second user table name an alias name called sponsoredUser . Hope you will transfer this query into laravel.

这里我使用用户表2次。首先是用于该用户，第二个用于获取该赞助用户的信息。这就是为什么我给第二个用户表名称一个名为referencedUser的别名。希望您将此查询转移到laravel。

#3

Add this function to your user model:

将此功能添加到您的用户模型：

public function sponsor()
{
   $this->belongsTo(App\User::class,'sponsor_id','user_name');
}

Now you can make this query:

现在您可以进行以下查询：

$users = App\User::with('sponsor')->get();

All users will have a "sponsor" relation loaded.

所有用户都将加载“赞助商”关系。

Foreach ($users as $user){
   $user->sponsor->first_name;
   // Etc
}

#1