Requirement scenarios:

Articles articles: id user_id title content created_at posts: Id (added) user_id(id) title(created) content(created) created_at User_id user_id article_id Content created_at User_id user_id user_id user_id user_id user_id User_id User_id Id (Added) Name (user name) Avatar created_AT (creation time)Copy the code

Select * from articles where id, title, publication time, user name, user profile picture, etc. SELECT a.id, a.user_id, a.title, a.created_at, u.name, u.avatar FROM articles AS a, users AS u WHERE a.user_id=u.id ORDER BY a.created_at DESC

[{"id": 1."userId": 1."title": "Article Title 1"."createdAt": "The 2020-02-03 11:00:00"."name": "User 1"."avatar": "Avatar address 1"},... ]Copy the code

Post id, post title, post time, user name, user profile picture implementation (two tables associated query) : SELECT p.id, p.user_id, p.title, p.created_at, u.name, u.avatar FROM posts AS p, users AS u WHERE p.user_id=u.id ORDER BY p.created_at DESC

[{"id": 1."userId": 1."title": "Post Title 1"."createdAt": "The 2020-02-03 11:00:00"."name": "User 1"."avatar": "Avatar address 1"},... ]Copy the code

Latest post comment list interface /latestComments

Requirements: Sort by comment time, comment content, comment time, commenter name, commenter avatar, article title implementation (three table associated query) : SELECT c.id, c.article_id, c.user_id, c.content, c.created_at, u.name, u.avatar, a.title FROM comments AS c, users AS u, articles AS a WHERE c.user_id=u.id AND c.article_id=a.id ORDER BY c.created_at DESC

[{"id": 1."artileId": 1."userId": 1."content": "Comment 1"."createdAt": "The 2020-02-03 11:00:00"."name": "User 1"."avatar": "Avatar address 1",
        'title': "Headline 1"},... ]Copy the code

Procedure for Connecting a Client to a Server interface (Android Example)

// Article list interface: Create Acticle model
public class Article {
    public Integer id;   
    public Integer userId;   
    public String title;    
    public String name;    
    public String avatar;
    public String createdAt;
}

// Post list interface: Create a Post model
public class Post {
    public Integer id;   
    public Integer userId;   
    public String title;    
    public String name;    
    public String avatar;
    public String createdAt;
}

// Update Comment interface: Create Comment model
public class Comment {
    public Integer id;   
    public Integer articleId;
    public Integer userId;
    public String content;   
    public String createdAt;
    public String name;  
    public String avatar;
    public String title;
}
Copy the code

New demand

Added the user level function. Added the user level field in the preceding three interfaces

Server-side program development process: Step 1: Add level field to user table Step 2: SELECT a.id, A.usser_id, A.twele, A.created_at, U.name, u.atar, u.level FROM articles AS a, users AS u WHERE a.user_id=u.id ORDER BY a.created_at DESC SELECT p.id, p.user_id, p.title, p.created_at, u.name, u.avatar, u.level FROM posts AS p, users AS u WHERE p.user_id=u.id ORDER BY p.created_at DESC SELECT c.id, c.article_id, c.user_id, c.content, c.created_at, u.name, u.avatar, u.level, a.title FROM comments AS c, users AS u, articles AS a WHERE c.user_id=u.id AND c.article_id=a.id ORDER BY c.created_at DESC

Client development process: find Article, Post, Comment model, add level attribute;

Consider: adding a level field to the Users table causes the server to modify 3 SQL and the client to modify 3 models?

The right approach < pseudocode >

$articles = SELECT * FROM articles ORDER BY created_at DESC;// Get the user ID for the article
$userIds = [];
for $acticle in $articles {
    $userIds[] = $acticle['user_id']
}
$userIdsIn = implode(', ', $userIds)
// Query the user table by user ID
$users = SELECT * FROM users  WHERE `id` IN ($userIdsIn)

