multiple joins laravel php

Question

I have a blog app, it has three tables that I want to look into for any search query.

Three tables: posts ,categories,tags.

Models:

Post:

class Post extends Model
{
    public function category(){
        return $this->belongsTo('App\Category');
    }

    public function tags(){
        return $this->belongsToMany('App\Tag','post_tag','post_id','tag_id');
    }

    public function users(){
        return $this->belongsTo('App\User','author_id');
    }
}

Tag:

class Tag extends Model
{
    public function posts(){
        return $this->belongsToMany('App\Post','post_tag','tag_id','post_id');
    }
}

Category:

class Category extends Model
{
    protected $table='categories';


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

Table Structure

/*Table: posts*/
----------------

/*Column Information*/
----------------------

Field        Type              Collation        Null    Key     Default  Extra           Privileges                       Comment  
-----------  ----------------  ---------------  ------  ------  -------  --------------  -------------------------------  ---------
id           int(10) unsigned  (NULL)           NO      PRI     (NULL)   auto_increment  select,insert,update,references           
created_at   timestamp         (NULL)           YES             (NULL)                   select,insert,update,references           
updated_at   timestamp         (NULL)           YES             (NULL)                   select,insert,update,references           
title        varchar(255)      utf8_unicode_ci  NO              (NULL)                   select,insert,update,references           
body         text              utf8_unicode_ci  NO              (NULL)                   select,insert,update,references           
slug         varchar(255)      utf8_unicode_ci  NO      UNI     (NULL)                   select,insert,update,references           
category_id  int(10) unsigned  (NULL)           YES             (NULL)                   select,insert,update,references           
image_path   varchar(255)      utf8_unicode_ci  YES             (NULL)                   select,insert,update,references           
author_id    int(10) unsigned  (NULL)           NO      MUL     (NULL)                   select,insert,update,references 



/*Table: tags*/
---------------

/*Column Information*/
----------------------

Field       Type              Collation        Null    Key     Default  Extra           Privileges                       Comment  
----------  ----------------  ---------------  ------  ------  -------  --------------  -------------------------------  ---------
id          int(10) unsigned  (NULL)           NO      PRI     (NULL)   auto_increment  select,insert,update,references           
name        varchar(255)      utf8_unicode_ci  NO              (NULL)                   select,insert,update,references           
created_at  timestamp         (NULL)           YES             (NULL)                   select,insert,update,references           
updated_at  timestamp         (NULL)           YES             (NULL)                   select,insert,update,references   

/*Table: categories*/
---------------------

/*Column Information*/
----------------------

Field       Type              Collation        Null    Key     Default  Extra           Privileges                       Comment  
----------  ----------------  ---------------  ------  ------  -------  --------------  -------------------------------  ---------
id          int(10) unsigned  (NULL)           NO      PRI     (NULL)   auto_increment  select,insert,update,references           
name        varchar(255)      utf8_unicode_ci  NO              (NULL)                   select,insert,update,references           
created_at  timestamp         (NULL)           YES             (NULL)                   select,insert,update,references           
updated_at  timestamp         (NULL)           YES             (NULL)                   select,insert,update,references 

/*Table: post_tag*/
-------------------

/*Column Information*/
----------------------

Field    Type              Collation  Null    Key     Default  Extra           Privileges                       Comment  
-------  ----------------  ---------  ------  ------  -------  --------------  -------------------------------  ---------
id       int(10) unsigned  (NULL)     NO      PRI     (NULL)   auto_increment  select,insert,update,references           
post_id  int(10) unsigned  (NULL)     NO      MUL     (NULL)                   select,insert,update,references           
tag_id   int(10) unsigned  (NULL)     NO      MUL     (NULL)                   select,insert,update,references 

What I was trying to look for is for any search query, suppose if he enters doughnuts it should be matched for the post title or category name or tag name, if any of the match occurs post related to it, should be shown in the search results.

What I tried

$post = DB::table('posts')
  ->join('categories', 'posts.category_id', 'categories.id')
  ->join('blog_users', 'blog_users.id', 'posts.author_id')
  ->join('post_tag', 'posts.id', 'post_tag.post_id')
  ->join('tags', 'tags.id', 'post_tag.tag_id')
  ->select(
     'posts.*', 'blog_users.name',
     'post_tag.tag_id', 'post_tag.post_id',
     'tags.name', 'categories.name')
  ->where(function($query) use ($search) {
    $query->where('posts.title', 'LIKE', $search)
          ->orWhere('categories.name', 'LIKE', $search)
          ->orWhere('tags.name', 'LIKE', $search);
  })
  ->paginate(5);

But this seems to give me duplicated results and did not work well.

I would like to know a better solution for this problem?

Answer 1

When I find myself using multiple joins in a query (specially in SELECT queries) what I usually do is implement a database view to encapsulate the logic behind the query.

Create migration like this

DB::statement("DROP VIEW IF EXISTS view_post");
DB::statement(" 
CREATE VIEW view_post
AS
SELECT 
    'posts.*', 
    'blog_users.name AS blog_users_name',
    'post_tag.tag_id', 
    'post_tag.post_id',
    'tags.name AS tags_name', 
    'categories.name AS categories_name'
    FROM posts
        INNER JOIN categories
        ON (posts.category_id = categories.id)
        INNER JOIN blog_users
        ON (blog_users.id = posts.author_id)
        INNER JOIN post_tag
        ON (posts.id = post_tag.post_id)
        INNER JOIN tags
        ON (tags.id = post_tag.tag_id)
 ");

Then make a model like this

class PostView extends Post { // <- To inherit properties of POST to here
    protected $table = 'view_post';
}

Then you can simplify your query like

$post = PostView::where(function ($query) use ($search) {
            return $query->orWhere('title', 'LIKE', $search)
                ->orWhere('tags_name', 'LIKE', $search)
                ->orWhere('categories_name', 'LIKE', $search);
        })
            ->groupBy('id')
            ->paginate(5);

What is important to keep in mind is that it's all depend on the situation. To me for my station this was a much cleaner approach rather than muddy up my query. I think you could adopt this approach too. Thanks.

Note: you may need to modify the view and the query further to represent what you are trying to achieve.