Level 9
Checkout using a hasManyThrough relationship
Nov 25, 2014
9
Level 15
Need Help Converting raw SQL into Laravel Many to Many
Apologies ... I'm old school. I learned raw sql and have little trouble with it. BUT, we're learning Laravel right ? Can I get this Laravel Many to Many relationship to work - nope. All I get is an empty array.
So here is my SQL, which works fine, gives me all those customers who have a product with an order status of "1". I also provide my attempt to recreate this is Laravel. There are 3 tables: "customer", "product" and the join table is "transactions" . ( guess I could change that to customer_product if needed)
Could some kind person tell me where I am going wrong and why my Laravel code produces an empty array? All this code is trying to do is find just 1 customer / product relationship. I have not even gotten as far finding a customer with a status yet. I just can't see it any more.
Many Thanks ! :o)
SQL
SELECT c.customer_id, c.co_name, t.status, p.name
From customers as c, transactions as t, products as p
WHERE t.status = '1'
AND c.customer_id = t.customer_id
AND p.product_code = t.product_id
Controller:
<?php
use src\Customer;
class OrderController extends BaseController {
public function index()
{
$segment = ucfirst(Request::segment(1));
$pageTitle = ucfirst(Request::segment(0));
$customers = Customer::find(1)->products;
return View::make ('orders/new_orders', ['customers'=>$customers, 'segment'=>$segment, 'pageTitle'=>$pageTitle]);
}
}
Model
<?php
namespace src;
use Eloquent;
class Customer extends Eloquent {
/**
* The database table used by the model.
*
* @var string
*/
protected $table = 'customers';
public $timestamps=true;
/**
* The attributes excluded from the model's JSON form.
*
* @var array
*/
protected $hidden = array('password', 'remember_token');
protected $primaryKey = 'customer_id';
public function products() {
return $this->belongsToMany('src\Product', 'transactions', 'customer_id', 'product_id' );
}
}
Level 52
Relation seems correct. Do you declare the good primary key ("product_code") in Product model ?
Level 19
Do you actually have a customer with the id of 1?
$customers = Customer::find(1)->products;
dd($customers); // what does this output?
Also, is this correct:
return $this->belongsToMany('src\Product', 'transactions', 'customer_id', 'product_id' );
// have you tried
return $this->belongsToMany('Product', 'transactions', 'customer_id', 'product_id' );
Level 15
Progress ! But I'm not there quite yet.
@bestmomo: The docs suggest that your $primaryKey needs to be the row id, but I changed the protected $primaryKey = 'product_code' and now I get the 2 products that the fictitious customer id 1, has in the join table "transactions"
@sitesense : dd($customers); gives me the products the customer, no. 1 has purchased, but nothing more.
Ok - as this is a ManytoMany relationship what I want is a report of all the customers from the customer table plus together with all the products they have purchased where there is a status of "1".
The join table, "transactions" has a column of customer_id, product_id, status.
My code does not yet deliver that. Indeed Jeffrey's lesson ( @kreitje I have watched it several times) on the topic does not deliver what I want. Jeffrey's lesson would deliver the products or the customers but not both at the same time.
Many thanks to all who have weighed in. I sense that we are almost there.
Any ideas ?
Level 1
On the subject of constraining relations via extra fields in your pivot table.
If I understand what you want correctly, you will need to add ->withPivot('status') to your relation. This allows you to work with the extra pivot field when building a query of the form products()->where(...)
Even more appropriate would probably be ->wherePivot('status', '1') which allows you to automatically constrain on the pivot field.
So products() ends up along the lines of:
public function products() {
return $this->belongsToMany('src\Product', 'transactions', 'customer_id', 'product_id' )->withPivot('status')->wherePivot('status', '1');
}
Using Eloquent pivots/relations can get a little tricky across multiple tables if you want data from several tables as they're not implemented with joins.
Jeffrey's lesson would deliver the products or the customers but not both at the same time.
In situations like these I often resort to joins as they can be more efficient and easier. Remember you can always 'kick down' to fluent in the context of your models to do joins and reflect SQL thinking. For example, you can:
Customer::query()
->select([...])
->join('table', 'table.field', '=', 'field')
->where(...)
->get([optional subset of columns]);
http://laravel.com/docs/4.2/queries
Lastly seeing the queries laravel generates can be quite handy. There's a few ways to do this, here's one; eg. in routes.php:
Event::listen('illuminate.query', function($query)
{
var_dump($query);
});
Level 15
@qgates Thank you for your reply. I like how you suggest using Query Builder. To me, as an SQL user, it feels far more intuitive. Frankly, I find Eloquent introduces a raft of unnecessary additional complexity and learning. I do not appreciate how it is sold as easier to use than SQL. However, as a student of Laravel, I feel obliged to learn it and use it for fear I am missing out on something important, but so far I just find it slow and unnecessarily complex.
Question: If I abandon Eloquent and go forward with Query Builder, what am I loosing ? Database independence I suppose. Having DB records presented as objects. Anything else ?
Level 1
@vincej see my reply to your other thread.
I don't agree that eloquent is sold as easier than SQL. Naturally, tutorials on eloquent will focus on situations where eloquent is a good fit; and when it is, the resulting code is super-simple. Eloquent has particular features and lends itself to certain designs. For example it's approach to relationships, with pivots and eager-loading, can be very efficient in certain programming situations, and can overall outperform complex joins when the programmer repeatedly needs to iterate across several sets of results on an underlying dataset. There are of course other situations where SQL approaches are better; it's all about options.
To your question, all you really lose is the option to learn a new approach, and the benefits of working with your data at eloquent's abstraction level. As someone new to eloquent, I'd avoid trying retrofitting eloquent to an already existing design or schema, especially if you're comforable with SQL. Get familiar with models and the fluent SQL methods, but don't feel compelled to use eloquent's sugar unless it seems intuitive.
In the meantime use full-fat eloquent as part of your data modelling when starting/working on new projects, so that you get to see what it has to offer you as a developer.
As an example of mix-match, check out this stackoverflow post, the reponse to which demonstrates how you can mix things up nicely to get the best of both worlds.
Level 52
I agree with @qgates. Eloquent is nice for simple situations, it is elegant and saves time. But in more complex situations query builder is the good choice and you can easily mix it with Eloquent.
I've spent much time sometimes to make Eloquent fit with a particular case while I had an easy query builder solution, just because I wanted to find the solution.
And also in specific cases raw queries (or parts) are inevitables.
You must find your own style and where you feel easy.
Level 15
Whilst I do not yet have a solution to my original problem I have just viewed two excellent lessons which hopefully should provide the answer. I will let you know when I hopefully, get it solved !
"Advanced Selections": https://laracasts.com/lessons/advanced-eloquent-selections
"Efficient SQL Queries": https://laracasts.com/lessons/efficient-sql-queries
I hope this might help someone.
Thanks for the generosity of your time !
Cheers!
Please or to participate in this conversation.