How to access pivot table with pivot table

I think your database structure needs work. owned_brand_pivot says that a child/toy combo belongs to a brand. The pivot should be between toy and brand, not brand and the pivot of child and toy.

Why do you have a model of ChildToy instead of 2 separate ones of Child and Toy?

Maybe if you explain why it's structured that way I could be more helpful.

If you really need that structure then you can always fall back to a raw SQL query.

ChildToy model is the pivot model. I have individual models and tables for child and toy. I also have toy_brand_pivot.

I could remove owned_brand_pivot and do brand_child_toy_pivot where there will be a 3-way foreign keys from child, toy and pivot. But I think that may be a bad practice for a database design.

A toy would only have one brand so your tables would be:

| Child  | Toy           | Brand  |
| -------- |---------------|------------|
| id        |     id         |       id |
|            | brand_id |   |

then a ChildToy pivot table as above.

edit: sorry I can't get the markdown for tables right yet.

I think you're misunderstanding what a pivot table is for. They are simply to create a many to many relationship between two things. A brand can have many toys. Can a toy have many brands? Can a brand have many children? Those relationships don't make sense to me.

If you want to, for instance, get the brands associated with a child you would get all the toys belonging to a child, and the toy would belong to a brand, which you could get also. You don't need to have a pivot table between all 3 for them to be connected.

The toys what I meant here have ambiguous names like Dinosaur, Ball, Rubik's Cube. So it is many to many because a dinosaur toy can have many brands.

So toy and brand have a pivot table brand_toy. Child shouldn't be in the pivot table. Child should be in its own table. The relationship between a child and a toy can be many to many because a child can have many toys and a toy can have many child owners. So that's a pivot table child_toy.

Once again you can get all the brands associated with a child through their relationships without accessing the pivot table itself.

// child model
public function toys() {
    $this->belongsToMany(Toy::class);
}

// toy model
public function children() {
    $this->belongsToMany(Child::class);
}

public function brands() {
    $this->belongsToMany(Brand::class);
}

// brand model
public function toys() {
    $this->belongsToMany(Toy::class);
}

// getting children's toys with brands
// not positive on how this is written, but something like...
Child::with('toys.brands')->get();
// should retrieve all of the toys and those toys' brands associated with a child.

Child::with('toys.brands')->get();

Yes it would grab the children with their toys, but all those toy brands' will also get fetched.

if I did this. Child::with('toys.brands')->find($childId)

For example,

The child that was fetched has the toy basketball, not only the brand associated to the child but all the brands that has a basketball toy will also get fetched.

Yes it would fetch the brands associated with the toy. What association is there between a child and a brand? Could you clarify what your question is?

That's why I need a pivot table for the coupled child_toy_id and brand_id so that I know what model the child has.

so that I know what model the child has.

Not sure what you mean by that.

sorry, what I meant was what brand the child has

Child::with('toys.brands')->get(); gets the brands the child has. A child can only have brands that are associated with the toys in their possession. I child can't have a brand directly. With that query, you will get a structure like this:

// children
[
    0 => [
        name => 'Tommy',
        'toys' => [
            0 => [
                'name' => 'dinosuar',
                'brands' => [
                    'fisher price'
                ]
            ],
            1 => [
                'name' => 'basketball',
                'brands' => [
                    'wilson',
                    'spalding'
                ]
            ]
        ]
    ],
    1 => [
        'name' => 'Chuckie',
        'toys' => [
            0 => [
                'name' => 'action figure',
                'brands' => [
                    'disney'
                ]
            ],
            1 => [
                'name' => 'basketball',
                'brands' => [
                    'wilson',
                    'spalding'
                ]
            ]
        ]
    ]
]

Then you can use the collection methods to manipulate the collection how you need to. If you only wanted the brands, you can use pluck to get them out of the data and use them how you need to.

I think the pluck statement would look like so:

$children = Child::with('toys.brands')->get();

// returns a collection of brands
$brands = $children->pluck('*.toys.*.brands')->unique();

But there's no record with the child and the brand, how is it even possible if there aren't another table?

It's just a chain.

