E-R Query Field

Abstract Entity Path

In an entity-relationship diagram, a many-to-many relationship is used to represent the association between two entities. Many-to-many relationships are 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 entities A and 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 an Abstract Entity Path is proposed to describe this direct or indirect many-to-many relationship between entities. An abstract entity path describes the many-to-many relationship between any two entities by considering all entities from one entity to another as nodes. For example, the abstract entity path between entity A and entity B is [A, B]; the path between entity B and entity A is [B, A]; and the path between entity C and entity A is [C, B, A]. The abstract entity path contains all the information about the relationship between any two entities, thereby enabling the dynamic generation of complex nested query statements.

DoytoQuery introduces the concept of an Abstract Entity Path and defines the annotation @DomainPath to describe relationships between entities. This tag is used for fields in query objects that are intended to query entity relationships. For example, the entity path `entitypath:"user,role"` can, based on a predefined table name format, yield two entity table names t_user and t_role, an intermediate table name a_user_and_role, and two foreign key names user_id and role_id. This information is then used to generate a 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

The table t_menu has a column parent_id that references the id column itself as a foreign key. The parent_id column is used to define hierarchical parent-child relationships between menu items. Menus are assigned to users as system resources through a general RBAC model. Therefore, the entity path from a menu to a user is: menu, perm, role, user, which is used to generate nested query statements.

Nested Queries

DoytoQuery generates nested query statements for fields by parsing the @DomainPath annotation configured on the fields.

The annotation is defined as follows:

DomainPath.java

@Target(FIELD)
@Retention(RUNTIME)
public @interface DomainPath {
    /**
     * To describe how to route from the host domain to the target domain.
     *
     * @return paths array
     */
    String[] value();

    String localAlias() default  "t";

    /**
     * The field in this domain to maintain the relationship with the target domain.
     *
     * @return name of the local field
     */
    String localField() default "id";

    /**
     * The field in another domain to maintain the relationship with this domain.
     *
     * @return name of the foreign field
     */
    String foreignField() default "id";

    String foreignAlias() default "t1";
}

Simple Nested Queries

Assume a hierarchical menu table where a child menu's parent_id points to the parent menu's id. Using this, all parent menus can be queried with the following statement:

SELECT * FROM menu WHERE id IN (SELECT parent_id FROM menu)

To execute this query via DoytoQuery, you need to create the corresponding MenuQuery class and add a field for the query, configured with the @NestedQueries annotation:

@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class MenuQuery extends PageQuery {
    // many-to-one
    @DomainPath(value = "menu", localField = "parentId")
    private MenuQuery parent;

    // one-to-many
    @DomainPath(value = "menu", foreignField = "parentId")
    private MenuQuery children;

    @DomainPath({"menu", "perm", "role", "user"})
    private UserQuery user;

    private String nameLike;
    private Boolean valid;
}