How to do a query search in multiple tables?

You can use a union for it

https://laravel.com/docs/7.x/queries#unions

But since it's 20 tables I think it's better to create a view and query that view.

Something like this in your migration

public function up()
{
  DB::statement(
    "CREATE OR REPLACE VIEW search_views AS
  	 SELECT title, 'books' 
  	 FROM books
  	 UNION ALL
  	 SELECT title, 'games'
  	 FROM games
  	 UNION ALL
  	 SELECT title, 'movies'
  	 FROM movies;"
  );
}

Then you can do something like

$records = SearchView::where('title', $request->title)->get();

Wow that helped a lot, thanks

@tray2 I just noticed that when i do a where search on the query search Laravel just ignores the union tables, he only do the where search on the first table, the other tables he just concat the records, do you know how can i solve it?

I'm doing something like that:

$query = DB::table('table1');

$query = $query->union(DB::table('table2'));

$data = $query->where('json->hash', 2803282938)->get();

So he show all the records from table2 next tables and apply the where clause only on the table1 , some solution for this?

Note: I'm using a SQLite database file.

Not knowing the structure of your tables it's hard to guess what the issue is. However the important thing when doing unions is that you have the same name on the fields and the same number of fields.

If you take you final query and replace ->get() with ->toSql() and then we can analyse the query. Also show the complete query builder query.

Eloquent and the Query Builder are awsome tools but sometimes it's just more readable to do it in plain SQL.

All my tables have the same columns, are two columns a id and json the only thing that changes are the structure of the json, but each json is a string, what i did was get all table names and do a foreach to do a union with all tables, because actually it is 56 tables. Well, the only solution i found was do a where in each query of table before do the union, but its a little bit ugly i think, if you have a better solution i'll appreciate it.

In fact it is doing the where only on the first query, i tried to use ->toSql() to see and this is the result:

select * from (select * from "table1" where "hash" = ?) union select * from (select * from "table2") union select * from (select * from "table3") union select * from (select * from "table4") . . . . 

Rule number one never do anything in php that the database can do for you.

Since you have the same structure on all tables why not just make it one table?

• id
• json
• type

That would look something like this in the table

1   {"title": "Some title"}            games
2   {"title": "Some other title"} books
3   {"title": "A third title"}          movies

So to get all the books you just do

Product::whereType('books')->get();

And to get everything just

Product::All();

I'm very allergic to using json in the database since it kind off defeats the purpose but it makes the query above much easier.

If you don't change to one table I suggest you do a database view with unions

CREATE VIEW all_products AS
SELECT id,
	     json,
             'books' AS type
FROM table1
UNION ALL
SELECT id,
	     json,
             'movies' AS type
FROM table2
UNION ALL
SELECT id,
	     json,
             'games' AS type
FROM table3

And so on.

Then you can query it the same way you would a single table.

https://dev.mysql.com/doc/refman/8.0/en/create-view.html