We have a child.

We have a toy.

children

| id | name |
| --- | --- |
| 1 | Tommy |

toys

| id | name |
| --- | --- |
| 1 | dinosaur |

child_toy

| child_id | toy_id |
| --- | --- |
|1 | 1 |

We also have a brand:

brands

| id | name |
| --- | --- |
| 1 | Fisher Price |

and a relationship between a toy and a brand:

brand_toy

| brand_id | toy_id |
| --- | --- |
| 1 | 1 |

We join the child to the toy through the pivot table. Now we have the toy. Now that we have the toy, we can join to the brand table through their pivot table.

I thinking doing some learning about SQL and how tables join to each other will make understanding the eloquent relationships on top of it much easier to grasp. There's a new series on here that talks about these concepts.

Even though you join it, you fetch all the brands for that toy, not only the child's toy brand.

Ahh I get what you are saying now.

So then the toys relationship with the brand is one-to-many, not many-to-many. There wouldn't be a pivot table in this situation. The toys table has a foreign key that connects to the brand table. There may be duplicates of the name of the toy, but they are different because the brand is different. You could have 2 basketball toys but one is wilson and one is spalding.

toys

| id    | name      | brand_id

| 1     | Basketball    | 1
| 2     | Basketball    | 3

brands

| id    | name

| 1     | Wilson
| 2     | Fisher Price
| 3     | Spalding

and retrieving would go from

$children = Child::with('toys.brands')->get();

// returns a collection of brands
$brands = $children->pluck('*.toys.*.brands')->unique();

to

$children = Child::with('toys.brand')->get();

// returns a collection of brands
$brands = $children->pluck('*.toys.brand');

and the relationship would be

// toy model
public function brand() {
    return $this->belongsTo(Brand::class);
}

// brand model
public function toys() {
    return $this->hasMany(Toy::class);
}

It helps to think say it out loud to realize what the relationship is. A brand can have many toys, but a toy can only belong to one brand.

You're thinking the item in the pivot table is the thing. It's not the thing, it points to a relationship between 1 thing and another.

If you have 2 rows in your brand_toy pivot table:

toy_id | brand_id
1   | 2
1   | 3

You don't have 2 toys. You have 1 toy that belongs to 2 brands.

If you want 2 toys that can be owned by different brands you need 2 separate items in your toys table. And that means a toy can only have 1 brand, so the pivot table doesn't work. That's where the answer in my previous comment comes in.

But what if I want to get all the toy's brand?

I only want to fetch a specific brand only if it is chained to a child.

If feel like every relationship I see must be many-to-many. I need help.

When using many to many relationship, does it mean that they are completely independent to each other but they have a relationship?

For example, a child and toy

You can create a child that doesn't have a toy and you can create a toy that doesn't have an owner yet.

So if I made Child and Toy relationship one to many. Where the toy has the foreign key of child_id then every toy MUST have an owner/child because if not, it will have an error that the child_id doesn't have a default value.

Am I looking this scenario right?

#hasManyThrough might help in your case.. Please go through the documentation and the given example. I have not fully read your code yet. https://laravel.com/docs/5.2/eloquent-relationships#has-many-through

If you also want a toy to be associated with many brands, then you can break the toy up into 2 tables. You can have a toys table and a toy_types table. So now your toy table will have 2 foreign keys. One for the brand and one for the type.

toys

id | brand_id | type_id
1 | 1 | 1
2 | 2 | 1
3 | 1 | 2

toy_types

id | name
1 | Basketball
2 | Doll

Now you can use toys to get all of the toys of type 'Basketball' to get all of their brands. Or you can still do what I said earlier to get a child's toy brand.

@cwinterweb

If it is one to many, then how can I update it if the toy's brand has been changed. The brand is still exist but its toy isn't his anymore because it been given to another brand.

If you want to change the brand a toy is associated with you just update it's foreign key to point to a different brand.

$basketball = Toy::where([
    'name' => 'Basketball',
    'brand_id' => $currentBrand->id
])->first();

$basketball->update(['brand_id', $newBrand->id]);

$basketball->save();