I have a problem in creating a query that contains the count of elements in another table by grouping the elements by a column.

I try to explain the problem with this example:

Table post

Table views

Post model

class Post extends Model{

  protected $table = 'post';

  protected $primaryKey = 'id';

  protected $fillable = [
    'title'
  ];

  public function related_views(){

    return $this->hasMany(Views::class, 'post_id', 'id');

  }

}

Views model

class Views extends Model{

  protected $table = 'views';

  protected $primaryKey = 'id';

  protected $fillable = [
    'post_id',
    'view_id'
  ];

}

Query

$query = Post::with([
                'related_views' => function ($query) {
                    return $query->groupBy('view_id');
                }
            ])
            ->withCount('related_views')
            ->get();

The query is executed correctly. In the multidimensional array that is returned there are, in addition to the columns in the main table, these 2 values: related_views and related_views_count.

The related_views_count variable counts all the elements while the related_views variable shows only the elements by grouping.

Is it possible to get the count of the elements in the related_views variable instead of getting the array of elements?

I hope I have stated my problem correctly.

CodePudding user response：

You can benefit from laravel's related models counting function and define your conditions as a closure function.

$query = Post::withCount([
                'related_views as related_views_grouped_count' => function ($query) {
                    return $query->groupBy('view_id');
                }
            ])
        ->get();

CodePudding user response：

With this query I solved the problem!

Post::query()
  ->withCount('related_views', function($query) {
          $query->select(DB::raw('count(distinct(view_id))'));
  })
->get();