Display foreign key name instead of ID

[BattleBrisket]

I take the time to establish all my foreign key relationships, and identify the "name" field on the tables, and am rewarded with no ability to swap an opaque Id field with more human labels.

Current behavior as default makes sense, as names will likely introduce at least single JOIN and thus affects performance. However an option to do so would be much appreciated. Without question, this is the single most common reason I have to drop a Question into raw SQL mode.

[camsaul]

Somewhat related: #2305

[salsakran]

+1

We had talked about this early on but dropped the ball. It's super useful in looking at lists of "real oblects" and generally makes things feel a little less raw

[tlrobinson]

+1

[salsakran]

Some thoughts here:

This should probably be an option on a per-table level. For large datasets in eg Redshift, doing a join might not be something we want to do automatically for every table.

What's a good definition of "large" here? 100k rows?

Also, should this be a join or a second query? Can we trust the query planner to DoTheRightThing if the joined columns aren't used in the WHERE clause?

[BattleBrisket]

Also, should this be a join or a second query? Can we trust the query planner to DoTheRightThing if the joined columns aren't used in the WHERE clause?

I would make this decision automatically, on a per-query basis following the logic: If the query planner (1) has established foreign key relationships, and (2) they proactively choose to display the foreign key value in the query results.

This negates the need for Metabase to concern itself with table-level toggles or arbitrary definitions of large-ness.

[dacort]

Would be pretty helpful to see this, along with the ability to choose what field would be shown. I'm experimenting with typical fact/dimension tables and the metabase query builder becomes very unfriendly.

[tlrobinson]

@dacort 0.19.0 has the ability to disable and reorder columns in the table view. Click the new gear icon next to the visualization type menu.

#3043 Is a prototype of a way to pull in additional columns into a table, but it's not smart enough to do the hybrid entity name + link to entity object detail page that I think we should do.

[tlrobinson]

To summarize the ideas so far:

1. per-table setting (default based on number of rows?)
2. per-query + column setting
3. if we're already doing the join, go ahead and pull the entity-names in
4. if the result set contains an entity/fk id column, and the cardinality is low enough (<100?) then do a follow-on query to get the entity names (also have a way to disable this per table?)
5. load + cache entity names "out of band" (not as part of the query)

3 wouldn't work for "raw data" until we add something like #3043

[dacort]

Thanks, @tlrobinson, just ran into that today!

In most of my cases, I'd still want to display the foreign name, but good to know I can hide the ID for the time being.

[ibushong]

+1

It kinda boggles my mind that this isn't already a feature. I would think this would be the main purpose for assigning the "Entity Name" metadata attribute on each table. When viewing table data, it would be much more useful (especially for non-technical people) to see the entity names, rather than an ID, especially as we start joining multiple tables.

Slightly related, I believe it would be more user friendly to have "Group By: User" instead of having to choose "Group By: User-->ID".

Example use case that I think is be pretty standard/baseline: We want to see the total number of orders each user has made.

IMO, ideal UX:

1. Select "Orders" table
2. Select "Count of Rows"
3. Select "Add Grouping" -> "User"
4. We are presented with a table with two columns, User Name, and number of orders
   (As discussed, the output should be configurable as to display the ID or the configured Entity Name)

As another point of example/reference, most web admin interfaces (g.g. Django, flask-admin) will display any foreign keys' string representation in list views, rather than just the raw ID.

Sorry for the rant and if this sounded condescending :) I just think this feature/capability makes so much sense given that Metabase is a tool to help non-technical people understand and query data.

[mazameli]

Love this, and would love to see it get implemented. Only thing that feels lame is having this be a per-table setting — unless it defaults to On, and you selectively turn it off? It would just suck to have to enable this for all your tables, assuming this would more often than not be the behavior you'd want.

[salsakran]

@ibushong Appreciate how strongly you feel about the matter ;)

I think we should be relatively smart about turning this on re: table size. Again, while this works amazing on toy dataset sizes, slapping this onto a redshift events table would result in a world of pain of done naively.

[LukeAbell]

+1!

[ipeevski]

This would be an amazing feature. With the level of polish of this product, it was a real disappointment when I linked the metadata and it wasn't displaying friendly names instead of just IDs.

I think it's been mentioned somewhere else, but this should also extend to filtering on foreign key columns (show dropdowns of friendly names, rather than field to type a number when filtering by a foreign key).

[etcook]

+1 - the lack of ability to do this was quite surprising in such a sophisticated and comprehensive product.

[kdoh]

Hey all. Excited to give you some updates on this. The ability to remap field values to other columns through foreign keys just hit master and will ship as part of our next release 0.25. Thanks for your patience and your feedback on this.