mysql - 我如何使用 laravel 中的联合来创建查询搜索

Question

我可以使用以下代码一次搜索一个表中的任何列

<?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);
    }
}

但是当我尝试使用以下方法添加表格时

<?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);
    }
}

我收到这个错误

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

编辑我的 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)
                : 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');
        }
    }

编辑她的 BlogIndexData.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)
                : 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');
        }
    }

我需要做哪些调整才能搜索其他表？

Answer 1

尝试让 PDO 实例直接执行您的查询:

    $PDO=DB::connection('mysql')->getPdo();
    $stmt=$PDO->prepare("
         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
    ");
    $stmt->bindParam(':query', $query);


    $stmt->execute();

    $result = $stmt->fetchAll();

我在 laravel 4 上运行的一个项目中有一个类似的查询，您可能需要修改参数。另外，为 laravel 原始查询做一个谷歌。

祝你好运!