How to use Laravel/Scout with database driver on multilangual model,

I have model Post, which is searchable and translatable, I want to apply search on translations too.

For translation I use Laravel Voyager's default translation, which I believe is same as https://docs.astrotomic.info/laravel-translatable/package/interface,

Search works perfectly on default language, but when I try to apply Scout's suggested solution on my case, I get this error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous

This is the query I use:

select * 
from `posts`
inner join `translations` on `posts`.`id` = `translations`.`foreign_key` 
                          and `translations`.`column_name` = 'body' 
                          and `translations`.`table_name` = 'posts' 
where (`posts`.`title` like %დოლორეს% 
       or `posts`.`body` like %დოლორეს% 
       or `translations`.`value` like %დოლორეს%) 
order by `id` desc

This is my model

class Post extends \TCG\Voyager\Models\Post
{
    use Searchable;
    use Translatable;
    protected $translatable = ['title', 'body', 'excerpt'];


    public function toSearchableArray(): array
    {
        return [
            'title' => $this->title,
            'body' => $this->body,
            'translations.value' => ' '

        ];
    }
}

and this is Controller

 $posti=Post::search(\request('search'))->query(function ($builder) {
            $builder->join('translations',function ($join) {
                $join->on('posts.id', '=', 'translations.foreign_key')
                ->where('translations.column_name', '=', DB::raw("'body'"))
                    ->where('translations.table_name','=',DB::raw("'posts'"));
            });
//                ->where('table_name','=','posts');
        })->get();

any help would be greatly appreciated

I tried to use this solution click here

So, the problem was that while joining the Posts and Translations tables, the Post's ID was replaced by the Translation's ID due to having the same names.

The solution was to select only necessary columns from the Translations table and obviously not to select the ID column.

Here is the final working code:

        $post = Post::search(\request('search'))->query(function ($builder) {
        $builder->select([
            'posts.*',
            'translations.table_name',
            'translations.column_name',
            'translations.foreign_key',
            'translations.value',
            'translations.locale',
        ])->where('posts.status','PUBLISHED')
            ->join('translations', function ($join) {
            $join->on('posts.id', '=', 'translations.foreign_key');
        })
            ->where('translations.table_name', '=', 'posts')->where(function($query) {
            $query->where('translations.column_name','body')
                ->orWhere('translations.column_name','title');
        });
    })->orderBy($column ?? 'id' , $order ?? 'asc')->get();

        $test = $post->unique('id');

This last line $test = $post->unique('id'); was necessary to avoid duplicated results when the searchable word was found in 'body' and also in 'title'