Crud: [Question] How to display name column from another table instead of referencing ID ?

[alekth85]

Ok, so I'm going insane with this most basic functionality I can think of.. I can't seem to find any proper answer to this question .. everybody is just giving link to the documentation page, and I've been trying to crack this one for a whole day now... without luck, which is really frustrating because you can't go more basic than this.

Anyway I have a table Hotels... this table contains bunch of columns, one of them is 'country', type integer.
This integer in this column is a reference to the ID field of table countries.

Default behavior is to just display the data from the table... this is how my hotels look like:
+----+-------------+---------+
| id | name | country |
+----+-------------+---------+
| 1 | test hotel1 | 1 |
+----+-------------+---------+

and this is how backpack shows it... what I want it to show is:

mysql> select h.id, h.name, wc.name from hotels AS h LEFT JOIN world_countries AS wc ON h.country = wc.id;
+----+-------------+----------+
| id | name | name |
+----+-------------+----------+
| 1 | test hotel1 | Cameroon |
+----+-------------+----------+

I tried this code:

`
$fda = [
'label' => "Country",
'type' => 'select',
'name' => 'country', // the db column for the foreign key
'entity' => 'country', // the method that defines the relationship in your Model
'attribute' => 'name', // foreign key attribute that is shown to user
'model' => "App\Models\Hotel" // foreign key model
];

    $this->crud->setColumnDetails('country', $fda);

`

.. and it does do something, but something wrong. I can see the query in the sql log:

2018-07-15T09:40:42.272792Z 66 Prepare select * from world_countries where world_countries.id in (?)
2018-07-15T09:40:42.273076Z 66 Execute select * from world_countries where world_countries.id in (1)

but the backpack shows: "Error loading page. Please refresh the page."

and the resultset is empty... there's nothing in it.

Can anybody help out here...?

[welcome[bot]]

Hello there! Thanks for opening your first issue on this repo!

Just a heads-up: Here at Backpack we use Github Issues only for tracking bugs. Talk about new features is also acceptable. This helps _a lot_ in keeping our focus on improving Backpack. If you issue is not a bug/feature, please help us out by closing the issue yourself and posting in the appropriate medium (see below). If you're not sure where it fits, it's ok, a community member will probably reply to help you with that.

Backpack communication mediums:

• Bug Reports, Feature Requests - Github Issues (here);
• Quick help (_How do I do X_) - Gitter Chatroom;
• Long questions (_I have done X and Y and it won't do Z wtf_) - Stackoverflow, using the backpack-for-laravel tag;

Please keep in mind Backpack offers no official / paid support. Whatever help you receive here, on Gitter, Slack or Stackoverflow is thanks to our awesome _awesome_ community members, who give up some of their time to help their peers. If you want to join our community, just start pitching in. We take pride in being a welcoming bunch.

Thank you!

--
Justin Case
The Backpack Robot

[AbbyJanke]

You will want to use the select column type:

```[
// 1-n relationship
'label' => "Parent", // Table column heading
'type' => "select",
'name' => 'parent_id', // the column that contains the ID of that connected entity;
'entity' => 'parent', // the method that defines the relationship in your Model
'attribute' => "name", // foreign key attribute that is shown to user
'model' => "App\Models\Category", // foreign key model
],

[tswonke]

• You will have to use the Country model in you code instead of Hotel
• You need a relationship country() in your Hotel-model
• attribute is the name of the db-column of the countries-table you want to be shown

[alekth85]

The documentation is not helpful at all. There are unanswered questions everywhere because of this, and I found ton of them..

@tswonke

I'm using a Hotel model and specifying relationship country() .. which exist:

app/Models/Hotel.php:
public function country() { return $this->hasOne(Country::class, 'id', 'country'); }

Yes, i assumed as much... but I'm not getting why it doesn't work.
I also found this in the error.log after I posted here:

[2018-07-15 12:05:08] development.ERROR: Trying to get property 'name' of non-object

But it's clearly doing the query on the right table and all ..

If I use Country model in my code instead of Hotel, what do i specify as "entity" ? In that case, there's no relationship .. Country model has no relationships defined, but Hotel has... it has Country relationship as described above.

[alekth85]

I cracked it.

The relation cannot be called "country" inside Hotel model (probably because the column name is "country" already ? ).

Renaming relation to country_rel for example worked.

[tswonke]

First of all IMHO you need a belongsTo-relationship instead of a hasOne-relationship. A country "can have" many hotels, so it's not 1-1.

With that changed, you code should work like this (if the name of the country is stored in a db-column called "name"):

$fda = [
   'label' => "Country",
   'type' => 'select',
   'name' => 'country', // the db column for the foreign key
   'entity' => 'country', // the method that defines the relationship in your Model
   'attribute' => 'name', // foreign key attribute that is shown to user
   'model' => "App\Models\Country" // foreign key model
];

[tswonke]

Concerning your last post, I would suggest to use country_id in your hotels-table to be more specific.

[alekth85]

Yes, I think I will have to rename them all and stick to it from now on.
This seem to be general assumption and it is clearer.. with _id suffix.

I would suggest adding a note in the documentation in the relevant section ... that the relation name must not be the same as a column name within the table / or prevent this somehow (if this truly is the reason for it.. but it seems it is as it does work now; and good catch about relation being wrongly defined.. thanks).

[tswonke]

Good to hear that it works now!

The "same name" issue is not backpack related but due to Laravel itself.

[Mthach]

ewrwer