Laravel 8 nested Model Relationships

Question

This is my first ever Laravel project, please bear with us. I am trying to display a parent->child hierarchy (not recursive), something akin to below:

Category 1
----List item
----------Resource 1
----List item
----------Resource 2
Category 2
-----List item 1
-----List item 2....etc

I can't get my head around how to write the query to get the resources linked to the subStatement, not the top category, basically, an inner join related to SubStatement. It is currently displayed as so;

    {
        "id": 1,
        "name": "Category 1: Practising plumbing overview",
        "description": "<p>Category 1: Practising plumbing overview</p>",
        "created_at": "2022-01-06 15:48:49",
        "updated_at": "2022-01-06 15:48:49",
        "deleted_at": null,
        "plumbing_area_id": 1,
        "category_id": 1,
        "SubStatement": [
            {
                "id": 1,
                "title": "Plying narrative for Category 1",
                "description": "<p>Body of content.</p>",
                "theme_id": 1,
                "plumbing_area_id": 1
            },
            {
                "id": 2,
                "title": "Lay narrative for Category 1",
                "description": "<p>body description.</p>",
                "theme_id": 1,
                "plumbing_area_id": 1
            }
        ],
        "resources": []
    },
    {
        "id": 2,
        "name": "Category 2: AAV Practising plumbing introduction",
        "description": "<p><strong>Category 2:</strong> AAV Practising plumbing introduction</p>",
        "created_at": "2022-01-06 15:49:12",
        "updated_at": "2022-01-06 15:49:12",
        "deleted_at": null,
        "plumbing_area_id": 1,
        "category_id": 1,
        "SubStatement": [
            {
                "id": 3,
                "title": "Plying narrative for Category 2",
                "description": "<p>Body of content;</p>",
                "theme_id": 2,
                "plumbing_area_id": 1
            },
            {
                "id": 4,
                "title": "Lay narrative for Category 2",
                "description": "<p>Body of content</p>",
                "theme_id": 2,
                "plumbing_area_id": 1
            }
        ],
        "resources": []
    },
    {
        "id": 3,
        "name": "Category 3: AAV Practising plumbing design",
        "description": "<p><strong>Category 3: </strong>AAV Practising plumbing design</p>",
        "created_at": "2022-01-06 15:49:47",
        "updated_at": "2022-01-06 15:49:47",
        "deleted_at": null,
        "plumbing_area_id": 1,
        "category_id": 1,
        "SubStatement": [
            {
                "id": 5,
                "title": "Plying narrative for Category 3",
                "description": "<p>Body of content;</p>",
                "theme_id": 3,
                "plumbing_area_id": 1
            },
            {
                "id": 6,
                "title": "Lay narrative for Category 3",
                "description": “Blah blah</p>",
                "theme_id": 3,
                "plumbing_area_id": 1
            }
        ],
        "resources": []
    },
    {
        "id": 4,
        "name": "Category 4: another “category,
        "description": “Body</p>",
        "created_at": "2022-01-06 15:50:04",
        "updated_at": "2022-01-06 15:50:04",
        "deleted_at": null,
        "plumbing_area_id": 1,
        "category_id": 1,
        "SubStatement": [
            {
                "id": 7,
                "title": "Plying narrative for Category 4",
                "description": "<p>sdfsdfsdf;</p>",
                "theme_id": 4,
                "plumbing_area_id": 1
            },
            {
                "id": 8,
                "title": "Lay narrative for Category 4",
                "description": "<p> sdfsdfsdfsdf</p>",
                "theme_id": 4,
                "plumbing_area_id": 1
            }
        ],
        "resources": []
    },
   ]

I am looking for something like below instead, please note where the resource node is:

[
  {
    "id": 1,
    "name": "Category 1: Practising plumbing overview",
    "description": "<p>Category 1: Practising plumbing overview</p>",
    "created_at": "2022-01-06 15:48:49",
    "updated_at": "2022-01-06 15:48:49",
    "deleted_at": null,
    "plumbing_area_id": 1,
    "category_id": 1,
    "SubStatement": [
      {
        "id": 1,
        "title": "Plying narrative for Category 1",
        "description": "<p>Body of content.</p>",
        "theme_id": 1,
        "plumbing_area_id": 1,
        "resources": [
          {
            "id": 1,
            "resource_title": "The name of the resource"
          }
        ]
      }
    ]
  }
]

//Themes Model

{
   return $this->hasMany(Statement::class, 'theme_id', 'id');
}
public function resources()
{
 return $this->belongsToMany(Resource::class);
}

//the Controller below is outputting the the JSON above

