How to query 20+ tables and output a single variable to View in Filament

Laravel+Filament V2 First Time Project (made a few simple blogs in Laravel)

Imagine something like this:

Table 1 "Company"
columns: id, name
Table 2 "Company Members"
columns: id, company_id, total_members, members_10s, members_20s
Table 3 "Company Achievements"
columns: id, company_id, scale, projects

This method requires my tables to have same fields, which just does not make sense to me (how can I put number of members into the same field as company name?). ANY video I managed to find on YouTube ALWAYS has literally one table for users and one table for entity and does not have anything specific for this mess.

I've been staring at my app\Http\Controllers\CompanyController.php for the past 2 days, but staring seems to have done very little to resolve my conundrum

        $companies = Company::query()
            ->where('created_at', '!=', NULL)
            ->orderBy('created_at', 'desc')
            ->paginate();

        $members = CompanyMembers::query()
            >select('')
            ->where('company_id', 'LIKE', )
            ->get();

        $achievements = CompanyAchievements::query()
            ->select('')
            ->where('', 'LIKE', )
            ->get()

        $companyData = $companies->union($members)->union($achievements)->get();

Please, show me the way

Maybe this and this are my way out of this one, but I'm not sure if that's the go-to way when it comes to TALL stack and Filament

vincent15000

2 years ago

Level 63

If you have some informations to share with us so that we can help you, please don't write all these links and share the informations right here.

These links are in the best case some simple links with some code or other information, in the worst case some XXXware, nobody will click on your links.

Pawooo

2 years ago

Level 5

@vincent15000 Oh, my bad — I thought staying transparent like this is better!

krisi_gjika

2 years ago

Best Answer

Level 14

are you perhaps looking for something like this:

$companies = Company::query()
            ->with(['members', 'achivements'])
            ->where('created_at', '!=', NULL)
            ->orderBy('created_at', 'desc')
            ->paginate();

2 likes

Pawooo

2 years ago

Level 5

@krisi_gjika This could be just it! Will check my models to see if I got everything set up correctly! Thank you so much!

So it doesn't matter if it's Fillament/TALL – just use eager loading as suggested above https://laravel.com/docs/10.x/eloquent-relationships#eager-loading-multiple-relationships

Tray2

2 years ago

Level 73

The easiest would be to use a union to join multple queries together. The important thing is that the data type for all the columns must be the same.

An example , if you have four tables, books, records,games, and movies, and you want the tem latest posts from these, and not the ten latest from each table but the latest in general, then you could do this.

SELECT * 
FROM (
  SELECT title, created_at, 'books' AS source_table
 FROM books
  UNION ALL
SELECT title, created_at, 'games' AS source_table
FROM games
  UNION ALL
SELECT title, created_at, 'movies' AS source_table
FROM movies
  UNION ALL
SELECT title, created_at, 'records' AS source_table
FROM records
) ORDER BY created_at DESC
LIMIT 10;

To make that look nice in Eloquent or the query builder isn't possible, so I suggest taking a look here

https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application

1 like

Pawooo

2 years ago

Level 5

@Tray2 Thanks! Extremely easy-to-understand!

1 like

Please or to participate in this conversation.