Level 52
Something like that :
$activities = Activity::join('resource', 'resource.activity_id', '=', 'activities.id')
->select(DB::raw('activities.*, COUNT(*) as count'))
->latest('count')
->groupBy('activities.id')
->get();
Feb 12, 2015
9
Level 17
Many to Many relationship Query.
I'm trying to figure out how I can do a query within a query. (if that even makes sense) I want to grab all the activities. For each activity I want to get the count of users that did the activity. Then I want to order all the activities in DESC order based on the amount of users that did each activity. I'm basically making a "Popular Activities Page" where I show all activities starting with the activity done by the most users.
I have 3 main tables for this
users
| id | name | password | email | created_at |
activities
| id | title | description | created_at |
resource. This is a table I'm using for posts which shows which user did which activity. (Users can show what activities they did, and attach media and locations to the post)
| id | user_id | activity_id | media_id | location_id | created_at |
Here are my models for each table
User Model
class User extends Eloquent {
protected $table = 'users';
/**
* get the activities associated with the given user
* @return mixed
*/
public function activities()
{
return $this->belongsToMany('Acme\Activities\Activity', 'resource', 'activity_id');
}
public function posts(){
return $this->hasMany('Acme\Resource\Resource');
}
public function media()
{
return $this->hasMany('Acme\Media\Media');
}
public function locations()
{
return $this->hasMany('Acme\Locations\Location');
}
}
Activity Model
class Activity extends Eloquent {
protected $table = 'activities';
public function posts(){
return $this->hasMany('Acme\Resource\Resource', 'resource_id');
}
/**
* get the users associated with the given activity card
* @return mixed
*/
public function users()
{
return $this->belongsToMany('Acme\Users\User', 'resource', 'user_id');
}
}
Resource Model
class Resource extends Eloquent {
protected $table = 'resource';
public function user()
{
return $this->belongsTo('Acme\Users\User', 'user_id');
}
public function activities()
{
return $this->belongsTo('Acme\Activities\Activity', 'activity_id');
}
public function media()
{
return $this->hasMany('Acme\Media\Media', 'media_id');
}
public function locations()
{
return $this->belongsTo('Acme\Locations\Location', 'location_id');
}
}
I know I can get all activities using Activity::get()
I can get a user count for a specific activity using
User::whereHas('resource', function($q) use ($activity_id){
$q->where('activity_id', $activity_id);
})->get()->count();
but I don't know how I can put all of this together in order to get all Activities sorted by user count, starting with the activity with the highest user count.
How would I make this query using eloquent?
Thanks in advance!
Level 17
@bestmomo This is close but this return the count for every time an activity was done. I only want the total amount of users that did an activity, not how many times they did it.
My resource table looks like this
| id | user_id | activity_id | media_id | location_id | created_at |
| 1 | 1 | 3 | x | x | x |
| 2 | 1 | 3 | x | x | x |
| 3 | 1 | 4 | x | x | x |
| 4 | 3 | 3 | x | x | x |
| 5 | 2 | 4 | x | x | x |
| 6 | 1 | 4 | x | x | x |
| 7 | 1 | 3 | x | x | x |
so this shows that:
user_id=1 did activity_id=3 3 times, and activity_id=4 once
user_id=3 did activity_id=3 once,
user_id=2 did activity_id=4 once.
so the user count for each activity should be
activity_id=3 is 2 users
activity_id=4 is 2 users
but the query gives me
activity_id=3 is 4
activity_id=4 is 3
This is where I'm having a hard time.
Level 4
@brentxscholl resource is your pivot table you should be able to achieve that like so:
$user->activities->count();
Level 17
@xroot This will return how many activities a user did. I'm looking to get all activities and sort them by the amount of users that did each activity. Starting with the activity that has the most users.
Level 54
You may need to do it in two queries.. first get all the activity IDs, then count the number of distinct user id's with that activity id. Not sure if you can pull that off in a single query.
Level 17
@willvincent this sound like it could work. So grab all activities and then with a foreach loop go through each and find the amount of distinct users? How would I catch the results, then sort the activities base on their count?
Level 54
@brentxscholl unfortunately I can't help you with the code, but I played around with some queries and was only able to get it to work by basically doing this:
select distinct(activity_id) from resources;
then for each activity id:
select count(distinct(user_id)) from resources where activity_id = $activity_id
Sorting may have to happen in php. :\
Seems like there's gotta be a better solution that can be accomplished by SQL though.
1 like
Level 17
This does the trick!
return Acme\Resource\Resource::select(DB::raw('*, COUNT(distinct(user_id)) as user_count'))->groupBy('activity_id')->orderBy('user_count', 'desc')->get();
1 like
Level 52
I didn't understand that a user could do many times the same activity. So my previous query becomes :
$activities = Activity::join('resource', 'resource.activity_id', '=', 'activities.id')
->select(DB::raw('activities.*, COUNT(distinct(resource.activity_id)) as count'))
->latest('count')
->groupBy('activities.id')
->get();
So you get all activities in good order, with count of distinct users.
1 like
Please or to participate in this conversation.