Draven Draven - 3 months ago 10
MySQL Question

Query chaining multiple tables

I am trying to get the users (ID 1) trip data from the

user_trips
table, along with that trips flights in the
user_flights
table, and I am getting the
airports
.
name
using the
flight_from
and
flight_to
foreign keys.

I've tried numerous ways, and I assume it's very easy but this is my first Laravel project. I've read the documentation and watched LaraCasts but I just can't figure out the right combination.

The
$user_flights
variable returns:

[
{
"id":1,
"user_trips_id":6,
"flight_from":1,
"flight_to":14,
"flight from":
{
"id":1,
"code":"AIZ",
"name":"Lee C Fine Memorial",
"city":"Lake Of The Ozarks",
"country":"United States"
},
"flightto":
{
"id":14,
"code":"AEX",
"name":"Alexandria Intl Arpt",
"city":"Alexandria",
"country":"United States"
}
},
{
"id":2,
"user_trips_id":7,
"flight_from":1,
"flight_to":22,
"flight from":
{
"id":1,
"code":"AIZ",
"name":"Lee C Fine Memorial",
"city":"Lake Of The Ozarks",
"country":"United States"
},
"flightto":
{
"id":22,
"code":"ADG",
"name":"Lenawee County Arpt",
"city":"Adrian",
"country":"United States"
}
}
]


I need it to return something like this (I think):

[
{
"id":6,
"user_id":1,
"name":"My Trip",
"flights":
{
"id":1,
"user_trips_id":6,
"flight_from":1,
"flight_to":14,
"flight from":
{
"id":1,
"code":"AIZ",
"name":"Lee C Fine Memorial",
"city":"Lake Of The Ozarks",
"country":"United States"
},
"flightto":
{
"id":14,
"code":"AEX",
"name":"Alexandria Intl Arpt",
"city":"Alexandria",
"country":"United States"
}
}
}
]


Schema

# `user_trips` table
Schema::create('user_trips', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id')->unsigned()->index();
$table->text('name');
});
# `user_flights` table
Schema::create('user_flights', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_trips_id')->unsigned();
$table->integer('flight_from')->unsigned();
$table->integer('flight_to')->unsigned();
$table->foreign('user_trips_id')->references('id')->on('user_trips')->onDelete('cascade');
$table->foreign('flight_from')->references('id')->on('airports')->onDelete('cascade');
$table->foreign('flight_to')->references('id')->on('airports')->onDelete('cascade');
});


TripBuilderController

<?php
namespace App\Http\Controllers;

use App\Airport;
use App\UserFlights;

/**
* Class TripBuilderController
*
* @package App\Http\Controllers
*/
class TripBuilderController extends Controller
{
/**
* @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
*/
public function index()
{
$airports=Airport::all();

# Returns all `user_trips` and `user_flights`.
# I need to return only `user_trips`.`user_id` associated to user ID 1,
# and the `user_flights` associated to the user's `user_trips`.
$user_flights=UserFlights::with('flightfrom')->with('flightto')->get();

return view('welcome', compact('airports', 'user_flights'));
}
}


UserTrips Model

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

/**
* Class UserTrips
*
*/
class UserTrips extends Model
{
/**
* Indicates if the model should be timestamped.
*
* @var bool
*/
public $timestamps=FALSE;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable=[
'name',
'user_id'
];

/**
* @param UserFlights $user_flights_obj
* @return Model
*/
public function addFlight(UserFlights $user_flights_obj)
{
return $this->userflights()->save($user_flights_obj);
}

/**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function userflights()
{
return $this->hasMany(UserFlights::class);
}

/**
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function user()
{
return $this->belongsTo(User::class);
}

/**
* @return mixed
*/
public function addTrip()
{
# Retrieve the trip by the attributes, or instantiate a new instance...
$trip_obj=$this->firstOrNew(['user_id'=>1]);
if(!$trip_obj->id)
{
$trip_obj->name='My Trip';
$trip_obj->save();
}

return $trip_obj;
}
}


UserFlights Model

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

/**
* Class UserFlights
*
*/
class UserFlights extends Model
{
/**
* Indicates if the model should be timestamped.
*
* @var bool
*/
public $timestamps=FALSE;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable=[
'flight_from',
'flight_to'
];

/**
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function flightfrom()
{
return $this->belongsTo(Airport::class, 'flight_from');
}

/**
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function flightto()
{
return $this->belongsTo(Airport::class, 'flight_to');
}

/**
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function usertrip()
{
return $this->belongsTo(UserTrips::class, 'user_trips_id');
}
}

Answer
User::find(1)->userTrips()->with('userflights', 'userflights.flightfrom', 'userflights.flightto')->get();

or calling directly from UserTrips

UserTrips::whereUserId(1)->with('userflights', 'userflights.flightfrom', 'userflights.flightto')->get();

with() gives you eager loading and when you convert a model a JSON, it includes any of the eager loaded models you specified.

Comments