Database query with Eloquent

Question

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.

Answer 1

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();