我有一个博客应用程序,其中有三个表格,我想查找任何搜索查询。
三个表格: posts
, categories
, tags
。
模型:
文章 (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');
}
}
标签:
class Tag extends Model
{
public function posts(){
return $this->belongsToMany('App\Post','post_tag','tag_id','post_id');
}
}
分类:
class Category extends Model
{
protected $table='categories';
public function posts(){
return $this->hasMany('App\Post');
}
}
表结构 |
---|
/*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
我所要寻找的是针对任何搜索查询,例如输入“doughnuts”,应该匹配“文章标题”、“类别名称”或“标签名称”,如果发生匹配,则应在搜索结果中显示相关的文章。 我尝试过的方法
$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);
但这似乎给我带来了重复的结果,并且效果不佳。我想知道更好的解决方案是什么?
groupBy('id')
吗? - GayangroupBy('id')
吗?看看我的答案是否符合你的需求。 - Gayan