multiple table search laravel

Question

i want to search all contents inside a question post, which includes question title, question description, answers, question comments and answers comments.
i wanted to make an optimized search query for this case.

output should have 'slug' in common. if the keyword match title, description,answers,question comments and answer comments, the response should be somewhat like this ..

  #items: array:3 [▼
    0 => {#2517 ▼
      +"slug": "question-slug-here"
      +"search_type": "a-comment"
      +"content": "asdfg afgnistan jjdknsfr"
    }
    1 => {#2524 ▼
      +"slug": "question-slug-here"
      +"search_type": "answer"
      +"content": "<p>fgnbk gsnpobgklkfdmlkfdjb mdfjlblfkdj hbdjsdl,g hkldjhbfdk hbopdtgh dgjnhgfm</p>"
    }
    2 => {#2518 ▼
      +"slug": "question-slug-here"
      +"search_type": "a-comment"
      +"content": "fgn again testing"
    }
    3 => {#2519 ▼
      +"slug": "question-slug-here"
      +"search_type": "title"
      +"content": "question title here which has searched keyword"
    }
    4 => {#2520 ▼
      +"slug": "question-slug-here"
      +"search_type": "q-comment"
      +"content": "matched question comment here"
    }
    5 => {#2521 ▼
      +"slug": "question-slug-here"
      +"search_type": "description"
      +"content": "matched question description here"
    }
    ....
    ....
  ]

One title and description record per question, but it should have many answer records,question and answers comments records for a single question.

I've the following codes

$questions = DB::table('questions as q')
                ->select('slug',
                    DB::raw("(CASE 
                        WHEN title LIKE '%".$search_text."%' THEN 'title'
                        WHEN description LIKE '%".$search_text."%' THEN 'description'
                        WHEN answer LIKE '%".$search_text."%' THEN 'answer'
                        WHEN ac.comment LIKE '%".$search_text."%' THEN 'a-comment'
                        WHEN qc.comment LIKE '%".$search_text."%' THEN 'q-comment'
                        ELSE 'None'
                        END) AS search_type,

                        (CASE 
                        WHEN title LIKE '%".$search_text."%' THEN title
                        WHEN description LIKE '%".$search_text."%' THEN description
                        WHEN answer LIKE '%".$search_text."%' THEN answer
                        WHEN ac.comment LIKE '%".$search_text."%' THEN ac.comment
                        WHEN qc.comment LIKE '%".$search_text."%' THEN qc.comment
                        ELSE 'None'
                        END) AS content
                        "))
              ->leftjoin('question_answer as qa','qa.question_id','=','q.id')

              ->leftjoin('question_answer_comments as qc', function($join) {
                  $join->on('qc.commentable_id','=','q.id')
                      ->where('qc.commentable_type', '=', 'App\Models\Question');
              })
              ->leftjoin('question_answer_comments as ac', function($join1) {
                  $join1->on('ac.commentable_id','=','qa.id')
                      ->where('ac.commentable_type', '=', 'App\Models\QuestionAnswer');
              })
              
              
              ->where('q.is_active','Y')
              ->where(function ($query) use ($search_text) {
                  $query->orwhere('title','LIKE','%'.$search_text.'%')
                  ->orwhere('description','LIKE','%'.$search_text.'%')
                  ->orwhere('answer','LIKE','%'.$search_text.'%')
                  ->orwhere('ac.comment','LIKE','%'.$search_text.'%')
                  ->orwhere('qc.comment','LIKE','%'.$search_text.'%');
              })
              // ->groupBy('q.id','search_type')
              ;

dd($questions->get());

Current problem

1. If the keyword matches title, it'll fetch some same records multiple time irrespective of other search results (it has matched answers and comments, but it didn't fetch those. I guess the error has to do with select DB Raw query)
2. If keyword doesn't match title, then it'll search for all others except question comments. If i reverse the order of question and answer comment in DB Raw of select(question comment first, answer comment second), then it'll show only question comments , and it may sometimes show multiple record for the same question comment data like this.

How do i make use of the groupBy attribute to eliminate duplicate fields.
Can i use addSelect based on conditions instead of DB raw with cases?

I'll appreciate the help and guidance. Thanks :)

Answer 1

Your question model should have relations setup as following:

In question model Question.php

public function answers()
{
    return $this->hasMany(Answer::class);
}

public function comments()
{
    return $this->hasMany(Comment::class);
}

and search scope as

public function scopeSearch($query, string $term)
{
    collect(explode(' ', $term))->filter()->each(function ($term) use ($query) {
        $term = '%' . $term . '%';
        $query->where('title', 'like', $term)
            ->orWhere('description', 'like', $term)
            ->orWhereHas('comments', function ($query) use ($term) {
                $query->where('body', 'like', $term); // comments body field
            })->orWhereHas('answers', function ($query) use ($term) {
                $query->where('body', 'like', $term) // answers body field
                ->orWhereHas('comments', function ($query) use ($term) {
                    $query->where('body', 'like', $term); // answer comments body field
                });
            });
    });
}

public static function scopeIsActive($query)
{
    return $query->where('is_active', 'Y');
}

In answer model Answer.php

public function comments()
{
    return $this->hasMany(Comment::class);
}

Now in you can search in controller as

$questions = Question::search($search_text)->isActive()->get();

If you are using polymorphic relation then please use orWhereHasMorph

I hope, you have understand above. If you have any question, please let me know.