Sử dụng Array trong Postgres để thay thế cho bảng trung gian
White

Quốc Cường viết ngày 23/10/2015

Thông thường, khi sử dung đến các quan hệ 1: nhiều hoặc nhiều: nhiều trong bảng quan hệ để có thể truy vấn ta cần có bảng trung gian ở giữa. Với việc sử dụng kiểu Array trong Postgres bạn có thể loại bỏ được bảng trung gian này. Demo nhé :
Bạn có 2 bảng như sau

  1. Users
    • id - serial - Primary Key
    • username - character varying[255]
    • email - character varying[255]
    • jobs - integer[] << Array alt text
  2. Jobs
    • id - serial - Primary Key
    • name - character varying[255]
    • description - character varying[255] alt text

Để có thể kết nối dữ liệu giữa 2 bảng :

SELECT users.* , array_to_json(array_agg(jobs.*)) as detail_jobs FROM users
LEFT JOIN jobs ON jobs.id = ANY(users.jobs)
GROUP BY users.id;

Kết quả bạn nhận được :
alt text

Ta có thể chọn định danh tên côt để có 1 mảng trả về ví dụ :

SELECT users.* , array_to_json(array_agg(jobs.name)) as detail_jobs FROM users
LEFT JOIN jobs ON jobs.id = ANY(users.jobs)
GROUP BY users.id;

alt text

Thay vì sử dụng ANY bạn có thể sử dụng cú pháp IN và unnest

SELECT users.* , array_to_json(array_agg(jobs.*)) as detail_jobs FROM users
LEFT JOIN jobs ON jobs.id IN (SELECT unnest(users.jobs))
GROUP BY users.id;

Bạn có thể thêm giá trị vào trong Array bằng cú pháp

WITH u AS  (
  SELECT array_append( users.jobs, 3 ) AS jobs FROM users WHERE id = 1
)
UPDATE users SET jobs = u.jobs FROM u WHERE users.id = 1;

Bạn cũng có thể xóa 1 jobs khỏi Array bằng cú pháp

WITH new_jobs AS (
  SELECT array_agg( jobs.job ) AS jobs FROM (
    SELECT unnest( users.jobs ) AS job FROM users WHERE id = 1
  ) jobs WHERE jobs.job != 3
)

UPDATE users SET jobs = new_jobs.jobs FROM new_jobs WHERE id = 1;

link tham khảo Array

Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

White

Quốc Cường

12 bài viết.
66 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
White
31 6
Một câu hỏi thường gặp của lập trình viên có kinh nhiệm với SQL mới chuyển sang Mongo là làm thế nào để có thể model được quan hệ 1 : nhiều. Có rất...
Quốc Cường viết hơn 2 năm trước
31 6
White
25 7
Phát triển ứng dụng trên local tương đối đơn giản. Bạn chỉ việc cd vào thư mục , gõ câu lệnh node app.js , ứng dụng đã hoạt động. Mọi thứ sẽ trở n...
Quốc Cường viết hơn 2 năm trước
25 7
White
21 4
REST là kiến trúc phần mềm phổ biến nhất hiện nay trên internet. Thực tế khi đọc bài viết về REST các bạn sẽ thấy nó hơi bị mơ hồ khó hiểu. REST ...
Quốc Cường viết hơn 2 năm trước
21 4
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


White
{{userFollowed ? 'Following' : 'Follow'}}
12 bài viết.
66 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!