Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have read the docs and I'm not clear on it. i followed search essentials and would like to use unions to search multiple columns. I have this right now in my post model:
public function scopeSearch($query, $search)
{
return $query->where('title', 'LIKE', "%$search%");
}
this is in my BlogController
public function index(Request $request)
{
$query = $request->get('q');
$posts = $query
? Post::search($query)->paginate(15)
: Post::paginate(15);
$tag = $request->get('tag');
$data = $this->dispatch(new BlogIndexData($tag));
$layout = $tag ? Tag::layout($tag) : 'blog.layouts.index';
return view($layout, $data)->withPosts($posts);
}
This is what I want to do but don't know to make it work
SELECT title FROM posts
WHERE title like "%$query%"
UNION
select subtitle from posts
WHERE subtitle like "%$query%"
union
select title from tags
WHERE title like "%$query%"
union
select subtitle from tags
WHERE subtitle like "%$query%"
any help would be beyond great
I'm a small site both elasticsearch and angolia would be impractical. If anybody knows how to finish what @jeffway started in his search essentials tutorials to make this work would be magnificent thanks
using the query builder http://laravel.com/docs/5.1/queries#unions you might create something like this:
$subtitle = DB::table('posts')
->where('subtitle', 'LIKE', '%$query%');
$tagtitle = DB::table('tags')
->where('title', 'LIKE', '%$query%');
$tagsubtitle = DB::table('tags')
->where('subtitle', 'LIKE', '%$query%');
$search = DB::table('posts')
->where('title', 'LIKE', '%$query%');
->union($subtitle)
->union($tagtitle)
->union($tagsubtitle)
->get();
@frezno that doesn't work for me. I tried it. I get an error about it being a string or something.
do it like this
public function index(Request $request)
{
$query = $request->get('q');
$subtitle = DB::('posts')
->where('subtitle', 'LIKE', '%$query%');
$tagtitle = DB::('tags')
->where('title', 'LIKE', '%$query%');
$tagsubtitle = DB::('tags')
->where('subtitle', 'LIKE', '%$query%');
$search = DB::('posts')
->where('title', 'LIKE', '%$query%');
->union($subtitle)
->union($tagtitle)
->union($tagsubtitle)
->get();
$posts = $search
? Post::search($query)->paginate(15)
: Post::paginate(15);
$tag = $request->get('tag');
$data = $this->dispatch(new BlogIndexData($tag));
$layout = $tag ? Tag::layout($tag) : 'blog.layouts.index';
return view($layout, $data)->withPosts($posts);
}
How would I set it up I'm a bit confused
what being a string? do you know the exact error message?
It could be the case that it doesn't like the '%$query%' the you might try something like so: '%'. $query .'%'
If all tries are failing you always can use a raw select. Then it will work for sure.
I've read that raw queries have security risks because of injections
@frezno heres error SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select * from posts where title LIKE %dolo%) union (select * from posts where subtitle LIKE %dolo%) union (select * from tags where title LIKE %dolo%) union (select * from tags where subtitle LIKE %dolo%))
@Raygun, sure. But with raw queries, you can use query params.
About your error: you are using *, which means 'all columns'. Unions require the same number of columns.
@Henri85 thanks for the response. How do I prevent that from selecting everything? I thought I was only selecting one column. and are there videos on here for raw queries?
@raygun, such as this:
select title from posts where title like '%dolo%'.
The query builder uses PDO parameter binding. I think it's better to use. Raw queries are 1) not object oriented 2) more difficult to maintain 3) dangerous (own security required)
the following code gives me this error
Array to string conversion in Post.php line 22
this is my BlogController.php
public function index(Request $request)
{
$query = $request->get('q');
$subtitle = DB::table('posts')
->select( 'subtitle')
->where('subtitle', 'LIKE', '%'. $query .'%');
$tagtitle = DB::table('tags')
->select('subtitle')
->where('subtitle','LIKE', '%'. $query .'%');
$tagsubtitle = DB::table('tags')
->select('title')
->where('title','LIKE', '%'. $query .'%');
$search = DB::table('posts')
->select('title')
->where('title','LIKE', '%'. $query .'%')
->union($subtitle)
->union($tagtitle)
->union($tagsubtitle)
->get();
$posts = $search
? Post::search($search)
: Post::paginate(15);
$tag = $request->get('tag');
$data = $this->dispatch(new BlogIndexData($tag));
$layout = $tag ? Tag::layout($tag) : 'blog.layouts.index';
return view($layout, $data)->withPosts($posts);
}
in my Post.php
public function scopeSearch($query, $search)
{
return $query->where('title', 'LIKE', "%{$search}%") <<<<line 22>>
->paginate(15);
}
causes Array to string conversion in Post.php line 22
good to see the query is working now, although you shouldn't need the selects.
That's a working example (localhost:8000/search)
// routes.php
Route::get('search', function() {
$query = 'sub';
$subtitle = \DB::table('posts')
->where('subtitle', 'LIKE', '%'.$query.'%');
$tagtitle = \DB::table('tags')
->where('title', 'LIKE', '%'.$query.'%');
$tagsubtitle = \DB::table('tags')
->where('subtitle', 'LIKE', '%'.$query.'%');
$search = \DB::table('posts')
->where('title', 'LIKE', '%'.$query.'%')
->union($subtitle)
->union($tagtitle)
->union($tagsubtitle)
->get();
dd($search);
});
ok, back to you code:
The $search-Argument in your scopeSearch methode is this the search-query or what are you passing to it?
There's really no need to start multiple discussions on the same issue. You're not going to do yourself any favours with the people that are trying to help you.
@frezno Actually this is what's working for me but now my posts order is in reverse
my BlogController.php
{ public function index(Request $request) {
$query = $request->get('q');
$posts = $query
? Post::search($query)
: Post::paginate(15);
$tag = $request->get('tag');
$data = $this->dispatch(new BlogIndexData($tag));
$layout = $tag ? Tag::layout($tag) : 'blog.layouts.index';
return view($layout, $data)->withPosts($posts);
}
my Post.php
public function scopeSearch($query, $search) {
return $query
->orderBy('created_at', 'desc')
->where('title', 'LIKE', "%{$search}%")
->orWhere( 'subtitle', 'LIKE', '%'. $search .'%')
->orWhere('content_raw', 'LIKE', "%{$search}%")
->paginate(15)
;
}
I'm halfway there. I've added some of the columns I wanted. They are all from the same table though. But now it seems my post are showing the oldest first now
put the orderBy after the last orWhere
I am able to search any column in one table at a time using the following code
<?php
namespace App;
use App\Services\Markdowner;
use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
class Post extends Model
{
protected $dates = ['published_at'];
protected $fillable = [
'title', 'subtitle', 'content_raw', 'page_image', 'meta_description',
'layout', 'is_draft', 'published_at',
];
public function scopeSearch($query, $search)
{
return $query
->where('title', 'LIKE', "%{$search}%")
->orWhere( 'subtitle', 'LIKE', "%{$search}%")
->orWhere('content_raw', 'LIKE', "%{$search}%")
->orderBy('created_at', 'desc')
->join($first)
->paginate(15);
}
}
but when I try to add a table using the following
<?php
namespace App;
use App\Services\Markdowner;
use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
class Post extends Model
{
protected $dates = ['published_at'];
protected $fillable = [
'title', 'subtitle', 'content_raw', 'page_image', 'meta_description',
'layout', 'is_draft', 'published_at',
];
public function scopeSearch($query, $search)
{
$first = Tag::where('title', 'LIKE', "%{$search}%")
->orWhere( 'subtitle', 'LIKE', "%{$search}%")
->get();
return $query
->where('title', 'LIKE', "%{$search}%")
->orWhere( 'subtitle', 'LIKE', "%{$search}%")
->orWhere('content_raw', 'LIKE', "%{$search}%")
->orderBy('created_at', 'desc')
->union($first)
->paginate(15);
}
}
I get this error
Argument 1 passed to Illuminate\Database\Query\Builder::mergeBindings() must be an instance of Illuminate\Database\Query\Builder, instance of Illuminate\Database\Eloquent\Collection given, called in /home/vagrant/Code/search/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php on line 1236 and defined
What adjustments do I need to make for this to search the other table?
ok, again: you cannot query an union (or whatever) AFTER ORDER BY
but even if it would work, what sense does it make to order just a fraction of the result???
@raygun Why these unions... ?
nobody knows, @JarekTkaczyk , but he
I'm new to programming @JarekTkaczyk @frezno (2 mos) so I there is a lot I don't know. The unions, how i understood them to be used, are for listing results from the other table into a single list
@raygun OK, I see. So let me put it this way: You care about showing together different results matching the search criteria to your user (say in autocomplete dropdown box or something similar).
BUT you don't care about fetching these results in single query, which is cumbersome in MySQL (SQL) with either joins or unions. Better get the results from different tables separately, and just merge them the way you want them to be shown to the user.
That said, your code could be cut to just these lines (using the package I linked in my first answer):
$posts = Post::search('dolo', ['title', 'subtitle'])->skip($postsOffset)->take($postsLimit)->get();
$tags = Tag::search('dolo', ['title', 'subtitle'])->skip($tagsOffset)->take($tagsLimit)->get();
// then return both and present them in your view
There's a chance you want just posts that have either title/subtitle matching %dolo% or related tags with title/subtitle matching %dolo%, so you could simply do:
$posts = Post::search('dolo', ['title', 'subtitle', 'tags.title', 'tags.subtitle'])->paginate(15);
This way you can fetch paginated posts matching search criteria pretty easily.
@JarekTkaczyk I think where saying the same thing. Say I type coldplay into the search field. it would return title and subtitle from my POSTS table, TAGS table, and my VIDEO table (which I plan on adding) and list them on my page. There should only be a single instance of each, no duplicates. I'm going to check out the package you recommended and give it a shot. As I said I've been learning this for about 2 months and anything without a video is a bit confusing
@JarekTkaczyk oh snap just realized you made that. @JarekTkaczyk I don't see any directions on how to download or require it or anything
@raygun https://github.com/jarektkaczyk/eloquence/wiki/Installation
Just import it in the composer.json like in the wiki above, then use Eloquence trait in the model and optionally define default searchable fields:
class Post extends Model {
use Eloquence; // Sofa\Eloquence\Eloquence
// optionally
protected $searchableColumns = ['title', 'subtitle'];
}
@JarekTkaczyk tried using it it's confusing. the trait is not being recognized I don't get it. looks great tho. but it's not for me
@raygun What do you find confusing there? Show me your model and I'll tell you what's wrong.
@JarekTkaczyk Where I have it marked "RIGHT HERE" below, is the code that interacts with each other
this is my post.php <?php
namespace App;
use App\Services\Markdowner;
use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
use Sofa\Eloquence\Eloquence;
class Post extends Model
{
use Eloquence;
protected $dates = ['published_at'];
protected $fillable = [
'title', 'subtitle', 'content_raw', 'page_image', 'meta_description',
'layout', 'is_draft', 'published_at',
];
public function scopeSearch($query, $search) <----RIGHT HERE
{
return $query
->where('title', 'LIKE', "%{$search}%")
->orWhere( 'subtitle', 'LIKE', "%{$search}%")
->orWhere('content_raw', 'LIKE', "%{$search}%")
->orderBy('created_at', 'desc')
->paginate(15);
}
/**
* The many-to-many relationship between posts and tags.
*
* @return BelongsToMany
*/
public function tags()
{
return $this->belongsToMany('App\Tag', 'post_tag_pivot');
}
/**
* Set the title attribute and automatically the slug
*
* @param string $value
*/
public function setTitleAttribute($value)
{
$this->attributes['title'] = $value;
if (! $this->exists) {
$this->setUniqueSlug($value, '');
}
}
/**
* Recursive routine to set a unique slug
*
* @param string $title
* @param mixed $extra
*/
protected function setUniqueSlug($title, $extra)
{
$slug = str_slug($title.'-'.$extra);
if (static::whereSlug($slug)->exists()) {
$this->setUniqueSlug($title, $extra + 1);
return;
}
$this->attributes['slug'] = $slug;
}
/**
* Set the HTML content automatically when the raw content is set
*
* @param string $value
*/
public function setContentRawAttribute($value)
{
$markdown = new Markdowner();
$this->attributes['content_raw'] = $value;
$this->attributes['content_html'] = $markdown->toHTML($value);
}
/**
* Sync tag relation adding new tags as needed
*
* @param array $tags
*/
public function syncTags(array $tags)
{
Tag::addNeededTags($tags);
if (count($tags)) {
$this->tags()->sync(
Tag::whereIn('tag', $tags)->lists('id')->all()
);
return;
}
$this->tags()->detach();
}
/**
* Return the date portion of published_at
*/
public function getPublishDateAttribute($value)
{
return $this->published_at->format('M-j-Y');
}
/**
* Return the time portion of published_at
*/
public function getPublishTimeAttribute($value)
{
return $this->published_at->format('g:i A');
}
/**
* Alias for content_raw
*/
public function getContentAttribute($value)
{
return $this->content_raw;
}
/**
* Return URL to post
*
* @param Tag $tag
* @return string
*/
public function url(Tag $tag = null)
{
$url = url('blog/'.$this->slug);
if ($tag) {
$url .= '?tag='.urlencode($tag->tag);
}
return $url;
}
/**
* Return array of tag links
*
* @param string $base
* @return array
*/
public function tagLinks($base = '/blog?tag=%TAG%')
{
$tags = $this->tags()->lists('tag');
$return = [];
foreach ($tags as $tag) {
$url = str_replace('%TAG%', urlencode($tag), $base);
$return[] = '<a href="'.$url.'">'.e($tag).'</a>';
}
return $return;
}
/**
* Return next post after this one or null
*
* @param Tag $tag
* @return Post
*/
public function newerPost(Tag $tag = null)
{
$query =
static::where('published_at', '>', $this->published_at)
->where('published_at', '<=', Carbon::now())
->where('is_draft', 0)
->orderBy('published_at', 'asc');
if ($tag) {
$query = $query->whereHas('tags', function ($q) use ($tag) {
$q->where('tag', '=', $tag->tag);
});
}
return $query->first();
}
/**
* Return older post before this one or null
*
* @param Tag $tag
* @return Post
*/
public function olderPost(Tag $tag = null)
{
$query =
static::where('published_at', '<', $this->published_at)
->where('is_draft', 0)
->orderBy('published_at', 'desc');
if ($tag) {
$query = $query->whereHas('tags', function ($q) use ($tag) {
$q->where('tag', '=', $tag->tag);
});
}
return $query->first();
}
}
this is my BlogController.php
<?php
namespace App\Http\Controllers;
use App\Jobs\BlogIndexData;
use App\Http\Requests;
use App\Post;
use App\Tag;
use Illuminate\Http\Request;
use App\Services\RssFeed;
use App\Services\SiteMap;
class BlogController extends Controller
{
public function index(Request $request)
{
$query = $request->get('q');
$posts = $query
? Post::search($query) <----RIGHT HERE
: Post::orderBy('published_at', 'desc')->paginate(15);
$tag = $request->get('tag');
$data = $this->dispatch(new BlogIndexData($tag));
$layout = $tag ? Tag::layout($tag) : 'blog.layouts.index';
return view($layout, $data)->withPosts($posts);
}
public function showPost($slug, Request $request)
{
$post = Post::with('tags')->whereSlug($slug)->firstOrFail();
$tag = $request->get('tag');
if ($tag) {
$tag = Tag::whereTag($tag)->firstOrFail();
}
return view($post->layout, compact('post', 'tag', 'slug'));
}
public function rss(RssFeed $feed)
{
$rss = $feed->getRSS();
return response($rss)
->header('Content-type', 'application/rss+xml');
}
public function siteMap(SiteMap $siteMap)
{
$map = $siteMap->getSiteMap();
return response($map)
->header('Content-type', 'text/xml');
}
}
@JarekTkaczyk where do I go from here? do I delete the scopeSearch() in my Post.php that corresponds with my Post::search() in my BlogController.php. Both are marked by "RIGHT HERE" This is where I'm stuck at
@raygun format the code and show me what you tried with Eloquence not being recognized.
my BlogController.php
public function index(Request $request)
{
$query = $request->get('q');
$posts = $query
? Post::search('foo bar', ['name', 'posts.title', 'posts.subtitle'])->get()->paginate(15)
: Post::orderBy('published_at', 'desc')->paginate(15);
$tag = $request->get('tag');
$data = $this->dispatch(new BlogIndexData($tag));
$layout = $tag ? Tag::layout($tag) : 'blog.layouts.index';
return view($layout, $data)->withPosts($posts);
}
in my post.php I deleted the scopeSearch()
I get this error
Call to a member function make() on null
@raygun It seems you didn't do pt 4 from the installation guide:
Add Sofa\Eloquence\ServiceProvider to your config/app.php providers array - it will register the Mutator as a service in the IoC Container as well as factories on the Builder class.
@JarekTkaczyk ok just did it. my BlogController.php
public function index(Request $request)
{
$query = $request->get('q');
$posts = $query
? Post::search('$query', ['name', 'posts.title', 'posts.subtitle'])->get()->paginate(15)
: Post::orderBy('published_at', 'desc')->paginate(15);
$tag = $request->get('tag');
$data = $this->dispatch(new BlogIndexData($tag));
$layout = $tag ? Tag::layout($tag) : 'blog.layouts.index';
return view($layout, $data)->withPosts($posts);
}
my post.php
<?php
namespace App;
use App\Services\Markdowner;
use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
use Sofa\Eloquence\Eloquence;
class Post extends Model
{
use Eloquence;
protected $dates = ['published_at'];
protected $fillable = [
'title', 'subtitle', 'content_raw', 'page_image', 'meta_description',
'layout', 'is_draft', 'published_at',
];
/**
* The many-to-many relationship between posts and tags.
*
* @return BelongsToMany
*/
public function tags()
{
return $this->belongsToMany('App\Tag', 'post_tag_pivot');
}
/**
* Set the title attribute and automatically the slug
*
* @param string $value
*/
public function setTitleAttribute($value)
{
$this->attributes['title'] = $value;
if (! $this->exists) {
$this->setUniqueSlug($value, '');
}
}
/**
* Recursive routine to set a unique slug
*
* @param string $title
* @param mixed $extra
*/
protected function setUniqueSlug($title, $extra)
{
$slug = str_slug($title.'-'.$extra);
if (static::whereSlug($slug)->exists()) {
$this->setUniqueSlug($title, $extra + 1);
return;
}
$this->attributes['slug'] = $slug;
}
/**
* Set the HTML content automatically when the raw content is set
*
* @param string $value
*/
public function setContentRawAttribute($value)
{
$markdown = new Markdowner();
$this->attributes['content_raw'] = $value;
$this->attributes['content_html'] = $markdown->toHTML($value);
}
/**
* Sync tag relation adding new tags as needed
*
* @param array $tags
*/
public function syncTags(array $tags)
{
Tag::addNeededTags($tags);
if (count($tags)) {
$this->tags()->sync(
Tag::whereIn('tag', $tags)->lists('id')->all()
);
return;
}
$this->tags()->detach();
}
/**
* Return the date portion of published_at
*/
public function getPublishDateAttribute($value)
{
return $this->published_at->format('M-j-Y');
}
/**
* Return the time portion of published_at
*/
public function getPublishTimeAttribute($value)
{
return $this->published_at->format('g:i A');
}
/**
* Alias for content_raw
*/
public function getContentAttribute($value)
{
return $this->content_raw;
}
/**
* Return URL to post
*
* @param Tag $tag
* @return string
*/
public function url(Tag $tag = null)
{
$url = url('blog/'.$this->slug);
if ($tag) {
$url .= '?tag='.urlencode($tag->tag);
}
return $url;
}
/**
* Return array of tag links
*
* @param string $base
* @return array
*/
public function tagLinks($base = '/blog?tag=%TAG%')
{
$tags = $this->tags()->lists('tag');
$return = [];
foreach ($tags as $tag) {
$url = str_replace('%TAG%', urlencode($tag), $base);
$return[] = '<a href="'.$url.'">'.e($tag).'</a>';
}
return $return;
}
/**
* Return next post after this one or null
*
* @param Tag $tag
* @return Post
*/
public function newerPost(Tag $tag = null)
{
$query =
static::where('published_at', '>', $this->published_at)
->where('published_at', '<=', Carbon::now())
->where('is_draft', 0)
->orderBy('published_at', 'asc');
if ($tag) {
$query = $query->whereHas('tags', function ($q) use ($tag) {
$q->where('tag', '=', $tag->tag);
});
}
return $query->first();
}
/**
* Return older post before this one or null
*
* @param Tag $tag
* @return Post
*/
public function olderPost(Tag $tag = null)
{
$query =
static::where('published_at', '<', $this->published_at)
->where('is_draft', 0)
->orderBy('published_at', 'desc');
if ($tag) {
$query = $query->whereHas('tags', function ($q) use ($tag) {
$q->where('tag', '=', $tag->tag);
});
}
return $query->first();
}
}
my error
BadMethodCallException in Builder.php line 1994:
Call to undefined method Illuminate\Database\Query\Builder::posts()
@raygun This line is wrong:
? Post::search('$query', ['name', 'posts.title', 'posts.subtitle'])->get()->paginate(15)
change it to:
// given you want to search posts.name, posts.title, posts.subtitle columns:
? Post::search($query, ['name', 'title', 'subtitle'])->paginate(15)
// given related tags.title, tags.subtitle and local posts.name columns:
? Post::search($query, ['name', 'tags.title', 'tags.subtitle'])->paginate(15)
Please or to participate in this conversation.