$usersMap = new map;
for $user in $users {
    $usersMap[$user['id']] = $user;
}
// Merge user information into the corresponding article
for $acticle in $articles {
    $article['user'] = $usersMap[$acticle['user_id'[]} This is written to avoid the confusion of nested queries: it took so much effort and so many lines of code to implement1Join table SQL functionality, the gain is not worth the loss.// Return the format[{"id":1."userId":1."title":"Article Title 1"."content":"Article Content 1"."createdAt":"The 2020-02-03 11:00:00"."user": {"id":1."name":"User 1"."avatar":"Avatar address 1"."createdAt":"The 2020-02-03 11:00:00",}},... ] /posts [{"id":1."userId":1."title":"Post Title 1"."content":"Post Content 1"."createdAt":"The 2020-02-03 11:00:00"."user": {"id":1."name":"User 1"."avatar":"Avatar address 1"."createdAt":"The 2020-02-03 11:00:00",}},... ] Update comments list interface: /latestComments [{"id":1."userId":1."articleId":1."content":"Comments 1"."createdAt":"The 2020-02-03 11:00:00"."user": {"id":1."name":"User 1"."avatar":"Avatar address 1"."createdAt":"The 2020-02-03 11:00:00",},"article": {"id":1."userId":1."title":"Article Title 1"."content":"Article Content 1"."user":null."createdAt":"The 2020-02-03 11:00:00",}},... ]Copy the code

Client Model (Android example)

Public class User {public Integer id; public String name; public String avatar; } public class Article {public Integer id; public String title; public String content; public String createdAt; public User user; Public class Post {public Integer id; public String title; public String content; public String createdAt; public User user; Public class Comment {public Integer id; public Integer userId; public String content; public String createdAt; public User user; // User model public Article Article; // Article model}Copy the code

Realize the function of increasing user level

Server program development process: add level field in User table, find User model, add level object attribute; Client development process: find User model, add level object attributes;Copy the code

What is the ORM?

Object Relational Mapping (ORM or O/R Mapping) is a programming technique used to transform data between different types of systems in object-oriented programming.

ORM is a programming technique used in object-oriented programming to maintain mappings between objects during data conversion between different types of systems.

Key words: different types of systems; Data conversion; Maintain mappings between objects;

So ORM is the deployment of EloquentORM in PHP, GROM in GoLand, and Hibernate in Java. According to the definition of ORM, ORM emphasizes that the correct object mapping relationship should be maintained when different types of system data are converted. Different types of system are not only the data of the database system and the data of the application program, but also the data of the application program and the data of the client side. Most people’s understanding of ORM is that they do not need to write SQL by hand. Through the example above, they can write interfaces that conform to ORM specifications without ORM framework.

Think: Why keep this object mapping?

What are object relationships?

Explanation: one-to-one, one-to-many, many-to-many analysis: Acticle and user are one-to-one relationship, one article corresponds to one user; User and Acticle is a one-to-many relationship, a user can write more than one article, many online articles explained very clearly, here is not too tired to describe

// User article list interface
{
    "id":1."name":"User 1"."avatar":"Avatar address 1"."createdAt":"The 2020-02-03 11:00:00"."articles":[
        {
            "id":1."userId":1."title":"Article Title 1"."content":"Article Content 1"."user":null."createdAt":"The 2020-02-03 11:00:00",}... ] } Client model/ / User model
public class User {
    public Integer id;   
    public String name;    
    public String avatar;
    
    public ArrayList<Article> articles; // The article model array
}
Copy the code

Why use the ORM framework

It takes more than a dozen lines of code to implement the SQL function of 1 linked table, and the latest comment interface 3 associated tables, which is more troublesome to write. ORM framework can simplify the process

Var articles [] model.article db.preload ("User").find (&articles) SELECT * FROM 'users' WHERE' users'. 'deleted_at' IS NULL SELECT * FROM 'users' WHERE' users'. 'deleted_at' IS NULL IS NULL AND ((' id 'IN (1,2))) var comments [] model.ment Db.preload ("User").preload ("Article").find (&comments) // SELECT * FROM 'comments' WHERE' comments'. 'deleted_at' IS NULL SELECT * FROM 'users' WHERE' users'. 'deleted_at' IS NULL AND ((' id 'IN (1,2))) SELECT * FROM' articles' 'articles'.' deleted_at 'IS NULL AND ((' id' IN (1,2))Copy the code

Add a slightly more complex example to deepen your understanding:

// article/:id var article model db.Preload("User").Preload("Comments").Preload("Comments.User").Find(&article, SELECT * FROM 'articles' WHERE'. 'deleted_at' IS NULL AND (('. 'id' = 1)) SELECT * FROM 'articles' WHERE'. '= 1 `users` WHERE `users`.`deleted_at` IS NULL AND ((`id` IN (1))) SELECT * FROM `comments` WHERE `comments`.`deleted_at` IS SELECT * FROM 'users' WHERE' users'. 'deleted_at' IS NULL AND (' id 'IN (1)) {" id ": 1," userId ": 1," title ":" post title 1 ", "content" : "article 1", "createdAt" : "the 2020-02-03 11:00:00", "user" : {" id ": 1, "Name" : "1" user, "avatar" : "avatar address 1", "articles", null, "createdAt" : "the 2020-02-03 11:00:00,"}, "comments" : [{" id ": 1, "UserId" : 1, "articleId" : 1, "content" : "1" comment, "article", null, "createdAt" : "the 2020-02-03 11:00:00", "user" : {" id ": 1, "Name" : "1" user, "avatar" : "head address 1", "articles", null, "createdAt" : "the 2020-02-03 11:00:00,"}},... Public class Article {public Integer id; public String title; public String content; public String createdAt; public ArrayList<Comment> comments; // Comment model array public User User; // User model}Copy the code

The benefits of code compliance with the ORM specification

First, improve the efficiency of server development, using ORM framework without handwritten inefficient SQL statements; 2. Improve the docking efficiency of the client. The client model corresponds to the server model one by one. Third, reduce the cost of communication between the client and the server, and discuss business on the same model, which is not easy to cause ambiguity; 4. Generate the corresponding client model file from the server model, refer to Apache Thrift and gRPC. Five, the use of ORM framework, avoid associated query, slow query optimization for single table SQL than multiple table associated SQL is simple; 6. ORM frameworks implement SQL precompilation to avoid SQL injection; SQL > select * from 'MySQL' where SQL > = 'MySQL' > = 'MySQL' >Copy the code

Afterword.

Mysql > SELECT * FROM (SELECT * FROM); mysql > SELECT * FROM (SELECT * FROM); SELECT NOT(content) FROM articles SELECT NOT(content) FROM articles Add articles_id to articles and load articles when needed.

Problem 2: Some interfaces do not want to expose all fields in a table, but SELECT * FROM cannot be filtered. Solution: Add the interface data filtering function to the application program to filter the fields that you do not want to expose by setting field parameters.

Question 3: In a discussion, someone asked me how to use ORM for statistical business. I also wrote SQL for statistical business. ORM refers to object mapping, which has nothing to do with statistical business.