Mysql Full Text query with eloquent relationship

I have defined many to many relationship between my Post and Category models. I need to perform FULL TEXT search on column title and body in posts table. The search may also have supplied ids of category when submitted from search form.

How can i perform this search? My current codes

//Post.php
public function categories()
{
     return $this->belongsToMany('App\Category', 'post_categories');
}


//Category.php
public function posts()
{
      return $this->belongsToMany('App\Post', 'post_categories');
}

// my current query to search 
$posts = Post::whereRaw( "MATCH(title,body) AGAINST(? IN BOOLEAN MODE)",  [$q]  )->get();

How can i modify the current query to have the relationship ? I mean how I can add where clause to contain only those row which belongs to supplied categories ids?

Any other suggestion related to improving my logic or code?

Thank you in advance

Dry7

8 years ago

Level 36

try

$posts = Post::whereRaw( "MATCH(title,body) AGAINST(? IN BOOLEAN MODE)",  [$q]  )->with('categories')->get();

in where use post_categories.id

sannjay

8 years ago

Level 1

I think it first load all the matching rows regardless of category ids ?

sannjay

8 years ago

Level 1

There should be something like,

//First fetch all post_id from `pivot` table
$postids = DB::table('post_categories')->whereIn('category_id', [1, 2, 3])->pluck('post_id')->get();

//and then above query like this
$posts = Post::whereRaw( "MATCH(title,body) AGAINST(? IN BOOLEAN MODE)",  [$q]  )->whereIn('id', $postids )->get();

I am not sure if it is correct query or there is better way to do it using Eloquent

Please or to participate in this conversation.