Widgets for n 2 m Relations

In this case we talk about a multi-to-multi relation. Each record of the table in question is related with zero to n records of another table (called related table here). The relation is realised via a third table (called relation table here) which normally contains only values of the primary keys of the table in question and the related table.

This is the definition of a simple geometry table, loadable as layer into QGIS:

CREATE TABLE point
(
  gid integer NOT NULL,
  the_geom geometry(Point,my_srid),
  CONSTRAINT point_pkey PRIMARY KEY (gid)
);

Simple Case

The related table contains a primary key field and one additional field (normally varchar), the relation table contains only two fields.

-- definition of related table
CREATE TABLE "lookupTable"`
(
  id integer NOT NULL,
  value character varying(45) NOT NULL,
  CONSTRAINT "lookupTable_pkey" PRIMARY KEY (id )
);

-- definition of relation table
CREATE TABLE "point_has_multiLookup"
(
  point_gid integer NOT NULL,
  "multiLookup_id" integer NOT NULL,
  CONSTRAINT "point_has_multiLookup_pkey" PRIMARY KEY (point_gid , "multiLookup_id" ),
  CONSTRAINT "fk_point_has_multiLookup1" FOREIGN KEY (point_gid)
      REFERENCES point (gid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "fk_point_has_multiLookup2" FOREIGN KEY ("multiLookup_id")
      REFERENCES "multiLookup" (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);

This relation is presented as a Ddn2mListWidget. The records of the related table are sorted alphabetically, any already chosen records are moved to the top when opening the mask.

widget for a simple n-2-m relation

Advanced Case

The related table contains more than two fields, the relation table contains only two fields.

-- definition of related table
CREATE TABLE "complexLookup"
(
  id integer NOT NULL,
  value1 character varying(45) NOT NULL,
  value2 character varying(45),
  value3 character varying(45),
  CONSTRAINT "complexLookup_pkey" PRIMARY KEY (id )
);

-- definition of relation table
CREATE TABLE "point_has_complexLookup"
(
  point_gid integer NOT NULL,
  "complexLookup_id" integer NOT NULL,
  CONSTRAINT "point_has_complexLookup_pkey" PRIMARY KEY (point_gid , "complexLookup_id" ),
  CONSTRAINT "fk_point_has_complexLookup1" FOREIGN KEY (point_gid)
      REFERENCES point (gid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "fk_point_has_complexLookup2" FOREIGN KEY ("complexLookup_id")
      REFERENCES "complexLookup" (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);

All fields of the related table are shown in a tree structure, the first field identifying the dataset. The records of the related table are sorted alphabetically, any already chosen records are moved to the top when opening the mask.

widget for an advanced n-2-m relation (DdN2mTreeWidget)

Relation Table with more than two Fields

Now, lets assume the relation table has not only the two primary key fields but additional fields.

-- definition of the relation table
CREATE TABLE "point_has_multiLookup_with_additional_field"
(
  point_gid integer NOT NULL,
  "multiLookup_id" integer NOT NULL,
  "dateField" date NOT NULL,
  CONSTRAINT "point_has_multiLookup_with_additional_field_pkey" PRIMARY KEY (point_gid , "multiLookup_id" ),
  CONSTRAINT "fk_point_has_multiLookup_waf1" FOREIGN KEY (point_gid)
      REFERENCES point (gid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "fk_point_has_multiLookup_waf2" FOREIGN KEY ("multiLookup_id")
      REFERENCES "multiLookup" (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);
-- related table is multiLookup

In this case the relation table is displayed in a table widget in which the related table's record is identifyied by the first field. The user can Add and Remove entries, a double click opens the data-driven input mask for the relation table.

widget for a multi-field n-2-m relation

Filter lengthy lists

Widgets for n-2-m relations can be filtered to only display the choices that match the filter. Any already selected choice is always displayed regardless of the filter. Enter your filter string in the text line and click the filter button or hit return. Unset the filter by clicking the Remove filter button.

User class of a table with an optional catalog

This widget must be added in a plugin either subclassing DataDrivenUi or using DdManager's addInputWidget() or replaceInputWidget() methods. Using the catalog table is optional.

-- definition of the catalog table
CREATE TABLE "catalog"`
(
  id integer NOT NULL,
  catalog character varying(45) NOT NULL,
  CONSTRAINT "catalog_pkey" PRIMARY KEY (id )
);

-- definition of the related table
CREATE TABLE "multi_lookup_with_catalog"
(
  id integer NOT NULL,
  service character varying(64) NOT NULL,
  catalog_id integer NOT NULL,
  CONSTRAINT "multi_lookup_with_catalog" PRIMARY KEY (id ),
  CONSTRAINT "fk_multi_lookup_with_catalog" FOREIGN KEY (point_catalog_id)
      REFERENCES catalog (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
);
-- definition of the relation table
CREATE TABLE "point_has_multiLookup_with_catalog"
(
  point_gid integer NOT NULL,
  "multiLookup_id" integer NOT NULL,
  amount integer NOT NULL default 1,
  description character varying(64),
  CONSTRAINT "point_has_multiLookup_with_catalog_pkey" PRIMARY KEY (point_gid , "multiLookup_id" ),
  CONSTRAINT "fk_point_has_multiLookup_wc1" FOREIGN KEY (point_gid)
      REFERENCES point (gid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "fk_point_has_multiLookup_wc2" FOREIGN KEY ("multiLookup_id")
      REFERENCES "multi_lookup_with_catalog" (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);

All possible values of the related table are displayed in a table widget, those included in the relation table are ticked and moved to the top upon opening (similar to the DdN2mTreeWidget). A double click opens the data-driven input mask for the relation table and ticks the record (if not already ticked). To make it easier for the user to find the values catalogs can be defined.

DdN2mCheckableTableWidget showing all possible entries in the relation table

DdN2mCheckableTableWidget showing only entries contained in catalog2