Laravel Mysql从两个表中选择并在第三个表上连接结果

Question

I wonder if its possible to select two tables and join a third table

I have 3 tables:

upvotes -id (PK) -voteOn (FK) - points to users PK -voteBy (FK) - points to users PK -vote (Int) -voteAdded (Int) - unix timestamp

downvotes -id (PK) -voteOn (FK) - points to users PK -voteBy (FK) - points to users PK -vote (tinyInteger) -voteAdded (Int) - unix timestamp

users id (PK) username

What I now want to do is ~ select ALL votes from the tables upvotes and downvotes where voteOn = 2 then join table users on voteBy orderBy voteAdded Asc

More or less I want to get all votes on a specific user then join the users table so that I can get the username of the person who placed the vote

Answer 1

U can use the DB facade to achieve this which will generate an sql query that is fired on the db

  $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();

Then you can loop through the users using foreach

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

That should work