-
Notifications
You must be signed in to change notification settings - Fork 31
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
One-to-many? #34
Comments
Currently, there is no support for this; it would indeed be a killer feature though, and one I'd like to add, if I find enough time. Glad to hear you're finding SimpleCRUD useful! |
@yaskevich do you mind giving a quick example schema and a description of what you would want to see on the form/ and list views? |
CREATE TABLE authors (
author_id INTEGER NOT NULL,
author_name TEXT,
PRIMARY KEY (author_id ),
UNIQUE (author_id )
);
CREATE TABLE texts (
text_id INTEGER NOT NULL,
text_name TEXT,
PRIMARY KEY (text_id),
UNIQUE (text_id)
);
CREATE TABLE relations (
text_id INTEGER,
author_id INTEGER
);
One-to-many relation seems not to have easy implementation in HTML, but if we have small amount of data, we could just show all items that could be "many" as list of checkboxes. Here list of "author" checkboxes which are bound with "relations" table. Practically, adding relation of "text ↔ author(s)" is an additional transaction (when basic transaction is just adding an item/editing the name) on a time. INSERT INTO "texts" VALUES('The Adventures of Huckleberry Finn');
INSERT INTO "texts" VALUES('Nightfall');
INSERT INTO "authors" VALUES('Mark Twain');
INSERT INTO "authors" VALUES('Isaac Asimov');
INSERT INTO "authors" VALUES('Robert Silverberg'); The interface should tie authors and texts, like this: INSERT INTO "relations" VALUES(1, 1);
INSERT INTO "relations" VALUES(2, 2);
INSERT INTO "relations" VALUES(2, 3); (also surely we should do checking |
You can administer this database with a simple_crud setup for relations with both text_id and author_id set up as foreign keys, right? I realize it's not beautiful, but the whole point of this is kind of to have a simple interface to the database. If you make it sortable I think you could have a reasonably useful list come out of it, (say sort by author id column and you'll have the books grouped by author). (Understand, I'm not disputing that more capabilities == better, I'm just saying that SimpleCRUD can do a (simple) implementation of this with its existing features.) |
@msouth As for me, I can implement all the CRUD logics for my project. I only mention that this capability (one-to-many, like in example above) will make a SimpleCRUD even more attractive, especially for beginners, and really handy for experienced, but lazy people. |
@yaskevich Thank you for the further illumination you provided in response to my questions--I wanted to clarify exactly what it was you were looking for. Just to be clear on my intentions--I wouldn't want to come across as if I was being defensive about what the existing capabilities are, for example. I just want it to be as clear as possible what your pain point is, and it's also helpful to muse about what the solution would involve. [btw, sorry for not noticing that you were not using an id in your join table, I obviously skimmed that response too quickly--I had done a quick test locally but I put ids by default and just didn't notice that detail of your structure] [one more aside--I think you would want a unique multicolumn constraint on that relation table--at least in that case you would get rejections at the db level for duplicates.] [ok I guess two more notes--you're really talking about a many to many relationship here (at least among books and authors), right? Again, I'm asking just to be clear. A book could have multiple authors, and your schema would have no trouble representing that.] |
@msouth |
@msouth this is an awesome module Thanks! I just wanted to throw my vote in (not that I get a vote) for this feature. I've recently started on a project to create an interface for a database of IPs and subnets. Only I'm doing the reverse in a many-to-one type of relationship. My full schema is here: https://gist.github.com/three18ti/6200127 But the gist is: I have a table of servers, a table of ips, and a table linking the ips to servers, I'd like to be able to pull a list of servers and at the same time list IPs assigned to each server. (SQL is by no means by wheelhouse, but) I think the sql that acheives what I am looking for is:
As it stands, I don't think there's an easy way to do this with SimpleCRUD, though I'm certainly open to suggestions. |
I experimented with a solution for this in PR #88 |
At first, I'd like to say that SimpleCRUD is a great solution for quick CRUD tasks, especially as an alternative for popular ways of easy sharing of structured data (like Google Spreadsheets).
However, first advantage of DB against spreadsheet is relations between tables. If we have only id-by-id relation, it's easy and everything works like a charm, but how to deal with a one-to-many relations? E.g. if one designs a task list and needs to relate a task to several persons. I can't find a way to do it easily, without implementing all this logics manually.
The text was updated successfully, but these errors were encountered: