How to get summary of related table?

Hello, In laravel 7 app I have 2 related tables ForumThread:

        Schema::create('forum_threads', function (Blueprint $table) {
            $table->increments('id')->unsigned();
            ...
            $table->integer('forum_id')->unsigned();
            $table->foreign('forum_id')->references('id')->on('forums')->onUpdate('RESTRICT')->onDelete('CASCADE');

        });

and ForumPost:

        Schema::create('forum_posts', function (Blueprint $table) {
            $table->bigIncrements('id')->unsigned();
            ...
            $table->integer('forum_thread_id')->unsigned();
            $table->foreign('forum_thread_id')->references('id')->on('forum_threads')->onUpdate('RESTRICT')->onDelete('CASCADE');
            ...
            $table->integer('viewed')->unsigned()->default(0);

        });

The last have field viewed and I need to calculate sum of viewed field of related forum_posts rows. I model app/ForumThread.php I addded forumPostsViewedSum method :

    public function forumPosts()
    {
        return $this->hasMany('App\ForumPost');
    }

    public function forumPostsViewedSum()
    {
        return $this->hasMany('App\ForumPost')
                    ->selectRaw('SUM(viewed) as forum_posts_viewed_sum')
                    ->groupBy('forum_thread_id');
//                    ->groupBy('forum_id');
    }

and in controller :

        $forumThreads = ForumThread
            ::with('latestForumPost.creator')
            ->withCount('forumPosts')
            ->with('creator')
            ->getByForumId($forum_id)
            ->orderBy($order_by, $order_direction)
            ->offset($limit_start)
            ->take($forum_threads_per_page)
            ->with('forumPostsViewedSum')
            ->get();
        \Log::info(varDump($forumThreads, '  get_forum_threads  -3 $forumThreads::'));

Looking at traced sql I see what I need

   SELECT * 
    FROM `vfrm_forum_posts` 
    WHERE `vfrm_forum_posts`.`forum_thread_id` in (2, 3) 
    ORDER BY `created_at` desc 
...  
  
  
   SELECT SUM(viewed)     AS forum_posts_viewed_sum 
    FROM `vfrm_forum_posts` 
    WHERE `vfrm_forum_posts`.`forum_thread_id` in (2, 3) 
    GROUP BY `forum_thread_id`

Last request returns data I expect, but in returned data and in traced data(Log::info) I got empty array for forum_posts_viewed_sum field .

Why invalid data for forum_posts_viewed_sum and how can I do it?

Thanks!

rodrigo.pedra

5 years ago

Best Answer

Level 56

On your forumPostsViewedSum you need to also select the forum_thread_id column, so Laravel can reference it back when matching the results:

public function forumPostsViewedSum()
{
    return $this->hasMany(ForumPost::class)
        ->select('forum_thread_id') // <<<<< ADDED
        ->selectRaw('SUM(viewed) as forum_posts_viewed_sum')
        ->groupBy('forum_thread_id');
}

But if you want similar functionality as ->withCount(...) you can try doing this instead:

$forumThreads = ForumThread::query()
    ->with('latestForumPost.creator')
    ->withCount('forumPosts')
    ->with('creator')
    ->getByForumId($forum_id)
    ->orderBy($order_by, $order_direction)
    ->offset($limit_start)
    ->take($forum_threads_per_page)
    // ->with('forumPostsViewedSum') // <<<<< REMOVED
    ->addSelect([
        'forum_posts_viewed_sum' => ForumPost::query()
            ->selectRaw('SUM(viewed)')
            ->whereColumn('forum_thread_id', 'forum_threads.id'),
    ])
    ->withCasts(['forum_posts_viewed_sum' => 'integer'])
    ->get();

Hope it helps.

1 like

mstdmstd

5 years ago

Level 8

Thanks! It helped. The only thing : in debugging I see that data have rather complicated structure :

            [forum_posts_viewed_sum] => Array
                (
                    [0] => Array
                        (
                            [forum_thread_id] => 18
                            [forum_posts_viewed_sum] => 90
                        )

                )

and on client(I use vuejs) I have to use line like:

              {{ forumThread.forum_posts_viewed_sum[0].forum_posts_viewed_sum }}

If there is a way to shorten it?

rodrigo.pedra

5 years ago

Level 56

Did you try the second option?

On the second option I add a select subquery mostly to avoid this nested data structure. The select sub-query is how the ->withCount(...) already works.

Also, if you prefer to use the first option and keep the relation, you might want to change the ->hasMany(...) on the forumPostsViewedSum relation to a ->hasOne(...):

    public function forumPostsViewedSum()
    {
        return $this->hasOne(ForumPost::class)
            ->select('forum_thread_id') // <<<<< ADDED
            ->selectRaw('SUM(viewed) as forum_posts_viewed_sum')
            ->groupBy('forum_thread_id')
            ->withDefault(['forum_posts_viewed_sum' => 0]);
    }

So at least you would avoid the array around the result.

As it is grouped by there will always be only one record by forum thread.

Also the ->withDefault(...) will ensure that even forum threads without posts yet get a record.

1 like

rodrigo.pedra

5 years ago

Level 56

Another change you can do to shorten it further is changing the aggregate alias to something shorter, for example:

public function forumPostsViewedSum()
{
    return $this->hasOne(ForumPost::class)
        ->select('forum_thread_id')
        ->selectRaw('SUM(viewed) as value')
        ->groupBy('forum_thread_id')
        ->withDefault(['value' => 0]);
}

Then in your Vue component you would call it:

{{ forumThread.forum_posts_viewed_sum.value }}

1 like

Please or to participate in this conversation.