How to connect 3 table?

Question

In my case, I have three tables:

• type

  • id
  • type
  • reason_id

• reason

  • id
  • reason

• permission

  • id
  • type_id
  • reason_id (not sure has or not)

I expect that:

{
    "message": "Get all permissions with type and reason",
    "data": [
        {
            "id": "1",
            "types": [
                "id": "1",
                "type": "Sick",
                "reasons": [
                    "id": "1",
                    "reason": "covid-19"
                ]
            ]
        }

Answer 1

in laravel try this:

Reason::select(
    'reasons.id as reasonId',
    'reasons.reason as reason',
    'types.id as typeId',
    'types.type as type',
    'permissions.id as permissionId',
)
    ->join('types', 'reasons.id', '=', 'types.reason_id')
    ->join('permissions', 'types.id', '=', 'permissions.type_id')
    ->get();

Answer 2

$getDetailsLike = City::leftJoin('states as ST', 'ST.id', '=', 'cities.state_id')
        ->leftJoin('countries as CO', 'CO.id', '=', 'ST.country_id')
        ->where('CO.id', 101)
        ->where('ST.id', 4030)
        ->where('cities.name', 'LIKE', '%nagar%')
        ->select('cities.*', 'ST.id as state_new_id', 'ST.name as state_name', 'CO.id as country_new_id', 'CO.name as country_name')
        ->get();