Mysql full text search with Laravel Translatable?

Question

Up until now, I have always used a full text search like this:

public function posts($search)
{
    $searchString = $this->prepareString($search);
    $posts = Post::select('*')->selectRaw('MATCH(name) AGAINST(? IN NATURAL LANGUAGE MODE) AS relevance', [$searchString])->whereRaw('MATCH(name) AGAINST(? IN NATURAL LANGUAGE MODE)', [$searchString])->orderBy('relevance', 'desc')->get();

    return $posts;
}

And this works perfectly, however now I am using this package Laravel Translatable. And the name field is not part of the Post, but it is part of the PostTranslation. How can I use the above query, but for the translation? Here are my migrations:

Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();
        });

Schema::create('post_translations', function (Blueprint $table) {
            $table->increments('id');

            $table->string('name');
            $table->text('body');

            $table->char('locale', 2)->index();

            $table->integer('post_id')->unsigned();
            $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');

            $table->unique(['id', 'locale']);
        });

Answer 1

You can use DB Facade something like this

public function posts($search)
{
    $searchString = $this->prepareString($search);
    $posts = DB::table('post_translations')->select('*')->selectRaw('MATCH(name) AGAINST(? IN NATURAL LANGUAGE MODE) AS relevance', [$searchString])->whereRaw('MATCH(name) AGAINST(? IN NATURAL LANGUAGE MODE)', [$searchString])->orderBy('relevance', 'desc')->get();

    return $posts;
}