Laravel Eloquent to Join Table and Count Related

Laravel Eloquent to join table and count related

This is more of a MySQL join+group+select trick which includes following steps.

1. Join your relation table(use join if you want to exclude rows with RoomsCount=0, else use leftJoin)
2. Use groupBy by primaryKey to avoid duplicates of the join.
3. Select count of joined table

    $this->model->leftJoin('Rooms', 'Properties.ID', '=', 'Rooms.Property')
      ->selectRaw('Properties.*, count(Rooms.RoomID) as RoomsCount')
      ->groupBy('Properties.ID')
      ->get();

How to count two related tables using Laravel Query Builder?

Try this:

use Illuminate\Support\Facades\DB;

DB::table('table2')
    ->join('table1', 'table2.rif', '=', 'table1.rif')
    ->select(DB::raw('count(*) as count'), 'table1.category as category')
    ->groupBy('category')
    ->get();

If you want to count only a specific category:

DB::table('table2')
    ->join('table1', 'table2.rif', '=', 'table1.rif')
    ->where('table1.category', 'Silver')
    ->count()

See Laravel docs for more info.

Join tables and see the count of categories in each category name in laravel 8

If You are willing to use Eloquent, the answer would be much simple.

Post would be a model for posts table

Category would be a model for categories table

there would be one-to-one or any other relationship as per
requirements between models

Let's say there is a one-to-many relationship between Category and Post model
This can be achieved by adding a function in your Category model

public function posts()
{
    return $this->hasMany(Post::class);
}

then you can use something like...

Category::withCount('posts')->get()

Laravel Joins to retrieve total count

UPDATE 2

Total no of users started training (1 or more episodes under a training).
Something like this would work in mysql I guess, do please try.

$userCount = User::select(DB::raw('count(distinct(user_id))'))
 ->join('completions', 'completions.user_id', 'users.id')
 ->join('episodes', 'completions.episode_id', 'episodes.id')
 ->whereColumn('trainings.id', 'episodes.training_id')
 ->toSql(); //get the raw sql to use below

$trainings = Training::with('episodes')
    ->select('trainings.*', 
      DB::raw( "($userCount) as user_count"  ) //insert above sql. take note of the parentheses, it's important since it's actually enclosing the entire sql statement from above
     )
    ->get();

Laravel actually uses a similar method behind the withCount method.
What's happening here:

• ->select('trainings.*', this is a normal select that's asking to select all columns of the trainings table

• DB::raw( "($userCount) as user_count" ) the $userCount will give us the SQL to count the unique user_ids that appears in completions table. The final result of this query will be just the count, so we are attaching that subquery as a column called user_count into the select list.

  //in case the subquery needs a basic explanation.

• $userCount query: the important condition here is the condition in whereColumn. This is what makes this query attach to every row in the trainings table being selected. So what this query is basically translating to: for each row in trainings table, get all related episode and count the unique user ids. And this gives the no of unique users who has at least one episode in the completions list for a particular training.

---

UPDATE

For every training, I'd like to show how many users has watched at least 1 episode.

Training::with(['episodes' => function($query){
  return $query->withCount('users');
}])->get();

Now Episode relation will have an attribute named 'users_count', or along those words, that indicates the number of users who have watched the episode.

---

BEFORE UPDATE

And my completions table has user_id, episode_id because it is a many
to many relation to show which episode has been watched by users.

Therefore, it is safe to assume that your User model has a belongsToMany relationship method that links User and Episodes through the completions table. Something similar to this:

public function completedEpisodes(){
 return $this->belongsToMany(Episode::class, 'completions_table');
}

I'd like to show how many users has watched at least 1 episode.

Now, you can count all users with at least one completedEpisode like so:

User::has('completedEpisode')->count(); //use get() instead of count if you want to retrieve the results

How Get Count in Join Query in Laravel

If I understood your question correctly, you are trying to find the count of user activities grouped by a brand's name. Also you want the top 5 records ordered by the ones with the most user activities.

So, the following should work:

$top_donwload_list = DB::table('user_activities')
    ->selectRaw("brands.brand_name, COUNT('user_activities.*') as user_activitiesCount")
    ->join('brands', 'brands.id', '=', 'user_activities.brand_id')
    ->groupBy('brands.brand_name')
    ->orderBy('user_activitiesCount', 'desc')
    ->take(5)
    ->get();

How to get Laravel Eloquent Count and Case in Join

I already know what is cause. First, I can't identify the error because I am using dataTable so the error is from dataTable. Then I tried to fetch the result of my query using view and the error says SQLSTATE[42000]: Syntax error or access violation: 1055

This the solution I deed. Kindly see below:

In config\database.php

Find mysql array and change the 'strict' => true to 'strict' => false

Thank you for help guys.

---

Related Topics