Relationships, get products where relationship table belongs to the user

Published 2 months ago by nc1991

So I have 2 tables products, and purchases, I am trying to get the purchases that belong to the owner of the product. Anyone can purchases, but only the user owns the product

I have my tables like so:

    Schema::create('products', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id');
        $table->string('title');
        $table->text('description');
        $table->string('category');
        $table->string('auction_type');
        $table->string('allow_offers');
        $table->string('condition');
        $table->integer('quantity');
        $table->integer('auctionlength');
        $table->decimal('buyitnow_price');
        $table->decimal('auction_price');
        $table->string('shipping');
        $table->decimal('shipping_cost');
        $table->integer('shipping_time');
        $table->string('international_shipping');
        $table->string('location');
        $table->string('allow_returns');
        $table->date('enddate');
        $table->timestamps();
        $table->softDeletes();           
    });

Purchases table

    Schema::create('purchases', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id');
        $table->integer('product_id');
        $table->decimal('amount');
        $table->string('name');
        $table->string('address');
        $table->string('city');
        $table->string('state');
        $table->string('country');
        $table->string('postal');
        $table->string('email');
        $table->string('status');
        $table->timestamps();
        $table->softDeletes();
    });

I have tried the following but this still seems to get rows

dd(Product::where('user_id', Auth::user()->id)->with('purchases')->get());

purchases model

public function product()
{
    return $this->belongsTo('App\Models\Market\Product');
}

product model

public function purchases()
{
    return $this->hasMany('App\Models\Market\Purchases');    
}

My two models are Purchases and Products, without raw query, how can I get the purcahse of products that belongs to user_id

tisuchi
tisuchi
2 months ago (264,695 XP)

Here is the way-

Product::where('user_id', Auth::user()->id)->with('product.purchases')

Now it will load the user's purchases that belongs to product.

Its called Nested Eager Loading. Check in following link-

Ref: https://laravel.com/docs/5.5/eloquent-relationships#eager-loading

nc1991

I seem to be getting an error doing that

Call to undefined relationship [product] on model [App\Models\Market\Product].
tisuchi
tisuchi
2 months ago (264,695 XP)

So, here is the logic-

In you user model, put relationships like this way-

In User model-

  • product()
  • purchases()

In Product model-

  • purchases()
  • user()

In Purchase model-

  • product()
  • user()

Now, you are able to access like this way-

Product::where('user_id', Auth::user()->id)->with('product.purchases')->get()

It will load all the products with all purchases belongs that belongs to authentication user.

SO, MAKE SURE THAT YOU HAVE RELATIONSHIPS IN PROPER WAY.

nc1991

So I am still getting that error

Call to undefined relationship [product] on model 
[App\Models\Market\Product].

I have the relationships like so:

user.php

public function product()
{
    return $this->hasMany('App\Models\Market\Product');    
}

public function purchases()
{
    return $this->hasMany('App\Models\Market\Product');    
}

product.php

public function user()
{
    return $this->belongsTo('App\Models\Account\User');
}

public function purchases()
{
    return $this->belongsTo('App\Models\Market\Purchases');    
}

purchases.php

public function product()
{
    return $this->belongsTo('App\Models\Market\Product');    
}

public function user()
{
    return $this->belongsTo('App\Models\Account\User');    
}  

Any idea?

tisuchi
tisuchi
2 months ago (264,695 XP)

Can you dd() this query and show result?

dd(Product::with('product.purchases')->where('user_id', Auth::user()->id)->get());
nc1991
Illuminate \ Database \ Eloquent \ RelationNotFoundException
Call to undefined relationship [product] on model [App\Models\Market\Product].

https://i.imgur.com/MilClrX.png

Also, the builder in case you need it: https://i.imgur.com/wiz3Hhc.png

tisuchi
tisuchi
2 months ago (264,695 XP)

I think we were doing in wrong way.

May be this will work for you.


Product::with('user.purchases')->where('user_id', Auth::user()->id)->get();

It should return all products with user purchases.

nc1991

This gets the products that I own, but are not purchased.

https://i.imgur.com/mSwp9H4.png

https://i.imgur.com/zFjUVLY.png

tisuchi
tisuchi
2 months ago (264,695 XP)

Correct. Now if you need to print what you have purchased, here is the way-

$allProducts = Product::with('user.purchases')->where('user_id', Auth::user()->id)->get();

//following line will print all of your purchased table data
dd($allProducts->user.purchases)
nc1991

I'm not trying to get the items I've purchased :(, because I could just use Auth::user()->purchases, I'm just trying to get all rows from purchases where the product_id, belongs to a product I own

for example

select * from purchases
left join products
    on products.id = purchases.product_id
where products.user_id = loggedin user's id
tisuchi
tisuchi
2 months ago (264,695 XP)

Its way simpler than that.

Try-

$allProducts = Product::with('purchases')->where('user_id', Auth::user()->id)->get();

//following line will print all of your purchased table data
dd($allProducts->purchases)
nc1991

Property [purchases] does not exist on this collection instance.

ftrillo

Assuming you have the id of the owner:

$purchases = Purchase::whereHas('product.user', function ($query) {
    $query->where('products.user_id', Auth::id());
})->get();

I'm not sure I understood what you wanted. This should get you the purchases of any product owned by the authenticated user.

You can use whereHas to query stuff in related models, check the docs for more details.

If the dot notation in the whereHas doesn't work you might need to nest a whereHas inside of another.

nc1991

I manage to get it to work via the hasthroughmany

/**
 * The products the user has sold
 */
public function sold()
{
    return $this->hasManyThrough('App\Models\Market\Purchases', 'App\Models\Market\Product');
}

Please sign in or create an account to participate in this conversation.