想要使用外键关系获取laravel中当前登录用户的数据，但是会给出重复的行

Question

I am trying to get the data of current login user using laravel and mysql but I am getting the result duplicate rows of stored in data base. I have three tables. users table containing id as primary key, education table containing user_id as foreign key, the primary key of users table and also a field research_area. The third table is publications table which is also containing user_id as foreign key. Now I am trying to get the all publications of current login user with title, publication year and research area. My code is working but it gives me all the result of stored data in tables. My code is given here.

Controller code is PdfController.php

   <?php

    namespace App\Http\Controllers;

    use Illuminate\Http\Request;
    use DB;
    use PDF;
    use Auth;

    class PdfController extends Controller
    {
        public function publicationpdf()
            {
                $user_id = Auth::user()->id;
                $data['data'] = DB::table('users')
                    ->join('publications', 'users.id', '=', 'publications.user_id')
                    ->join('education', 'users.id', '=', 'education.user_id')
                    ->select('users.id','publications.*','education.research_area')
->where('users.id',$user_id)
                    ->get();
                if(count ($data)>0){
                    return view('pdf/publicationpdf',$data);
                }
                else
                {
                    return view('pdf/publicationpdf');
                }
     }

My view is publicationpdf.blade.php and its code is given here.

@extends('layouts.app')
@section('content')
<div class="container"><br>


    <h4>Name: {{ Auth::user()->tname }}</h4>



    <div class="text-center">
        <h3>Publications Details</h3>
    </div><br/>
    <table class="table">
        <tr>
            <th>
                Publication Title
            </th>
            <th>Research Area</th>
            <th>Publication Year</th>
        </tr>
        @foreach($data as $value)
        <tr>
            <td>{{$value->title}}</td>

            <td>{{$value->research_area}}</td>

            <td>{{$value->year}}</td>
        </tr>
        @endforeach

    </table>


</div>

@endsection

and my route is here.

Route::get('pdf/publicationpdf','PdfController@publicationpdf');

I dont know how to get the all publications of login user just one time every row, but it gives me duplicate rows, I means three copies of one row, It does not show any error but returning the duplicate data of user. Please help. Thank in advance

Picture is here

My tables are given Here. publications table.

Answer 1

Try this: You should add where user table has id=logged in user id.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use PDF;
use Auth;

class PdfController extends Controller
{
    public function publicationpdf()
        {
            $user_id = Auth::user()->id;
            $data['data'] = DB::table('users')
                ->join('publications', 'users.id', '=', 'publications.user_id')
                ->join('education', 'users.id', '=', 'education.user_id')
                ->select('users.id','publications.*','education.research_area')
                ->where('users.id',$user_id) // else it will get all rows
                ->get();
            if(count ($data)>0){
                return view('pdf/publicationpdf',$data)->with($user_id);
            }
            else
            {
                return view('pdf/publicationpdf');
            }
 }

Answer 2

How many rows you have in your education table for that particular user? The result you are getting is normal because you have multiple records in your child tables publication or education.