Home / Questions / 500925
Accepted
Darinel Cigarroa
Asked: 2022-05-03 08:53:09 +0800 CST 2022-05-03 08:53:09 +0800 CST

Query of two tables related to Eloquent - Laravel

  • 772

Hello, first of all, thanks for reading my question. I have made a query to my database relating 2 tables. I have done this using JOIN and I was wondering how the same query could be made with Eloquent. The main table is cat_tabulator_histories and this table is related to cat_contract_types and cat_sub_contract_type, I want to get the records from cat_tabulator_histories bringing with it the records that are related.

I share my query in JOIN so that it is better understood:

 $tabulatorsHistory = DB::table('cat_tabulator_histories')
            ->where('cat_tabulator_id', $id)
            ->join('cat_contract_types', 'cat_contract_types.id', '=', 'cat_tabulator_histories.cat_contract_type_id')
            ->join('cat_sub_contract_types', 'cat_sub_contract_types.id', '=', 'cat_tabulator_histories.sub_contract_type_id')
            ->select('cat_tabulator_histories.id as id', 'cat_tabulator_histories.name as name', 'cat_tabulator_histories.base_salary_cents as salary_cents', 'cat_tabulator_histories.compensation_cents as compensation_cents', 'cat_tabulator_histories.is_active as active', 'cat_tabulator_histories.created_at as created_at','cat_tabulator_histories.start_date as start_date','cat_tabulator_histories.end_date as end_date', 'cat_tabulator_histories.date_active as date_active', 'cat_contract_types.name as type_contract', 'cat_sub_contract_types.name as type_subcontract')
            ->orderBy('cat_tabulator_histories.start_date', 'DESC')
            ->get();

Thank you for your answers. It should be noted that my tables are already related at the model level. Again, thank you very much. Any opinion on where I can start would be very helpful.

I have managed to do the query with eloquent in this way:

$tabulatorsHistory = CatTabulatorHistory::where('cat_tabulator_id', $tabulatorOriginal->id)
                ->with('contract','subContract')
                ->get();

Now the problem I am facing is that it brings me all the related table like this:

 {
            "id": 3,
            "name": "PRUEBA1.0",
            "base_salary_cents": 2000000,
            "daily_salary_cents": 0,
            "compensation_cents": 1000000,
            "start_date": "2021-12-15",
            "end_date": "2021-12-30",
            "is_active": false,
            "budget_code": null,
            "gross_allocation": 0,
            "concepts": null,
            "cat_contract_type_id": 1,
            "cat_tabulator_id": 72,
            "sub_contract_type_id": 1,
            "date_active": "2021-12-02 10:36:27",
            "created_at": "2021-12-02T16:35:25.000000Z",
            "updated_at": "2021-12-02T16:35:25.000000Z",
            "contract": {
                "id": 1,
                "name": "BASE/ESTRUCTURA/OPERATIVO CONF",
                "christmas_bonus_days": 90,
                "gratification_days": null,
                "holidays": 20,
                "isActive": true,
                "is_unionized": false,
                "cat_vacations_type_id": 1,
                "cat_sat_contract_type_id": null,
                "cat_sat_work_day_type_id": null,
                "cat_sat_employee_regime_id": null,
                "schedules_id": null,
                "created_at": "2021-10-29T21:35:08.000000Z",
                "updated_at": "2021-10-29T21:35:08.000000Z"
            },
            "sub_contract": {
                "id": 1,
                "name": "BASE",
                "cat_contract_type_id": 1,
                "created_at": "2021-10-29T21:35:12.000000Z",
                "updated_at": "2021-10-29T21:35:12.000000Z"
            }
        },

I want only the name of each relationship.

sql

1 Answers

  1. Best Answer

    Query with JOIN:

     $tabulatorsHistory = DB::table('cat_tabulator_histories')
            ->where('cat_tabulator_id', $id)
            ->join('cat_contract_types', 'cat_contract_types.id', '=', 'cat_tabulator_histories.cat_contract_type_id')
            ->join('cat_sub_contract_types', 'cat_sub_contract_types.id', '=', 'cat_tabulator_histories.sub_contract_type_id')
            ->select('cat_tabulator_histories.id as id', 'cat_tabulator_histories.name as name', 'cat_tabulator_histories.base_salary_cents as salary_cents', 'cat_tabulator_histories.compensation_cents as compensation_cents', 'cat_tabulator_histories.is_active as active', 'cat_tabulator_histories.created_at as created_at','cat_tabulator_histories.start_date as start_date','cat_tabulator_histories.end_date as end_date', 'cat_tabulator_histories.date_active as date_active', 'cat_contract_types.name as type_contract', 'cat_sub_contract_types.name as type_subcontract')
            ->orderBy('cat_tabulator_histories.start_date', 'DESC')
            ->get();

    Consult with Eloquent:

    $tabulatorsHistory = CatTabulatorHistory::where('cat_tabulator_id', $tabulatorOriginal->id)
                ->with('contract','subContract')
                ->get();

    If you want to get only one field from each table in this case I use the name field:

    $tabulatorsHistory = CatTabulatorHistory::where('cat_tabulator_id', $tabulatorOriginal->id)
                ->with(['contract:id,name,cat_contract_types.id', 'subContract:id,name,cat_sub_contract_types.id'])
                ->get();

    load specific columns

    • 1