One-to-many?

[yaskevich]

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.

[bigpresh]

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!

[msouth]

@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?

[yaskevich]

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 
  );

• Form "author": Add author/Edit name.
• Form "text": Add text/Edit title

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. Data: Nightfall; Isaac Asimov, Robert Silverberg The Adventures of Huckleberry Finn; Mark Twain

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 "SELECT from..." before INSERT or use "INSERT OR REPLACE INTO ..." – I'm not very good with these SQLite-specific techniques) or "untie" respectively ("DELETE from "relations" where author_id = ? and text_id = ?").

[msouth]

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.)

[yaskevich]

@msouth Yes, you're right, one could make a workaround, I've even implemented such a solution just for test: yes, it is possible, not really comfortable and lacks additional checks for relations data - e.g., it easily creates duplicated records. (Also it seems SimpleCRUD will need a column with index id for the relations table.)

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.

[msouth]

@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.]

[yaskevich]

@msouth Yes, you are right, I agree with your notes, also my code above is not completely tested, some lines I wrote directly here (e.g. inserts into relations require a column name).

[three18ti]

@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:

/* get server name and ips */
SELECT server.name, ip.ipFROM server
    LEFT JOIN server_ips ON server.id = server_ips.server_id
    LEFT JOIN ip on server_ips.ip_id = ip.id;

As it stands, I don't think there's an easy way to do this with SimpleCRUD, though I'm certainly open to suggestions.

[joshrabinowitz]

I experimented with a solution for this in PR #88