加入Laravel的3张桌子？

Question

I have 3 tables: Questions, Answers & Ratings

I want to join the answers table with questions table where questions.id = answers.question_id and ratings table to answers table where answers.id = ratings.answer_id

But it returns null

 $allQuestionWithAnswerAndRating = DB::table('questions')->orderBy('questions.id','desc' )
    ->join( 'answers', 'questions.id' , '=' , 'answers.question_id' )
    ->where('answers.user_id', '=' ,Auth::user()->id )
    ->join( 'ratings', 'answers.id' , '=' , 'ratings.answer_id')
    ->select( 'questions.id as id' , 'questions.body as question' , 'answers.body as answer' ,'answers.user_id as user_id')
    ->get();

What is wrong with this code?

Thanks

Answer 1

Take a look at the advanced join statements section of the eloquent documentation.

You may also specify more advanced join clauses. To get started, pass a Closure as the second argument into the join method. The Closure will receive a JoinClause object which allows you to specify constraints on the join clause

....

If you would like to use a "where" style clause on your joins, you may use the where and orWhere methods on a join. Instead of comparing two columns, these methods will compare the column against a value:

For your statement that would look something like this:

DB::table('questions')
    ->join( 'answers', function($join) {
        $join->on('questions.id' , '=' , 'answers.question_id')
             ->where('answers.user_id', '=' , Auth::user()->id);
    })->join( 'ratings', 'answers.id' , '=' , 'ratings.answer_id')
    ->select(
        'questions.id as id', 
        'questions.body as question', 
        'answers.body as answer',
        'answers.user_id as user_id'
    )->get();