View Detail on Foreign Key does not work when targeting an item outside the first 2000 rows

[iethree]

Describe the bug
When clicking "view detail" on a foreign key - if it is not in the first 2000 rows of the table, it will give the user a not found message.

Because of the nature of the bug, I don't think it's reproducible with the sample DB.

Logs

(nothing material)

To Reproduce
Steps to reproduce the behavior:

1. Go to a table with more than 2000 foreign keys defined
2. Click on a foreign key (that's not in the first 2000 rows of the table) and select "View Details"
3. Instead of seeing the object's details, you will get a "we're a little lost" error

Expected behavior
The user should see the detail view of the object selected

Screenshots

detailNotFound.mp4

Information about your Metabase Installation:

You can get this information by going to Admin -> Troubleshooting.

• Your browser and the version: Google Chrome Version 100.0.4896.127 (Official Build) (64-bit)
• Your operating system: Ubutntu 20.04
• Your databases: Postgres 14.2
• Metabase version: master branch - 2022-04-19 - v0.42.1-SNAPSHOT"
• Metabase hosting environment: local development
• Metabase internal database: H2

Severity

Makes foreign-key object detail view useless for most large tables.

[flamber]

Regression in 0.43.0-rc1 since #20101 @kulyk

P1 contender.

Reproduce 1 - problematic

1. Question > Sample > Orders
2. Filter ID - input 2 and 18000
   
3. Click User_ID=2392 > View details
   
4. We're a little lost...
   

Reproduce 2 - less problematic

1. Question > Sample > Orders - save as "Q1"
2. Add to dashboard and create Dropdown filter connected to Products.Category
3. Filter by Doohickey and click question title
   
4. On the question, it shows a table with everything correct - and clicking ID=6 will show "Previously" correctly disabled
   
5. Go back to the dashboard and filter by Doohickey and click ID=6
   
6. Show Object Details with "Previous" enabled, which is incorrect
   

[ranquild]

After spending a few hours on trying different approaches I believe that the correct way to fix this is to refactor the detailed view to load data for the selected entity and DO NOT use data from the active query. This would allow opening the view within a query for related entities without loading another table which might actually not have the selected entity.

[mazameli]

@ranquild I'm not sure how to unblock you. Questions:

1. What is the user-facing/UX consequenc(es) of "refactor the detailed view to load data for the selected entity and DO NOT use data from the active query", if any?
2. How long do you think this proposed refactor would take? Does @flamber et al think this needs to block 0.43?
3. Does @kulyk have any thoughts that could help?

[ranquild]

@mazameli If we change the object details view in this way we would not navigate to a different table when browsing details. We could make Up and Down buttons work by navigating between results in the current dataset.

[ranquild]

fyi @kulyk @flamber @mazameli an easy fix would be to add a primary key filter when navigating to another table. E.g. when clicking on User ID in Orders table we currently open People without filters and fail when there is no selected entity. But we could open the table with a filter for the primary key and it won't fail.