UNION DISTINCT on laravel?

Question

MySQL

Hello. There are several databases. All are identical in structure, the difference is only in content. There is a table bets, and the corresponding model. The bets table has a user_id column. It may repeat. I need to pull out the number of unique user_ids (this is implemented using $query->distinct('user_id')->count('user_id')). But I need to do this for all databases. The name of the databases and tables is given, and the following code is now written:

public static function todayAll() {
        $queries = [];
        $rooms = []; // get list somehow (HIDEN)
        foreach($rooms as $room) {
            $query = DB::table(db_table_name($room, self::class))->where('created_at', '>=', Carbon::today())->distinct('user_id');
            $queries[] = $query;
        }
        $result = $queries[0];
        for($i=1; $i < count($rooms); $i++) {
            $result = $result->unionAll($queries[$i]);
        }
        return count($result->get());
    }

Unfortunately, I can't call it count()before the union, and I also can't call it after, since then it only applies to the first request and not correctly. You have to use the usual one countfrom php. Actually replacing unionAllwith unionthe same does not help. I do not know what to do. This f-th must be optimized, because so many requests to different databases are too much.

Answer 1

As an option - make a view in the database that combines the results from all tables, and then a distance count based on the results of the view.