{
 $output = Theme::where ( 'category_id', '=', $category_id )
                    ->with(['subStatement' =>
                        fn ($query) =>
                        $query->select('id','title','description','theme_id','therapy_area_id')

                    ])
                    ->with(['resources' =>
                        fn ($query) =>
                        $query->select('id','temporary_url')])
                    ->get();
}

// I attempted writing something like this. It threw a "Integrity constraint violation: 1052 Column 'id' in field list is ambiguous" error

{
    return $this->hasOneThrough(Statement::class, Resource::class);
}

Try defining a oneToMany relation (subStatement-hasMany-resources) relation in subStatement model, then in your query, instead of : ->with(['subStatement' =>, do: ->with(['subStatement.resources' => and it should fetch all the resources also that belong to a subStatement — Subash, Jan 17 '22 at 11:25
Try changing to `$query->select('statements.id', ...` and `$query->select('resouces.id' ...`) _assuming statements and resources are your tables name_ — Clément Baconnier, Jan 17 '22 at 11:32
@ClémentBaconnier - The resource table is another model, so when I select the resources.id column, it gives me a "Column not found: 1054 Unknown column 'resources.theme_id'" error. Basically looking to do the Theme model link, what I need is the SubStaement resources links — Matt Allen, Jan 17 '22 at 16:00
@SuvashBhandari - When I try your example, it gives me a "Call to undefined relationship [SubStatement] on model [App\\Models\\Theme].", — Matt Allen, Jan 17 '22 at 16:03
Sorry I read your question too fast. Can you share your database relationship? It will help us to connect the dots between your models and your architecture. — Clément Baconnier, Jan 17 '22 at 16:46
@ClémentBaconnier - I've just added the DB schema to the question. I only want to get the resources that belong to the Statement Model, the resources should appear under the Statement Model (SubStatements of the Themes). The link table between resource and statement is resource_statement — Matt Allen, Jan 17 '22 at 18:03
@ClémentBaconnier. - Just to add, the hierachy of the payload should be Theme => Statement => Statement-Resources, as described in my question. Only thing I'm stuck is trying to get through the Statement Model to its related resource(s) — Matt Allen, Jan 17 '22 at 18:12
Thanks. You have two existing relationship between `themes` and `statements` : 1-N and N-N which one should be used? Also I'm guessing, in your model, one relationship is called `subStatements` and the other `statements`, which is which? — Clément Baconnier, Jan 18 '22 at 09:28
@ClémentBaconnier - Thanks for getting back to me. The one that should be used is the 1-N, one statement, many resources. SubStatement is only descriptive, it actually should be Statement. Sorry, I've moved it around a bit, from Theme to Statement Model. So It's/should be 1 Theme to many Statements, and 1 Statement to many resources. Thanks again. — Matt Allen, Jan 18 '22 at 09:42

score 3 · Accepted Answer · answered Jan 18 '22 at 10:08

Your main issue is that you need to nest the query.

I will first adding the relationships to your models according to your schema.
Then, I will add the base query for the nesting if I understood what you're trying to achieve correctly.

Models

Theme.php


public function subStatements()
{
    return $this->hasMany(Statement::class); // 1-N
}

public function statements()
{
    return $this->belongsToMany(Statement::class); // N-N
}

public function resources
{
    return $this->belongsToMany(Resource::class); // N-N
}

Statement.php


public function theme()
{
    return $this->belongsTo(Theme::class); // 1-N
}

public function themes()
{
    return $this->belongsToMany(Theme::class); // N-N
}

public function resources
{
    return $this->belongsToMany(Resource::class); // N-N
}

Resource.php


public function themes()
{
    return $this->belongsToMany(Theme::class); // N-N
}

public function statements()
{
    return $this->belongsToMany(Statement::class); // N-N
}

Query


$themes = Theme::query()
               ->where('category_id', $category_id)
               ->with([
                  'subStatements' => fn ($query) => $query->with('resources') 
               ])
               ->get();

I didn't add the `select` yet to first see if this is working for you. — Clément Baconnier, Jan 18 '22 at 10:09
Also, I have pluralized `subStatements` since, according to your schema, on the relation 1-N, a theme has many subStatements and a subStatement has one Theme — Clément Baconnier, Jan 18 '22 at 10:11
Glad it works for you :) You should be able to accept the answer to mark your question as solved though — Clément Baconnier, Jan 18 '22 at 12:33
That was it! Many thanks. Didn't know I could upvote; just did, and have marked it as the accepted answer. Really appreciate your help. — Matt Allen, Jan 18 '22 at 14:55

Laravel 8 nested Model Relationships

1 Answers1

Models

Query