2
\$\begingroup\$

I have got two tables articles and tags in the database. My goal was to retrieve all fields from articles table and one category field from tags table with Eloquent. I couldn't do this so instead I came up with the following method in my controller.

public function showCategory($category){
    $data = DB::select('select articles.*,
                                tags.category
                                from articles
                                join tags on articles.id = tags.article_id 
                                where lang = ? AND tags.category = ?', ['ru', $category]);

    return $data;
}

In my Article and Tag models a have those relationships, respectively:

Article.php

  public function tags(){
    return $this->hasMany('\App\Tag');
}

Tag.php

public function articles(){
    return $this->hasMany('App\Article');
}

This implementation works for me, but I am still curious how can I achieve the same result with Eloquent and eager load tag's category.

\$\endgroup\$
1
\$\begingroup\$

The relation hasMany required a correspondent field in the target table.

I guess in your case you want manyToMany relationship, you are currently using oneToMany.

Be careful about the grammar, an Article belongsToMany Tags, not an Article hasMany Tags.

After re-coding your relationships with belongsToMany method you can simply bind your articles and tags within a pivot table that contains both the article_id and tag_id.

As an example I would recommend having a look at a package in GitHub. You should also have a look at this for raw information about Many to Many relationships in Laravel.

When it comes to the query you send via Laravel's Query\Builder;

You can start trying it with an Eloquent\Builder

Article::join('tags', 'articles.id', '=', 'tags.article_id')
    ->where('articles.lang', '=', 'ru')
    ->where('tags.category', '=', $category)
    ->get([
        'articles.*', 
        'tags.category'
    ]);

If the select at the end confuses you, you can also use the select method on the Query\Builder to specify the columns you want.

Article::select([
        'articles.*', 
        'tags.category'
    ]) 
    // You could also give each element of this array 
    // as a parameter to the `select` method
    // It would look like this:
    //      select('articles.*', 'tags.category')
    ->join('tags', 'articles.id', '=', 'tags.article_id')
    ->where('articles.lang', '=', 'ru')
    ->where('tags.category', '=', $category)
    ->get();
\$\endgroup\$
  • 1
    \$\begingroup\$ Thank you for pointing out wrong relationships in Article model. I didn't notice something was not right there. Thanks a lot. \$\endgroup\$ – Alexandr Jun 24 '16 at 4:57
  • \$\begingroup\$ I love it when people try to learn Laravel. :) Keep up buddy! \$\endgroup\$ – Ozan Kurt Jun 24 '16 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.