E-R Query Field

Entity-Relationship and Abstract Entity Path

In an Entity-Relationship diagram(ERD), a many-to-many relationship is a type of cardinality that refers to the relationship between two entities, say, A and B, where A may contain a parent instance for which there are many children in B and vice versa. The many-to-many relationship is also transitive. For example, if entity A has a many-to-many relationship with entity B, and entity B has a many-to-many relationship with entity C, then entity A and entity C also have a many-to-many relationship, which is an indirect many-to-many relationship.

Based on the transitivity of many-to-many relationships, the concept of Abstract Entity Path is proposed to describe this direct or indirect many-to-many relationship between entities. The abstract entity path uses all entities from one entity to another as nodes to describe the many-to-many relationship between any two entities. For example, the abstract entity path of entity A and entity B is [A, B], the abstract entity path of entity B and entity A is [B, A], and the abstract entity path of entity C and entity A is [C, B, A]. The abstract entity path contains all the information about the relationship between any two entities, so it is used to dynamically generate complex nested query statements.

GoooQo introduces the concept of abstract entity path and defines a label named entitypath to represent the relationship between entities. This label is used to query the fields in the query object for querying entity relationships.

Example 1. The entity path entitypath:"user,role", based on the predetermined table name format, can generate two entity table names t_user and t_role, one intermediate table name a_user_and_role, and two foreign key names user_id and role_id, and then generate the query statement:

SELECT * FROM t_user WHERE id
 IN (SELECT user_id FROM a_user_and_role WHERE role_id 
 IN (SELECT id FROM t_role [WHERE])
)

Example 2. The entity path entitypath:"user,role,perm" will generate:

SELECT * FROM t_user WHERE id
 IN (SELECT user_id FROM a_user_and_role WHERE role_id
 IN (SELECT role_id FROM a_role_and_perm WHERE perm_id
 IN (SELECT id FROM t_perm [WHERE])
))

Examples

The table t_menu has a column parent_id referring to the id column itself as a foreign key. The parent_id column is used to define the hierarchical parent-child relationship between menu items. The menus are assigned to the users as a system resource via a general RBAC model. Then the entity path from the menu to the user is: menu,perm,role,user, which is used to generate nested query statements.

import . "github.com/doytowin/goooqo/core"

type MenuEntity struct {
	IntId
	ParentId *int    `json:"parentId,omitempty"`
	Name     *string `json:"name,omitempty"`
}

type MenuQuery struct {
	PageQuery
	Id *int

	// many-to-one:
	// Query the submenus of a specific parent menu:
	// parent_id IN (SELECT id FROM t_menu [WHERE])
	Parent *MenuQuery `entitypath:"menu" localField:"ParentId"`

	// one-to-many:
	// Query the parent menu of a specific submenu:
	// id IN (SELECT parent_id FROM t_menu [WHERE])
	Children *MenuQuery `entitypath:"menu" foreignField:"ParentId"`

	/**
	many-to-many:
	Query the menus accessible to a specific user:
	id IN (SELECT menu_id FROM a_perm_and_menu WHERE perm_id
	   IN (SELECT perm_id FROM a_role_and_perm WHERE role_id
	   IN (SELECT role_id FROM a_user_and_role WHERE user_id 
	   IN (SELECT id FROM t_user [WHERE])
	））)*/
	User *UserQuery `entitypath:"menu,perm,role,user"`
}