CODEWITHSUNDEEP
mysqllaravel-5eloquent
mstdmstd
mstdmstd

Reputation: 3081

How to get latest post from threads with several fields?

In my Laravel 5.8/mysql5 app I get latest post from threads like

$forumThreads= ForumThread
    ::getByForumId($forum_id)
    ->select(
        'forum_threads.*',
        \DB::raw(' ( select count('.$prefix.'forum_posts.id) from '.$prefix.'forum_posts where '.$prefix.'forum_posts.forum_thread_id = '.$prefix.'forum_threads.id ) as forum_posts_count'),
        \DB::raw(' ( select created_at from '.$prefix.'forum_posts where '.$prefix.'forum_posts.forum_thread_id = '.$prefix.'forum_threads.id  order by id desc limit 1) as latest_post_created_at')
    )
    ->orderBy($order_by, $order_direction)
    ->offset($limit_start)
    ->take($forum_threads_per_page)
    ->get();

It works ok, but I need to get several fields for latest post. If I change condition as :

\DB::raw(' ( select created_at, title, slug from '.$prefix.'forum_posts where

I got error :

Cardinality violation: 1241 Operand should contain 1 column(s)

Of there is a way ?

modified block: In app/ForumThread.php I created

  public function latestForumPost()
    {
        return $this->hasOne('App\ForumPost')->latest();
    }

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

    }

and in my control I make :

  $forumThreads= ForumThread::with('latestForumPost')->withCount('forumPosts')
                              ->getByForumId($forum_id)
                              ->orderBy($order_by, $order_direction)
                              ->offset($limit_start)
                              ->take($forum_threads_per_page)
                              ->get();

    foreach( $forumThreads as $next_key=>$nextForumThread ) {
        $nextForumThread->latest_post_created_at= $nextForumThread->latestForumPost->created_at;
    }

and that works! But when I try to use $nextForumThread->latestForumPost in my blade.php file :

<tr v-for="nextForumThread, index in forumThreads" :key="nextForumThread.id">
    nextForumThread.latestForumPost::{{ nextForumThread.latestForumPost}}

Itdoes not work. Can it be use in blade template?

Upvotes: 0

Views: 46

Answers (1)

Dark Knight
Dark Knight

Reputation: 6541

You can achieve that with a simple relationship.

// ForumThread model
public function latestPost()
{
  return $this->hasOne('Post')->latest();
}

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

Now we will use latestPost relationship to get details of latest post per category and posts to get count.

So your refactored query would be:

$forumThreads= ForumThread::with('latestPost')->withCount('posts')
    ->getByForumId($forum_id)
    ->orderBy($order_by, $order_direction)
    ->offset($limit_start)
    ->take($forum_threads_per_page)
    ->get();

You can access these data as follow:

foreach ($forumThreads as $thread) {
    echo $thread->posts_count;    -- to show posts counts per thread.
    echo $thread->latestPost->id;  -- to access any field from latestPost
}

Reference:

Counting Related Models

Tweaking Laravel Relationship

Upvotes: 1

Related Questions