Is it possible to create a master-detail form?

[llagerlof]

Let's say, like a user form as main form and addresses as a detail form allowing more than one address for each user.

[lovasoa]

If I understand well, your need is to create users, and associate multiple addresses to each user ?

I added an example to this repo: Data insertion for One-To-Many Relationships: Master-Detail Forms

The main idea is to create two separate forms. The first one is an edition form for the already-existing user record, and the second is a form to add an address to the user.

When you initially load the user creation form, we do not display the address form. Only when the user has been created, you are redirected to the user edition form that contains the address form.

Does this solve your problem ?
How do you think the documentation could be improved to make this easier to discover ?

[llagerlof]

That's exactly what I was talking about. I missed it because I was not reading the directories, only the main content of the README.

I think this example could be included in the Examples section as the master-detail example. This term, I believe, is more well-known than nested forms.

You could also consider adding the other examples in examples directory to the README.

Thank you very much!

[lovasoa]

You were not missing anything, I created this example after you posted your question, because I thought it was a good thing to have documented 😬

I'll rename it to master-detail (which indeed seems to be a consecrated term for it that I didn't know) and add a link from the README.

[nofue]

So far, so fine. But insert_user.sql has a twist I don't understand:
UPDATE SET first_name = excluded.first_name, last_name = excluded.last_name, email = excluded.email
Where all of a sudden comes this 'excluded.' table name from? Is this a PostgreSQL thing? Then why does it still work when run on a sqllite in memory database despite the comment in said file?

[lovasoa]

Yeah, this is PostgreSQL syntax, but SQLite has partial support for it too. Let me break it down:

What's happening

The syntax you saw:

UPDATE SET first_name = excluded.first_name, ...

comes from a PostgreSQL-specific feature used with INSERT ... ON CONFLICT ... DO UPDATE.
In Postgres, when you use an upsert, there's a special pseudo-table called excluded that represents the row you tried to insert but got rejected due to a conflict. You can then reference excluded.<column> to pick values from that would-be-inserted row.

Example:

INSERT INTO users (id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john@example.com')
ON CONFLICT(id)
DO UPDATE SET
    first_name = excluded.first_name,
    last_name  = excluded.last_name,
    email      = excluded.email;

• If id=1 exists → the DO UPDATE kicks in.
• excluded = the row you attempted to insert.
• You can choose to take some or all values from it.

Why it still works on SQLite

SQLite copied this feature from PostgreSQL starting v3.24.0 (released in June 2018).
SQLite’s syntax for ON CONFLICT DO UPDATE is almost identical, including the special excluded table name.

So:

• If your in-memory SQLite is recent (most frameworks bundle ≥3.24), excluded works fine.
• If you were on an older SQLite, you'd get a syntax error.

TL;DR

• excluded is not a real table; it's a special alias available only inside an ON CONFLICT ... DO UPDATE.

More info

• https://www.sqlite.org/lang_upsert.html
• https://www.postgresql.org/docs/current/sql-insert.html

[nofue]

Thanks for getting into all the detail -- TL;DR woulf have been plenty for my curiosity as I was already expecting this. However, one major question is still not answered: WHY would I want to use this pseudo-table when I have the actual table context still available?
In "real code" Inserts look like this:

INSERT INTO users (id, first_name, last_name, email)
VALUES (id_field, first_name_field, last_name_field, email_field)
ON CONFLICT(id)

and from there it's just reasonable to write

DO UPDATE SET
    first_name = first_name_field,
    last_name  = last_name_field,
    email      = email_field;

I'm sure there are situations to use excluded (but I have yet to find one ;)
Speaking of "reasonable coding" -- this exclude thing will need additional documentation to help the next coder to get the point (such as this conversation) without any benefit -- what am I missing here?

Oh, and while we're on it, maybe you should change the comments regarding sqllite in the master-detail-forms.sql code.

[lovasoa]

It's more efficient that way.