从两个表中选择结果，并在第三个表上连接结果 - Laravel Mysql Select from two tables and join result on a third table

Laravel Mysql Select from two tables and join result on a third table

我想知道是否可以选择两个表并加入第三个表

我有3个表:

问题id (PK)- voteon (FK) -指向用户PK- voteby (FK) -指向用户PK投票(Int)- voteadded (Int) - unix时间戳

downvotes id (PK)- voteon (FK) -指向用户PK- voteby (FK) -指向用户PK投票(tinyInteger)- voteadded (Int) - unix时间戳

id (PK)用户名

我现在要做的是~从表upvotes和downvotes中选择所有投票，其中voteOn = 2，然后连接表users on voteBy order voteAdded Asc

或多或少，我想获得一个特定用户的所有投票，然后加入users表，这样我就可以获得投票的人的用户名

您可以使用DB facade来实现这一点，这将生成一个在DB

上触发的sql查询。

  $users = DB::table("users AS a") 
                   ->select(array("a.id as user_id", "a.username", "b.voteOn as upvote_on",
                "b.vote as upvote", "b.voteAdded as upvote_added","c.voteOn as downvote_on",  "c.vote as downvote",
                  "c.voteAdded as downvote_added"))
                   ->join("upvotes AS b",  "a.id", "=", "b.voteBy")
                   ->join("downvotes AS c",  "a.id", "=", "c.voteBy")
                   ->orderBy("b.voteAdded", "asc") 
                   ->get();

然后可以使用foreach

遍历用户

   foreach($users as $user) {
      $user->user_id;
      //use the alias as used in the select chained function 
    }

应该可以