How to join three tables with Laravel and keep data integrity

I am using Laravel 5.7 and now I am trying setup a relationship between three tables named:

  • Tickets (PK – TicketID, FK – CampusID)
  • Campus (PK – CampusID, FK – TechID)
  • User (PK – TechID)

I don’t think I set up my models correctly as I am showing a ticket where the CampusID doesn’t belong to the TechID. I am looking for a best practice on setting up Eloquent to keep the data integrity in place so I can prevent any abnormalities. As mentioned above the foreign key for Tickets should reference the Campus primary key, and Campus foreign key should reference the User primary key.

Here are my Models:

Ticket

protected $table='tickets';

public function user() {
    return $this->belongsTo(User::class);
}

Campus
protected $table='campus';

public function user() {
    return $this->belongsTo(User::class);
}

User
public function campus()
{
    return $this->hasMany(Campus::class, 'TechID');
}

public function ticket()
{
    return $this->hasMany(Ticket::class, 'AssignedTo');
}

Here is my controller:
namespace AppHttpControllers;

use IlluminateHttpRequest;
use Auth;
use AppCampus;
use AppTicket;

class PagesController extends Controller
{

    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth');
    }

    // Dashboard Page
    public function index()
    {
        $user = Auth::user();
        $campuses = Campus::where('TechID',$user->id)->pluck('CampusName');
        $tickets = Ticket::all()->where('AssignedTo', $user->id);

        return view('home')->with(['user' => $user,'campuses'=>$campuses,'tickets'=>$tickets]);
    }
    // Queue Page
    public function Queue() {
        return view('Pages.Queue');
    }
    // Reports Page
    public function Reports() {
        return view('Pages.Reports');
    }
    // Search Page
    public function Search() {
        return view('Pages.Search');
    }
}

I think my models are fine, but my controller is probably where I made some mistakes. I’ve tried reading questions on here already, watching videos, and reading the Laravel docs, but nothing has really clicked with me yet. I really appreciate any and all help. Ideally it should cascade changes. So if I have a situation where I want to change what location a tech belongs to I could just make the change in the Campus table probably in the TechID column.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I would use Eager Loading.

public function index()
{
    $user = User::with([
        'campuses' => function($query) {
           $query->select(['id', 'CampusName']);     
        }, 
        'tickets'
      ])->where('id', Auth::id())->first();

    $campuses = $user->campuses->pluck('CampusName');
    $tickets = Ticket::all()->where('AssignedTo', $user->id);

    return view('home')->with([
       'user' => $user,
       'campuses'=>$user->campuses->pluck('CampusName'),
       'tickets'=>$user->tickets]);
}

EDIT

You need to update your User model.

public function campuses()
{
    return $this->hasMany(Campus::class, 'TechID');
}

public function tickets()
{
    return $this->hasMany(Ticket::class, 'AssignedTo');
}


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x