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

• Alexandr: Thank you for pointing out wrong relationships in Article model. I didn't notice something was not right there. Thanks a lot.
• Ozan Kurt: I love it when people try to learn Laravel. :) Keep up buddy!