I've seen some threads that are potentially similar (e.g. http://laravel.io/forum/05-01-2014-eloquent-query), but so far I haven't figured this one out.
From the docs (http://laravel.com/docs/eloquent#querying-relations), I can see what I want to do: "limit my results based on the existence of a relationship". But the "::has" query does not do what I want -- the example there does not seem to deal with the possibility that the relationship is defined via a join table (i.e. a pivot table).
In my case I have 3 tables:
- organizations
- users
- organizations_users
So that one user can belong to multiple organizations. In one of my app's forms, I need to show a list of organizations that a user belongs to.
My Models
class Organization extends Eloquent {
public function users() {
return $this->hasMany('User','organizations_users','organization_id','user_id');
}
}
// ...
class User extends Eloquent implements UserInterface, RemindableInterface {
public function organizations() {
$this->hasMany('Organization','organizations_users','user_id','organization_id');
}
}
My Controller
So I thought the ::has operator would do what I wanted:
$organizations = Organization::has('users')->orderBy('name')->lists('name', 'id');
But it does not seem to understand that there's a pivot table involved here. I get an error like the following:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.organizations_users' in 'where clause'
(SQL: select `name`, `id` from `organizations` where (
select count(*) from `users` where `users`.`organizations_users` = `organizations`.`organization_id`
) >= 1 order by `name` asc)
It's looking for users.organizations_users when it needs to look for organizations_users.organization_id.
My Workaround
So my manual query is to count my own rows:
DB::select(DB::raw('SELECT `organizations`.`name`, `organizations`.`id` from `organizations`
INNER JOIN `organizations_users` ON `organizations`.`id` = `organizations_users`.`organization_id`
WHERE `organizations_users`.`user_id`='.$user_id.'
GROUP BY `organizations`.`id`'));
I feel like there's a way to do this, I'm just not structuring my relationships or queries correctly. Can anyone point me in the right direction? Thanks!
The problem is that you use wrong relation for this. Many to many is belongsToMany and hasMany works for 1 to many relationships.
So simply change relations to (and on User model you forgot return):
public function users() {
return $this->belongsToMany('User', 'organizations_users');
}
public function organizations() {
return $this->belongsToMany('Organization', 'organizations_users');
}
Then a list of organizations for a user:
$user->organizations; // collection of Organization models
// and of course
$organization->users; // the other way around
Now, has is supposed to fetch only those models that have the related ones:
$users = User::has('organizations')->get(); // returns users who have any organization related
$users = User::has('organizations', '>', 2)->get(); // returns users who have more than 2 organizations related
$users = User::whereHas('organizations', function ($q) {
$q->where('name', 'like', '%someName%');
})->get();
// returns users who have organizations matching the inner where clause (name column in this case)
The latter example could also query the pivot table itself if you need that
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast