I made a search function that searches name and content fields of all posts in the database. Some of these posts are in relation (parents and children). I have a problem as if my search returns a hit in a child post, I would like to return only parent data (to minimise the load).
My Post model:
public function children()
{
return $this->hasMany('App\Post','parent_post_id','id');
}
public function children_count()
{
return $this->children()
->selectRaw('parent_post_id, count(*) as answers')
->groupBy('parent_post_id');
}
public function parents()
{
return $this->hasOne('App\Post','id','paren_post_id');
}
public function author()
{
return $this->hasOne('App\User', 'id', 'user_id');
}
My search query:
$posts = Post::with('children')
->with('children_count')
->with('author')
->where('name','like','%'.$search_term.'%')
->orWhere('content','like','%'.$search_term.'%')
->orderBy('created_at','desc')
->groupBy('id')
->paginate(10);
Example: Post#1 has two children, Post#2 and Post#3. My search finds data in Post#3, now I would like it to return data of Post#1.
EDIT: I see that I need to explain more what exactly would I like to achieve.
Posts table structure:
I am searching name and content fields of each post (author (user_id) is irrelevant at this point). When search finds a hit in child post (has parent_post_id set to parent's id), I only want to get parent's data (id, slug, name, content etc.)
I was able to achieve this with:
$posts = DB::table('posts as a')
->leftJoin('posts as b', 'b.parent_post_id', '=', 'a.id')
->where('a.name','like','%'.$search_term.'%')
->orWhere('a.content','like','%'.$search_term.'%')
->orWhere('b.name','like','%'.$search_term.'%')
->orWhere('b.content','like','%'.$search_term.'%')
->select('a.*')->orderBy('a.created_at','desc')->paginate(10)
But was then unable to successfully count all children that returned parent has. This might also give some ideas how to wrap this Laravel way
Can anyone point me in the right direction?
class Post extends Model{
public function children()
{
return $this->hasMany(Post::class, 'parent_post_id', 'id');
}
public function children_count()
{
return $this->children()
->selectRaw('parent_post_id, count(*) as answers')
->groupBy('parent_post_id');
}
public function parents()
{
return $this->hasOne(Post::class, 'id', 'parent_post_id');
}
public function author()
{
return $this->hasOne(User::class, 'id', 'user_id');
}
}
$posts = Post::with(['children_count', 'author', 'children'])
->whereHas('author', function ($query) use ($search_term) {
$query->where('name', 'like', '%' . $search_term . '%');
})
->orWhere('content','like','%' . $search_term . '%')
->orderBy('created_at', 'desc')
->paginate(10);