How to return information from a third table?

posted 9 years ago

Hi, I am having difficultly understanding how to return information from a distant third table.

I have followed the Intermediate Task List tutorial on Laravel and learned how to establish relationships between my 3 tables:

Tables 1) Users, 2) Purchases, and 3) Products:

Users:

- id
- name

/**
 * Get all of the Purchases for the User.
 */
public function purchases()
{
	return $this->hasMany(Purchase::class);
}

Purchases:

- id
- user_id
- product_id

/**
 * Get the User that owns the Purchase.
 */
public function user()
{
	return $this->belongsTo(User::class);
}

/**
* Get the Product that owns the Purchase.
*/
public function product()
{
	return $this->belongsTo(Product::class);
}

Products:

- id
- name

/**
 * Get all of the Purchases for the Product.
 */
public function purchases()
{
	return $this->hasMany(Purchase::class);
}

Just for testing and the sake of simplicity I am retrieving data by using returns in my routing file:

// testing Eloquent returns
Route::get('/test', function () {
	return User::find(1)->purchases()->where('price', 79)->first();
});

However, given User #1, I cannot figure out how to continue retrieving data (the name property) from table #3 (Products). Is my table schema designed poorly?

Please let me know if there's any more info I should provide.

Any assistance would be greatly appreciated.

EDIT: Following an example I was able to write a query using Query Builder that returns the name property, as JSON I think. Is there a way to simplify this using Eloquent?

Route::get('/test3', function () {
	return DB::table('products AS pr')
		->join('purchases AS pu','pu.user_id','=','pr.id')
		->join('users AS u','u.id','=','pu.id')
		->where('u.id','=',1)
		->lists('name');
});

Last updated 3 years ago.

terreporter

replied 9 years ago

You don't have a relationship directly to products from users, but you should be able to nest them.

Maybe,

User::find(1)->with('purchases', 'purchases.product')->where('product.price', 79)->first();

gamzarme

replied 9 years ago

The first part works great to retrieve the product, thanks!

User::find(1)->with('purchases', 'purchases.product')

The condition, however fails, and so I will be looking further into how to query on the returned 'product' JSON in order to deduce the price of the product.

->where('product.price', 79)->first();

Based upon your mention of the lack of relationship between users and products, another thought came up. A user can have many different products, and a product can have many different users. So would it make sense to establish a many to many relationship between users and products with an intermediate pivot table?

terreporter

replied 9 years ago

Try this,

// split the with statements
// a where following a with should be part of the with
User::find(1)->with('purchases')->('purchases.product')->where('price', 79)->first();

Let me think on the relationships,

User -> Purchases -> Products
Products -> Purchases -> Users

I'm not sure about, Users -> Products, you should be able to get the products for a user through the purchases

// pull only the product relationship through the purchases
User::find(1)->('purchases.product')->where('price', 79)->first();

Last updated 9 years ago.

Moderators