[Feature]: Join columns from another table in "Browse Data"

[mmm29]

Hello.
Sometimes when browsing a table ("Browse Data" view) it's very useful to see joined columns from another table.
For example, if we have users and groups tables, and a user is assigned to any of the existing groups by their row ids (users table has a column named "group_id" that is a foreign key that references groups.id), it's useful while viewing the users table to join the groups table and see the group name along with the group row id, instead of looking every time in the groups table to see the group name.
Of course, we can achieve this behavior using the "Execute SQL" view and use a SQL query with JOIN to return desired columns along with joined ones, but the "Execute SQL" result has no filter, sorting and ability to delete/duplicate rows.
Thanks!

[scottfurry]

For those of us with grey hairs and long memories...

This sounds like something from MS Access. MS Access would perform the look up of the foreign key/joined table to display row data involved. There were other quirks, aka "features", MS Access did wrt to foreign keys, joined tables, et al. Although, those new to SQL got totally twisted around by the display without understanding the underlying mechanism MS Access was performing. I digress...

A dynamic view of data is kind of cool, on its face.
@chrisjlocke, @justinclift - maybe categorize this as a "long term" direction pile (i.e. discussions and planning pile).

[chrisjlocke]

I've tried this before, writing my own quick utility to tidy a database, and it can get a bit messy (ie, specific to the occasion). List records, and delete from table A, but if you're deleting that record, it'll be handy if you also delete that record over there, and that one, and if you've two records which link ...... .... ... .... /head explodes

Its always OK if tables are 'simple' and have numeric primary keys. A quick browse of the SQLite forum, and you see users who love nothing more than tables with weird-arse keys like multi-column multi-dimensional timed-value quasi-fruity values. Bah.

[scottfurry]

@chrisjlocke
...leaving aside developers penchant for ineffective and/or obtuse variable naming schemes...
Tell us how you really feel? 😸

I can imagine the situation you describe. And having a means to load a database scheme into memory and working on it in a dynamic manner that doesn't result in silicon having apoplectic fits resulting in releasing the hidden installed manufacturer's smoke is not without challenges.

I kind of like the notion being asked for. Again, "long term planning" pile.

[justinclift]

@mmm29 Are you familiar with "view"'s?

They're pretty much "virtual" tables, that look like a table but are really pulling data from multiple real tables underneath:

    https://www.sqlite.org/lang_createview.html

It sounds like what you're after?

[chrisjlocke]

Wake up @justinclift 😂 What part of 'Of course, we can achieve this behavior using ... view and ... JOIN ... but [no] ability to delete/duplicate rows.'

Deleting records via views involves writing about 5387465836537568374587365487356873658357935039598375837598375983757365873658735687365873 triggers. 😮

[justinclift]

Oops, needed more coffee before skimming this. 🤦

[justinclift]

Actually no. Reading the original issue again, it says:

Of course, we can achieve this behavior using the "Execute SQL" view ...

With the word "view" there seeming to mean more "Execute SQL tab". "View" in there seems like a co-incidental word use?

[mmm29]

@mmm29 Are you familiar with "view"'s?

They're pretty much "virtual" tables, that look like a table but are really pulling data from multiple real tables underneath:

https://www.sqlite.org/lang_createview.html

It sounds like what you're after?

@justinclift, I didn't know about views in SQL. Learnt something new, thanks. 😄
SQL views as a concept is actually what I've thought of, this is almost what I wanted except one thing. "Browse Data" lets me see the contents of the view, filter and sort it as I would do with regular tables. But it doesn't allow me to edit/delete/duplicate rows even if I have a column "id" in the view. It gives me an error "Error deleting record: could not get column information". I understand that this is the expected behavior of a view, since they are not designed to modify the data, but it would be cool to have such a "view" that allows us not to only view the data, but also to modify it.

[justinclift]

Hmmm, which version of DB4S are you using?

I kind of remember that someone... (@MKleusberg maybe?) was doing work so that some changes made to a view (in the Browse Data tab) would actually make it through to the underlying table.

Not remembering how far it got though.

Um, if you're using macOS or Windows, and are using one of our official release builds of DB4S, then maybe try our nightly builds instead?

    https://nightlies.sqlitebrowser.org/latest/

Saying that because that's where the most recent work on this would be. Possibly worth trying out. 😉

[mmm29]

Hmmm, which version of DB4S are you using?

I kind of remember that someone... (@MKleusberg maybe?) was doing work so that some changes to a view would actually make it through to the underlying table.

Not remembering how far it got though.

Um, if you're using macOS or Windows, and are using one of our official release builds of DB4S, then maybe try our nightly builds instead?

https://nightlies.sqlitebrowser.org/latest/

Saying that because that's where the most recent work on this would be. Possibly worth trying out. wink

I usually use linux and I use DB4S built from source, version 3.12.99 (Oct 22 2022).
I also have windows and tried the nightly builds you gave me, version 3.12.99 (Oct 30 2022).
The result is the same.
Any modification of the view is not possible (Set to NULL, cut, paste are also inactive), and while deleting a row I get "Error deleting record: Invalid file format". I don't remember what I did to get "Error deleting record: could not get column information" as I noticed in the previous message, now I always get "Invalid file format" when deleting a row.

[justinclift]

No worries, guess my memory was being dodgy then. 😉

[chrisjlocke]

Editing views is possible, but there is a 'secret option'. It also involves triggers.
See the wiki of love for more information. In fact, the Wiki has a section on views for your viewing pleasure.
But see this link for editing.
https://github.com/sqlitebrowser/sqlitebrowser/wiki/Views#editing-data-in-a-view