Query chaining multiple tables

Draven

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');
    }
}
Jared Rolt
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.

Эта статья взята из Интернета, укажите источник при перепечатке.

Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.

Отредактировано в
0

я говорю два предложения

0обзор
Войти в системуУчаствуйте в комментариях

Статьи по теме

TOP список

  1. 1

    Распределение Рэлея Curve_fit на Python

  2. 2

    How to click an array of links in puppeteer?

  3. 3

    В типе Observable <unknown> отсутствуют следующие свойства из типа Promise <any>.

  4. 4

    Как добавить Swagger в веб-API с поддержкой OData, работающий на ASP.NET Core 3.1

  5. 5

    Нарисуйте диаграмму с помощью highchart.js

  6. 6

    无法通过Vue在传单中加载pixiOverlay

  7. 7

    Отчеты Fabric Debug Craslytic: регистрация, отсутствует идентификатор сборки, применить плагин: io.fabric

  8. 8

    Статус HTTP 403 - ожидаемый токен CSRF не найден

  9. 9

    TypeError: store.getState não é uma função. (Em 'store.getState ()', 'store.getState' é indefinido, como posso resolver esse problema?

  10. 10

    ContentDialog.showAsync в универсальном оконном приложении Win 10

  11. 11

    В UICollectionView порядок меняется автоматически

  12. 12

    Merging legends in plotly subplot

  13. 13

    Elasticsearch - Нечеткий поиск не дает предложения

  14. 14

    Bogue étrange datetime.utcnow()

  15. 15

    Объединение таблиц в листе Google - полное соединение

  16. 16

    Single legend for Plotly subplot for line plots created from two data frames in R

  17. 17

    как я могу удалить vue cli 2?

  18. 18

    ViewPager2 мигает / перезагружается при смахивании

  19. 19

    Компилятор не знает о предоставленных методах Trait

  20. 20

    JDBI - В чем разница между @define и @bind в JDBI?

  21. 21

    проблемы с AVG и LIMIT SQL

популярныйтег

файл