Combining two different with no relationship DB table query in laravel for pagination

Question

I have two tables in my database that are not related, and I need those to be merge so I can put it on my search view but I don't have any idea, if it is possible or not.

Here is my code. The news and season tables are not related but they have similar columns which I am trying to put in one object for easy pagination. Is it possible?

$search = $request->search;
    $nresult = DB::table("news")
                ->select("news.news_id as id","news.title as title", "news.name_key as key", "news.content", "news.image", "news.thumb", DB::raw("'news' as type"))
                ->where("news.status", "=", 1)
                ->Where("news.title", "like", "%".$search."%")
                ->orWhere("news.content", "like", "%".$search."%")
                ->orWhere("news.variables", "like", "%".$search."%")
                ->orWhere("news.categories", "like", "%".$cat_id."%")
                ->orderBy("title", "asc")
                ->paginate(5);

            $sresult = DB::table("season")
                ->select("season.season_id as id","season.name as title", "season.key as key", "season.content", "season.image", "season.thumb", DB::raw("'season' as type"))
                ->where("season.status", "=", 1)
                ->Where("season.name", "like", "%".$search."%")
                ->orWhere("season.content", "like", "%".$search."%")
                ->orWhere("season.variables", "like", "%".$search."%")
                ->orderBy("title", "asc")
                ->paginate(5);

I'm not knowledgeable with laravel, but couldn't you just do `$result = $nresult->merge($sresult);` See this question: https://stackoverflow.com/questions/41756404/laravel-eloquent-union-query — EternalHour, Feb 12 '19 at 22:30
it works for merging but there's an issue in the pagination. I did replaced it to `->get();` still looking to have it pagination — t.lore, Feb 13 '19 at 13:50
having this issue now `Method Illuminate\Support\Collection::paginate does not exist.` — t.lore, Feb 13 '19 at 13:56
Seems that you are trying to ask two questions at once. You were asking about merging the data. — EternalHour, Feb 14 '19 at 01:12

score 2 · Accepted Answer · answered Feb 14 '19 at 09:21

You can combine two select statements into one statement with the union operator. Then you can apply a global orderBy and paginate:

$news = DB::table("news")
    -> ...
    ->orWhere(...);

$result = DB::table("season")
    -> ...
    ->orWhere(...)
    ->union($news)
    ->orderBy("title")
    ->paginate(10);

Combining two different with no relationship DB table query in laravel for pagination

1 Answers1

Linked