How to get the columns from the master table related with foreign key?

[bhattji]

What happened?

How to get the columns from the master table related with foreign key?

For example I have location_id in my User object, and in my resource I use location.name to get the name of the location, but that does not work in report.

How to reproduce the bug

when I try to retrieve the name value through foriegnId

Package Version

v3.0.x

PHP Version

PHP 8.3.9

Laravel Version

11.x

Which operating systems does with happen with?

No response

Notes

No response

[eighty9nine]

Please find the reply here: #14 (comment)

[bhattji]

Please find the reply here: #14 (comment)

Great, this works

However, I have put the Select Location filter in Filters. it does show all the Locations, but doesn't filter any records.

public function filterForm(Form $form): Form
{
return $form
->schema([
Select::make('location_id')->label('Location')
->options(Location::orderBy('name')
->get()
->pluck('name','id')
->toArray()
),
]);
}

Can you show, how does this filter work?

[eighty9nine]

Extending on the previous example, you do something like:

  Body\Table::make()
      ->data(
          fn(?array $filters) => User::join("locations", "location.id", "=", "users.location_id")
            ->where("users.deleted_at", null)
            ->where("users.location_id", $filters["location_id"])
            ->select("users.name", "users.email", "locations.name")
            ->get()
       ),

[bhattji]

Thanks for the response

It errors

Internal Server Error

ErrorException
Undefined array key "location_id"

==================================

don't we need to define the fiters array somewhere with location_id? where? how will the query get the filter value being defined in the Select component?

[eighty9nine]

You need to check check if it is null, since when the page loads, there is no value until you press the filter button. So you could change it to:

  Body\Table::make()
      ->data(
          fn(?array $filters) => User::join("locations", "location.id", "=", "users.location_id")
            ->where("users.deleted_at", null)
            ->when(isset($filters["location_id"], fn($query) => $query->where("users.location_id", $filters["location_id"]))
            ->select("users.name", "users.email", "locations.name")
            ->get()
       ),

[bhattji]

Excellent
It works

btw, can we group the items in reports? for example, instead of listing Location as Column, We can put it in Body Row, so that all the items of each location will be listed under it

[eighty9nine]

That is alot more complicated but in general you would something like:

    public function body(Body $body): Body
    {
        return $body
            ->schema([
                  ...Location::all()
                      ->map( fn(Location $location) => [
                        Text::make($location->name),
                        Body\Table::make()
                        ->data(
                            fn(?array $filters) => User::join("locations", "location.id", "=", "users.location_id")
                              ->where("users.deleted_at", null)
                              ->where("users.location_id", $location->id)
                              ->when(isset($filters["location_id"], fn($query) => $query->where("users.location_id", $filters["location_id"]))
                              ->select("users.name", "users.email", "locations.name")
                              ->get()
                           ),
                   ])
            ]);
    }

I'm not sure if it will work but it should give you some idea on how to go about it. Please feel to sponsor my work, it helps alot in contributing to the open source! Sponsor Me

[bhattji]

Thanks for the reply,

I have tried all that I could, but I am not able to place map properly, can you please help me inserting the map/location etc. in this code?

Thanks in advance

    public function body(Body $body): Body
    {
        return $body
        ->schema([
            Body\Layout\BodyColumn::make()
                ->schema([
                    Body\Table::make()
                        ->columns([
                            TextColumn::make("location_name")->label('Location'),
                            TextColumn::make("code"),
                            TextColumn::make("name"),
                            TextColumn::make("asset_type_name")->label('Type'),
                            TextColumn::make("asset_status_name")->label('Status'),
                            TextColumn::make("purchase_value")->label('Cost'),
                        ])
                        ->data(
                            fn (?array $filters) => $this->reportQyery($filters)
                        ),
                ]),
        ]);
    }

[eighty9nine]

Please contact me directly at eighty9nine@gmail.com and we can setup a call in order to sort that out.

[bhattji]

I have sent you an email, kindly respond