Fetching model by translated field

[jonasva]

I have a model Article with an ArticleTranslation model to store its translations. ArticleTranslation has a field called 'slug' which I would like to use to navigate to my blog articles. Something like example.com/blog/slug-to-an-article-in-english. (same approach as somewhat every blog site out there right). Now when someone hits that URL, I would have to fetch an article object based on the translated slug.

So I have this in my controller for that:

public function showArticle($slug) 
{
    $article = Article::where('slug', '=', $slug)->first();
    if(!is_object($article)) App::abort(404);

    return \View::make('blog::blog.show')->with('article', $article);
}

When I run that query I get the following error:
"Unknown column 'slug' in 'where clause' "

The problem is that by using the Model::where only allows me to search into the Model itself but not by fields the translated model. I know I could run a query on the ArticleTranslation model, but that would only return an ArticleTranslation object and any fields in the Article model itself would be inaccessible (unless I use additional queries). Would it be possible to add a method that allows you to query on the joined table of a Model + it's ModelTranslation?

In my example that would result in a query like:
select * from articles inner join article_translations on articles.id = article_translations.article_id where slug = 'my-slug' and locale = 'current locale';

[sdebacker]

I use whereHas:

$query->whereHas(
    'translations',
    function ($query) use ($slug) {
        $query->where('slug', $slug);
        $query->where('locale', App::getLocale());
    }
);

You can put this code in an eloquent query scope

[jonasva]

Thanks @sdebacker , thats works well, but the query it outputs is a bit strange:

select * from `articles` where (select count(*) from `article_translations` where `article_translations`.`article_id` = `articles`.`id` and `slug` = 'my-slug' and `locale` = 'en') >= 1

It does a bit of a useless subquery imo, I tried to replace it by $query->with(), but that gave some unwanted effects.

[sdebacker]

You can also use join if you prefer. Especially for big lists, the query will be faster.

[jonasva]

I tried using the join method, but I'm having problems properly accessing attributes from the returned object afterwards.

I was adding a new method in the Translatable model to test:

public static function findByTranslatedField($column, $operator, $value) 
{
    $instance = new static;

    $model = $instance->getNewTranslationInstance(App::getLocale());


    $result = $instance::join($model->getTableName(), $instance->getTableName().'.id', '=', $instance->getRelationKey())->where($column, $operator, $value)->get();

    echo '<pre>';
    var_dump($result);
    echo '</pre>';
    exit;
}

It returns an object of type 'Illuminate\Database\Eloquent\Collection'. In the case I'm testing with, I get 1 result back so I just access it with $result[0]. When I vardump that, I can get an attribute + its correct result by accessing $result[0]->getAttributes()['myfieldname']. But if I just do $result[0]->myfieldname, it returns NULL. Any idea about that?

[sdebacker]

Why not rewrite your first attempt like this:

public function showArticle($slug) 
{
    $article = Article::select('articles.id', 'article_translations.slug')
        ->join('article_translations', 'articles.id', '=', 'article_translations.article_id')
        ->where('slug', $slug)
        ->firstOrFail();

    return \View::make('blog::blog.show')->with('article', $article);
}

[jonasva]

I wrote that method in the Translatable model so I don't have to remake this kind of query for different translated models.

I adapted my code a bit to work like your solution with the Model::select structure and it solves the problem, thanks :).

[sdebacker]

Nice

[ghost]

Hello @sdebacker and @jonasva

I'm new to laravel and tried something similar

ArticleController

public function show(ArticleCategory $articlecategory, $slug)
    {
        $locale = Lang::locale();
        $article = Article::select('articles.id', 'article_translations.slug')
        ->join('article_translations', 'articles.id', '=', 'article_translations.article_id')
        ->where('slug', $slug)->withTranslations($locale)
        ->firstOrFail();
        $article->addPageView();
        return $article;
    }

This only shows

{
  "id": 2,
  "slug": "yoga-asana",
  "page_views": 16,
  "translations": [
  {
    "id": 3,
    "article_id": 2,
    "title": "Yoga Aasana",
    "slug": "yoga-asana",
    "subtitle": "Yoga Asanas for a healthy morning",
    "content": "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor 
    incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation 
    ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in 
    voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non 
    proident, sunt in culpa qui officia deserunt mollit anim id est laborum.",
    "locale": "en",
    "created_at": "2018-03-01 00:00:00",
    "updated_at": "2018-03-01 00:00:00"
    }
 ]
}

I have more fields on the Articles table like category_id, image, created_by, updated_by

They do not show up. When i use ->with('category') it shows null

How to solve this?

[sdebacker]

@shrikanth003, you can add the required columns in Article::select('articles.id', 'article_translations.slug'). Please use https://laracasts.com/discuss or https://laravel.io/forum for this kind of questions.

[ghost]

@sdebacker Thanks for replying. I have used join tables instead of select and it displays the complete table fields.

Ah ok, i was using this package, so i thought i should ask here. Here on i should laracasts