New features: 1) ability to enter ID manually to column that has a foreign key, 2) Follow foreign key

[expired user #10560]'s profile image [expired user #10560] posted 7 years ago in Feature discussion Permalink

I'm using HeidiSQL for a long time, but there is a feature that is missing for me and would be really helpful if exists in HeidiSQL.

Right now if there is a foreign key on a column (f.e. user_id), when you try to change it in "Data" tab it shows "select" field instead of text field, which may be fine for some users, but it's not sorted by ID, the order is random.

I think there should be option on right click like "Enter value" which allows user to enter any ID, without searching it on the list, which is not the best option.

Second feature which may be helpful is also around foreign keys. I think there should be right click option like "Go to" or "Follow" that will display record that is associated with that row. F.e. we have posts table where there is user_id column. When we click a row with user_id = 5, right click and "Follow" then Heidi should show users table with filter:id=5.

I think those functionalities are not difficult to develop and may improve using of HeidiSQL much more. Also - if there is some repository somewhere I can try to develop them by myself, as I'm a developer too.

ansgar's profile image ansgar posted 7 years ago Permalink

Right now if there is a foreign key on a column (f.e. user_id), when you try to change it in "Data" tab it shows "select" field instead of text field, which may be fine for some users, but it's not sorted by ID, the order is random.

The order is not random, it's ordered by the first text/varchar column in the foreign table HeidiSQL finds. Also this text column is displayed in the drop-down menu, so you can better identify the row.

There are currently two ways to manually enter values in a column with a foreign key:

  • in preferences > "Data editors", you can turn off "show values in foreign key columns. See attached screenshot.
  • just paste what you have in your clipboard, without pressing F2
1 attachment(s):
  • preferences-foreign-columns
[expired user #10560]'s profile image [expired user #10560] posted 7 years ago Permalink

There are currently two ways to manually enter values in a column with a foreign key:

  • in preferences > "Data editors", you can turn off "show values in foreign key columns. See attached screenshot.
  • just paste what you have in your clipboard, without pressing F2

thanks for fast reply, disabling the option helped, but still - select field is helpful if there are only a few rows, so we can easily select proper one. If it's possible it would be really helpful to use both - select or enter ID without changing options. And I know you can still use your clipboard, but it's quite often that I know that ID, have it in my head, but don't have it in clipboard, typing it anywhere just to copy it and paste in the field sounds a little bit overcomplicated, especially that all other columns can be very easily changed via double click or F2, except this one.

  1. What do you think about "Follow" functionality I described?
ansgar's profile image ansgar posted 7 years ago Permalink

The drop-down menu supports auto-selecting while typing. So, if you know the id, you can activate the drop-down, type the id quickly to find it, then check if it's the right one by looking at the text value.

That follow-to-foreign-table feature sounds nice. I could add a right-click menu item for that purpose which then opens a new query tab and fire a query to get the relevant table row.

[expired user #10560]'s profile image [expired user #10560] posted 7 years ago Permalink

The drop-down menu supports auto-selecting while typing. So, if you know the id, you can activate the drop-down, type the id quickly to find it, then check if it's the right one by looking at the text value.

Unfortunately it does not work as expected, that's why I raised it, let's say you have 1000 rows and you want to to set ID to 123. When you press 1 you see f.e. id=100, then, when you press 2, you see id=200. You can say "you can press 1 many times to find your id", but in this case they are not ordered by names, so when I press 1 constantly I see f.e. id=100, 133, 121, 1043 etc. Searching for id=123 is really hard then.

That follow-to-foreign-table feature sounds nice. I could add a right-click menu item for that purpose which then opens a new query tab and fire a query to get the relevant table row.

Sounds good, but please mind that sometimes it has few levels, f.e. from posts you want to go to related user records, and from there go (follow) to related group record from users table. If you use "Query" instead of "Data" tab, there is no possibility to move to another level, as query results differs from "Data" tab and I guess it won't have "Follow" option in results (same as it does not have a select field for foreign keys).

ansgar's profile image ansgar posted 7 years ago Permalink

The drop-down menu supports auto-selecting while typing. So, if you know the id, you can activate the drop-down, type the id quickly to find it, then check if it's the right one by looking at the text value.

Unfortunately it does not work as expected, that's why I raised it, let's say you have 1000 rows and you want to to set ID to 123. When you press 1 you see f.e. id=100, then, when you press 2, you see id=200. You can say "you can press 1 many times to find your id", but in this case they are not ordered by names, so when I press 1 constantly I see f.e. id=100, 133, 121, 1043 etc. Searching for id=123 is really hard then.

Well you have to type more quickly then. Please try it: type "123" quickly, which should bring you the id "123".

Please login to leave a reply, or register at first.