How to find for specific data in a many to many relationship?

Do you have a pivot table with all three foreign keys in it or do you have multiple pivot tables?

I have model_toy and child_toy.

You could do this on your Child model:

function hasToyWithModel($model) {
    return $this->toys()->whereHas('models', function ($query) use ($model) {
        $query->where('id', $model->id);
    })->exists();
}

I think that should work but I'm not sure if that's the most performant way.

Ohh. I didn't knew you can do queries with a many to many relationship.

Do I need to have table child_model?

No, that query actually uses 2 subqueries (which could make it slow with a lot of data).

So basically it says "Check if this Child has a Toy that has at least one Model with the ID of the given model"

But Toy has many to many relationship with Model. How does that query work?

With a subquery. Something like this:

SELECT * FROM child_toy WHERE EXISTS (SELECT * FROM model_toy WHERE model_id = ?)

Not sure about the exact syntax but something like that :)

Ohhh...I understand it now. Thank you very much!

@ftiersch What if I want to select all the child's toy with their model?

Does this work?

Child::with('toys.models')->find($id);

Yeah, that would work. You just put the name of the relationships in the with.

What if the child was given a new toy with a new model?

I could do a sync in child_toy but how would I add the model?

You don't have to, it works with your existing model_toy relationship. with() also gets the values dynamically.

Wait. So I just add the transaction to child_toy and it will already know what is its model?

Wait, I think you have an error in your database planning there.

One child can have multiple toys, right? But one toy can only be owned by a single child right? Also one toy is of a single Model but every Model can have multiple toys?

By the way. What I mean by model is the brand. My naming just got messed up. Sorry.

Child can have multiple toys, A toy can be own by multiple children. A toy can have many brands and a brand can have many toys.

Aah okay. That makes more sense. But why can a toy have many brands? Isn't a toy usually by a single brand?

Anyway. In that case you would have to add the relation between toy and brand and between toy and child. The rest should work as expected then.

In that case you would have to add the relation between toy and brand and between toy and child.

Yes. I have them. So when adding new child's toy, I just have to do this?

$child->toys()->sync([$id]);

and when fetching a specific child's toys and brands. I just have to do this?

Child::with('toys.brands')->find($id);

But why can a toy have many brands? Isn't a toy usually by a single brand?

Oh I did that because these toy names are ambiguous like Dinosaur, Ball, Truck etc.

If you add a new childs toy yes, that's all you need. But the toy needs to have all its brands already in the toy_brand relationship.

Yes. Before giving a child the toy, the toy is already recorded in toy_brand relationship.

Wait. The last model isn't fetched. I tried it just now.

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

Childs' toys are fetched but the brands aren't.

Are your relationships (so the methods) called "toys()" and "brands()"? And does the brands() relationship work if you use it normally on a Toy model?

Yes. It works when I do

Toy::with('brands')->get()

Wait. When I create toys, is this what I have to do?

Brands::find($id)->toys()->create([]);

Because when creating the toys, I did was :

$toy = Toy::create($request);
$toy->brands()->sync($request[$brand_id]);

$toy = Toy::create($request);
$toy->brands()->attach($brand_id);

Try this

I tried it. It's still the same.

I noticed that when I did this

Toy::with('brands')->find($id);

I get no brands. But it has data when I did Toy::with('brands')->get()

What could be the problem here?

@ftiersch okay, I managed to fix the problem.

But when I run this, I get all the brands for the toy that the child has even if the brand is different.

Child::with('toys.brands')->find($child_id);