Listing a User's Products by Category in Eloquent

@hfalucas There's no easy way when you're querying user, but just do this:

$userId = 1;

$callback = function ($q) use ($userId) {
  $q->where('users.id', $userId);
};

$categories = Category::with(['products' => function ($q) use ($callback) {
   // get only products of the user
   $q->whereHas('users', $callback);
}])
  // get only categories of the user (through products)
  ->whereHas('products.users', $callback)
  ->get();

Damed jarek eloquent as no secret for you.

I was thinking the same with nested whereHas. Dot notation works with whereHas really ?

@pmall Yeah, it works at last in L5.

...after I personally got bored with with vs has inconsistency ;)

https://github.com/laravel/framework/pull/6726

And yeah, Eloquent is my shit :)

Awesome @JarekTkaczyk . I have to agree Eloquent is really your shit! Thank you ;)

quick question @JarekTkaczyk ... is there a way I can access the pivot table?

public function products()
{
  return $this->belongsToMany('Product')->withPivot('the attribute you want');
}

:)

I'm doing that @pmall :)

The thing is with that query I can't access the pivot table.

Thinking better I don't really think I need it xD

@hfalucas Access in the query? You can do it of course:

$callback = function ($q) use ($userId) {
  $q->where('users.id', $userId)
      ->where('product_user.some_column', 'some_value');
};

I hate this thread :D The schema is obvious. The wanted result is quite basic, but it is hell to execute this query.

Totally agree @pmall :D

Thanks once again @JarekTkaczyk

@JarekTkaczyk not access them in the query, I mean print them too. Lets say product_user table has more fields other then the id's is possible to include them in the final result?

In my models I added them ->withPivot('field1, field2') but it seems that the relationship is "lost"(don't know how to put this) with the whereHas.

@hfalucas Yes, withPivot is the way. Show me the query and the result - I can't guess what you mean by lost here :)

@JarekTkaczyk The query is the one you posted above :)

I'm just trying to also add the fields from the pivot table.

@hfalucas OK, then define lost. Is it working differently when you add withPivot to the relation or..?

@JarekTkaczyk The query works the same with or without withPivot(). This means it returns the products nested under the respective category.

Like this:

- cat1
    - prod1
    - prod2
- cat3
    - prodN          

What I mean when trying to display the items on the pivot table is:

- cat1
    - prod1
    - pivot_field_1
   (...)

which was possible never possible (it probably is...I'm just stupid) with your query :)

Hope this makes sense.

@hfalucas Oh, ok. You just don't know how to access pivot fields :)

So, when object is loaded in the context of belongsToMany relation, then it has pivot model attached:

$category->pivot->field_1

and so on.

I know that @JarekTkaczyk :)

The problem is that the relation isn't being loaded.

(No Pivot table)

I thinks it is quite similar to this: https://laracasts.com/discuss/channels/general-discussion/can-i-improve-this-query-user-album-pictures-tags

@hfalucas Do you have pivot in hidden fields maybe? Was it there before adding withPivot? I can't guess :) It just is there always, so you must have done something wrong.

@JarekTkaczyk I don't have the pivot fields in hidden fields property and the pivot table was never there from the beginning(with and without withPivot). :(

I set up a quick project with only the referred models so you can see (if you have a chance) what I'm talking about.

https://github.com/hfalucas/query-problem

(the query is in the routes file)

Hi, @hfalucas, What is the program you used to make that diagram?

@hfalucas I will take a look at this later.

Hi @Mo7sin this is the website I used to the DB diagram: http://laravelsd.com/ .

@JarekTkaczyk thank you so much. You rock :D

@hfalucas Well my friend, you have Category hasMany Product, so there is no pivot involved. It will be there for products-users relation. All is good there!

And btw when you push something like this again, include seeder, so anyone can just run artisan migrate --seed and have the same data as you do.

Exactly @JarekTkaczyk :D between categories and products there is no pivot table, what I am trying to access is the products-users relation, in order tho achieve something like this (if it is even possible)

 - category_1 
       - product_1 
               - product_user_some_field // field from the users_product table 
 (and so on)

All of that for a given user of-course. So for a given user I be able to list all his products(with the product_user table fields included) grouped by the categories.

Probably my whole schema is wrong and it is not possible to do what I want, if so do you have any suggestions? Also hope i explained my self clear, don't really wanna sound annoying you have been super patient so far :)

PS: I really should had included the seed files, my bad :(

@hfalucas Well, then you need to load users as well:

with(['products' => function ($q) {...}, 'products.users']) ...

Then you will have pivot fields available on each user: user->pivot->field_1.

THANK YOU @JarekTkaczyk . Perfect :)