0%

sample tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
table1: customers
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(100) | YES | | NULL | |
| last_name | varchar(100) | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+

table2: orders
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| order_date | date | YES | | NULL | |
| amount | decimal(8,2) | YES | | NULL | |
| customer_id | int(11) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+

如何關聯兩個table

1
2
3
4
5
6
7
8
9
10
可透過設定foreign key去關聯其他table
$ alter table orders add foreign key (customer_id) references customers(id);
****+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| order_date | date | YES | | NULL | |
| amount | decimal(8,2) | YES | | NULL | |
| customer_id | int(11) | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+----------------+

交集

圖片來源:https://www.akademiaarchitektait.pl/sql-joins/

Inner Join(顯示A/B兩個table交集的資料)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ select * from customers inner join orders where customers.id=orders.customer_id;
+----+------------+-----------+----------------+----+------------+--------+-------------+
| id | first_name | last_name | email | id | order_date | amount | customer_id |
+----+------------+-----------+----------------+----+------------+--------+-------------+
| 1 | Robin | Jackman | roj@gmail.com | 1 | 2001-10-12 | 99.12 | 1 |
| 2 | Taylor | Edward | taed@gmail.com | 2 | 2001-09-21 | 110.99 | 2 |
| 1 | Robin | Jackman | roj@gmail.com | 3 | 2001-10-13 | 12.19 | 1 |
| 3 | Vivian | Dickens | vidi@gmail.com | 4 | 2001-11-29 | 88.09 | 3 |
| 4 | Harley | Gilbert | hgi@gmail.com | 5 | 2001-11-11 | 205.01 | 4 |
+----+------------+-----------+----------------+----+------------+--------+-------------+

可以用group by再對join之後的table做整理運算
$ select first_name, last_name, SUM(amount) as total_amount from customers inner
join orders where customers.id=orders.customer_id group by customers.id;
+------------+-----------+--------------+
| first_name | last_name | total_amount |
+------------+-----------+--------------+
| Robin | Jackman | 111.31 |
| Taylor | Edward | 110.99 |
| Vivian | Dickens | 88.09 |
| Harley | Gilbert | 205.01 |
+------------+-----------+--------------+

Left Join(完整顯示A table的資料)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
- 先插入一個沒有order的人customer資料
$ insert into customers(first_name, last_name) values ("Phil", "Lee");

- 再做left join,即使沒有剛剛加進去的customer沒有order,也會被完整顯示出來。
$ select * from customers left join orders on customers.id=orders.customer_id;
+----+------------+-----------+----------------+------+------------+--------+-------------+
| id | first_name | last_name | email | id | order_date | amount | customer_id |
+----+------------+-----------+----------------+------+------------+--------+-------------+
| 1 | Robin | Jackman | roj@gmail.com | 1 | 2001-10-12 | 99.12 | 1 |
| 2 | Taylor | Edward | taed@gmail.com | 2 | 2001-09-21 | 110.99 | 2 |
| 1 | Robin | Jackman | roj@gmail.com | 3 | 2001-10-13 | 12.19 | 1 |
| 3 | Vivian | Dickens | vidi@gmail.com | 4 | 2001-11-29 | 88.09 | 3 |
| 4 | Harley | Gilbert | hgi@gmail.com | 5 | 2001-11-11 | 205.01 | 4 |
| 5 | Phil | Lee | NULL | NULL | NULL | NULL | NULL |
+----+------------+-----------+----------------+------+------------+--------+-------------+

- 用group by做整理
select CONCAT(first_name, " ", last_name) as full_name, SUM(amount) as total_amount
from customers
left join orders
on customers.id=orders.customer_id
group by customers.id;
+----------------+--------------+
| full_name | total_amount |
+----------------+--------------+
| Robin Jackman | 111.31 |
| Taylor Edward | 110.99 |
| Vivian Dickens | 88.09 |
| Harley Gilbert | 205.01 |
| Phil Lee | NULL |
+----------------+--------------+

- 如果是null,可以用case設條件,顯示為0
select first_name, last_name,
case
when SUM(amount) is Null then 0
else SUM(amount)
end as total_amount
from customers
left join orders
on customers.id=orders.customer_id
group by customers.id
order by total_amount desc;
+------------+-----------+--------------+
| first_name | last_name | total_amount |
+------------+-----------+--------------+
| Harley | Gilbert | 205.01 |
| Robin | Jackman | 111.31 |
| Taylor | Edward | 110.99 |
| Vivian | Dickens | 88.09 |
| Phil | Lee | 0 |
+------------+-----------+--------------+

- 如果要再更精簡一點,可以使用IFNULL
select first_name, last_name,
ifnull(SUM(amount), 0) total_amount
from customers
left join orders
on customers.id=orders.customer_id
group by customers.id
order by total_amount desc;
+------------+-----------+--------------+
| first_name | last_name | total_amount |
+------------+-----------+--------------+
| Harley | Gilbert | 205.01 |
| Robin | Jackman | 111.31 |
| Taylor | Edward | 110.99 |
| Vivian | Dickens | 88.09 |
| Phil | Lee | 0.00 |
+------------+-----------+--------------+

Right Join(完整顯示B table的資料)

基本上概念跟left join一樣,因為我們在orders這個table裡面設置了FK,所以每一筆order都會有對應的customer,如果我們使用right join,查到的結果會跟inner join一樣。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select first_name, last_name,
ifnull(SUM(amount), 0) total_amount
from customers
right join orders
on customers.id=orders.customer_id
group by customers.id
order by total_amount desc;
+------------+-----------+--------------+
| first_name | last_name | total_amount |
+------------+-----------+--------------+
| Harley | Gilbert | 205.01 |
| Robin | Jackman | 111.31 |
| Taylor | Edward | 110.99 |
| Vivian | Dickens | 88.09 |
+------------+-----------+--------------+

sample table-employee

1
2
3
4
5
6
7
8
9
10
11
12
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
| 1 | Robin | Jackman | Software Engineer | 5500 | 2001-10-12 | NULL |
| 2 | Taylor | Edward | Software Architect | 7200 | 2002-09-21 | NULL |
| 3 | Vivian | Dickens | Database Administrator | 6000 | 2012-08-29 | NULL |
| 4 | Harry | Clifford | Database Administrator | 6800 | 2015-12-10 | NULL |
| 5 | Eliza | Clifford | Software Engineer | 4750 | 1998-10-19 | NULL |
| 6 | Nancy | Newman | Software Engineer | 5100 | 2007-01-23 | NULL |
| 7 | Melinda | Clifford | Project Manager | 8500 | 2013-10-29 | NULL |
| 8 | Jack | Chan | Test Engineer | 6500 | 2018-09-07 | NULL |
| 9 | Harley | Gilbert | Software Architect | 8000 | 2000-07-17 | NULL |
+----+------------+-----------+------------------------+--------+---------------------

字串拼接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CONCAT
$ select CONCAT(first_name, ", ", last_name) as full_name from employee;
+-------------------+
| full_name |
+-------------------+
| Robin, Jackman |
| Taylor, Edward |
| Vivian, Dickens |
| Harry, Clifford |
| Eliza, Clifford |
| Nancy, Newman |
| Melinda, Clifford |
| Jack, Chan |
| Harley, Gilbert |
+-------------------+

CONCAT_WS
$ select CONCAT_WS("-", first_name, last_name, title) as employee_title fro
m employee;
+---------------------------------------+
| employee_title |
+---------------------------------------+
| Robin-Jackman-Software Engineer |
| Taylor-Edward-Software Architect |
| Vivian-Dickens-Database Administrator |
| Harry-Clifford-Database Administrator |
| Eliza-Clifford-Software Engineer |
| Nancy-Newman-Software Engineer |
| Melinda-Clifford-Project Manager |
| Jack-Chan-Test Engineer |
| Harley-Gilbert-Software Architect |
+---------------------------------------+

取某些字元

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
substring(column, 起始字元排序, 結束字元排序)
$ select CONCAT(first_name, " ", last_name, " was hired on ", substring(hire_date, 1, 4)) as info from employee;
+------------------------------------+
| info |
+------------------------------------+
| Robin Jackman was hired on 2001 |
| Taylor Edward was hired on 2002 |
| Vivian Dickens was hired on 2012 |
| Harry Clifford was hired on 2015 |
| Eliza Clifford was hired on 1998 |
| Nancy Newman was hired on 2007 |
| Melinda Clifford was hired on 2013 |
| Jack Chan was hired on 2018 |
| Harley Gilbert was hired on 2000 |
+------------------------------------+

替換某些字元

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
REPLACE(column, 欲替換的字元, 替換上的字元)
把software改成hardware
$ select CONCAT(first_name, " ", last_name) as full_name, REPLACE(title, "Soft", "Hard") as new_title from employee;
+------------------+------------------------+
| full_name | new_title |
+------------------+------------------------+
| Robin Jackman | Hardware Engineer |
| Taylor Edward | Hardware Architect |
| Vivian Dickens | Database Administrator |
| Harry Clifford | Database Administrator |
| Eliza Clifford | Hardware Engineer |
| Nancy Newman | Hardware Engineer |
| Melinda Clifford | Project Manager |
| Jack Chan | Test Engineer |
| Harley Gilbert | Hardware Architect |
+------------------+------------------------+

反轉字串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ select CONCAT(first_name, " ", last_name) as full_name, REVERSE(title) as
new_title from employee;

+------------------+------------------------+
| full_name | new_title |
+------------------+------------------------+
| Robin Jackman | reenignE erawtfoS |
| Taylor Edward | tcetihcrA erawtfoS |
| Vivian Dickens | rotartsinimdA esabataD |
| Harry Clifford | rotartsinimdA esabataD |
| Eliza Clifford | reenignE erawtfoS |
| Nancy Newman | reenignE erawtfoS |
| Melinda Clifford | reganaM tcejorP |
| Jack Chan | reenignE tseT |
| Harley Gilbert | tcetihcrA erawtfoS |
+------------------+------------------------+

字串長度

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ select char_length(CONCAT(first_name, last_name)) as fullname_length from
employee;
+-----------------+
| fullname_length |
+-----------------+
| 12 |
| 12 |
| 13 |
| 13 |
| 13 |
| 11 |
| 15 |
| 8 |
| 13 |
+-----------------+

改字串大小寫

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ select UPPER(first_name) as first_name from employee;
+------------+
| first_name |
+------------+
| ROBIN |
| TAYLOR |
| VIVIAN |
| HARRY |
| ELIZA |
| NANCY |
| MELINDA |
| JACK |
| HARLEY |
+------------+

安裝MySQL

因為我是Mac,所以選擇MySQL Community Server下載。

https://dev.mysql.com/downloads/

安裝完成之後會出現一個彈跳視窗,給你一個暫時的密碼,後面可以再修改。

1
A temporary password is generated for root@localhost: wj4U#ds.fQvM

啟動mysql

到我們mysql的安裝位置啟動。

1
2
$ cd usr/local/mysql/bin
$ ./mysql -u root -p

修改密碼

1
2
$ ALTER USER "root"@"localhost" IDENTIFIED by "new_password";
$ exit;

安裝Docker

可參考此影片:https://www.youtube.com/watch?v=f9jBaXbv4nA&list=PLfQqWeOCIH4BfWvgffq2-pIm9Mc8pf_OI&index=2

在docker上執行mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
https://hub.docker.com/_/mysql
$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:(mysql_version)
$ docker ps (see all running containers )
$ docker exec -it (container_name or its ID) sh(enter container shell)
$ mysql -u root -p (use this caontainer's password)

$ docker stop (container_name or its ID)
$ docker ps -a (can see all containers been closed)
$ docker rm (container_name or its ID)

掛載資料檔
1.將欲使用的資料檔路徑掛載進container(如果已經掛載過就可以省略此步驟)
$ docker run --name some-mysql -v /Users/phil/Downloads:/root -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7

2.用source指令導入檔案
$ source /root/file_name

Database command

1
2
3
4
5
$ create database (database_name);
$ show databases; (see all databases we have now)
$ use (database_name); (switch to another database)
$ select (database_name); (see which database we are using)
$ drop (database_name); (remove database)

Table command

1
2
3
4
5
6
7
8
$ create table table_name( column type, column type....);
$ create table if not exists table_name( column type, column type....);

$ show tables; (see all tables we have)
$ describe table_name; (see all contents of the table)
$ show columns from table_name; (same result as $ describe table_name)
$ drop table table_name; (remove the table)

Practice (create an employee table)

1
2
3
4
5
6
7
8
9
$ create table employees(
id INT AUTO_INCREMENT,
birth_date DATE,
gender ENUM("F","M"),
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
hire_date DATE),
PRIMARY_KEY(id);

Data Insert command

1
insert into table_name(column1_name, column2_name,...) values (column1_value, cloumn2_value,...)

學習Ruby on Rails的過程中,因為都透過model來跟資料庫溝通,常常忽略掉最根本的SQL語法,所以接下來會在開發app的過程中,繼續把這塊補起來。
(以下都以一拳超人的資料庫為範本)

查詢資料

  1. 查全部資料

    1
    2
    SELECT *
    FROM heroes;
  2. 查全部S級英雄的資料

    1
    2
    3
    SELECT *
    FROM heroes
    WHERE hero_level = 'S';
  3. 查全部S級女性英雄的資料

    1
    2
    3
    SELECT *
    FROM heroes
    WHERE hero_level = 'S' AND gender = 'F';
  4. 只查部分欄位

    1
    2
    3
    SELECT name, gender
    FROM heroes
    WHERE hero_level = 'S';
  5. 查空值的資料

    1
    2
    3
    SELECT *
    FROM heroes
    WHERE age IS NULL;
  6. 查擁有關鍵字的資料

    1
    2
    3
    SELECT *
    FROM heroes
    WHERE name LIKE '%背心%';
  7. 查某個區間的資料

    1
    2
    3
    4
    5
    6
    7
    8
    9
    #數字不需要引號
    SELECT *
    FROM heroes
    WHERE age >= 10 AND age <= 25;

    #可簡寫為
    SELECT *
    FROM heroes
    WHERE age BETWEEN 10 AND 25;
  8. 查所有 S 級跟 A 級的英雄

    1
    2
    3
    4
    5
    6
    7
    SELECT *
    FROM heroes
    WHERE hero_level = 'S' OR hero_level = 'A';

    SELECT *
    FROM heroes
    WHERE hero_level IN ('S', 'A');
  9. 查不符合條件的資料

    1
    2
    3
    4
    5
    SELECT *
    FROM heroes
    WHERE hero_level <> 'S';

    #<>為不等於的意思
  10. 查不是 S 級也不是 A 級的英雄

    1
    2
    3
    SELECT *
    FROM heroes
    WHERE hero_level NOT IN ('S','A')

    更新資料

  11. 更新單筆資料的某個欄位

    1
    2
    3
    UPDATE heroes
    SET age = 10
    WHERE id = 25
  12. 更新單筆資料的多個欄位

    1
    2
    3
    UPDATE heroes
    SET age = 10, hero_level = 'A', hero_rank = 5
    WHERE id = 25
  13. 更新全部資料的某個欄位

    1
    2
    UPDATE heroes
    SET age = age + 1

    刪除資料

刪除符合某個條件的所有資料

1
2
DELETE FROM heroes
WHERE hero_level = 'C';

類別(Class)與實體(Instance)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
class Lakers #類別
def self.all #類別方法
puts "defense!"
end
def shoot(shots) #實體方法
puts "nails a #{shots}!"
end
end

Lakers.all #呼叫類別方法

KCP = Lakers.new #Lakers_player為類別,KCP為實體
KCP.shoot "three-pointer"

#類別方法印出 defense!
#實體方法印出 nails a three-pointer!!

類別有繼承特性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
class Animal
def eat(foods)
puts "#{foods} 超好吃!"
end
end

class Cat < Animal #繼承Animal,也可說是分類在Animal下面
end

Amy = Cat.new
Amy.eat "小魚乾" #印出小魚乾 超好吃!

class Dog < Animal #繼承Animal,也可說是分類在Animal下面
end

Ryan = Dog.new
Ryan.eat "潔牙骨" #印出潔牙骨 超好吃!

物件初始化

實體會先吃到初始化的指令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class Lakers_player
def initialize
puts "let's go Lakers!"
end

def shoot(shots)
puts "makes a #{shots}!"
end
end

Davis = Lakers_player.new
Davis.shoot "10 foot jumpshot"

# 印出let's go Lakers!
# makes a 10 foot jumpshot!

實體變數(Instance Variable)

寫法為 @ 開頭的變數,可在實體中裡自由取用,而且每個實體之間互不相影響。在使用 new 方法製作實體的時候,也可以順便傳參數進去。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class Lakers
def initialize(name, position)
@name = name
@position = position
end

def introduction
puts "Hi, my name is #{@name}, I play #{@position}."
end
end

Lebron = Lakers.new("Lebron", "Small Forward")
Lebron.introduction

#印出 Hi, my name is Lebron, I play Small Forward.

取用實體變數的方法:

Ruby 的實體變數沒辦法直接從外部取用,否則會發生錯誤訊息:

1
2
3
4
Lebron = Lakers.new("Lebron", "Small Forward")
Lebron.position = "Center"

#undefined method `position=' for #<Lakers:0x00005648e0e018c8> (NoMethodError)

必須要將”.position=”這個方法先定義進去,才能取用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
class Lakers
def initialize(position)
@position = position
end

def position
return @position #return可省略,會回傳@position,這三行稱之getter
end

def position=(new_position) #會設定我們下的參數,稱之setter
@position = new_position
end

end

Lebron = Lakers.new("Small Forward")
Lebron.position = "Center" #原始寫法為 position=("Center"),“position=”是一個方法,後面的center是參數
puts Lebron.position

#印出Center

Ruby 有幫我們定義了三個方法來解決這件事,分別是 attr_reader、attr_writer 以及 attr_accessor。這三個方法分別會做出「讀取」、「設定」以及「讀取 + 設定」的方法,所以原來的有點囉嗦的寫法就可使用 attr_accessor 改寫成這樣:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
class Lakers
attr_accessor :position

def initialize(name, position)
@name = name
@position = position
end

def introduction
puts "Hi, my name is #{@name}, I play #{@position}."
end
end

Lebron = Lakers.new("Lebron", "Small Forward")
Lebron.position = "Center"
puts Lebron.introduction

#印出Hi, my name is Lebron, I play Center.

類別變數(Class Variable)

隨著類別活著

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
class Dog
@@count = 0 #類別變數

def initialize
@@count += 1 #x += y => x = x + y
end

def self.counter #類別方法
return @@count
end
end

5.times { Dog.new }
p Dog.counter

#印出5

開放類別(Open Class)

如果同時出現多個相同名字的類別,在ruby的世界裡面,並不會由後面的覆蓋掉前面的,而是相互融合。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class Lakers
def hi
puts "hi"
end
end

class Lakers
def yo
puts "yo"
end
end

Lebron = Lakers.new
Lebron.yo #印出yo
Lebron.hi #印出hi

ruby甚至可以改原本預設的類別,如string, integer..等

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class Integer
def +(n)
100
end
end

p 1 + 2 #原始寫法是1物件呼叫+方法帶入2這個參數 p 1.+(2),印出100

class String
def say_hi
puts "hello #{self}!"
end
end

puts "Phil".say_hi #印出 hello Phil!

方法的存取控制

類別的方法存取限制常見的主要有三種:public、protected 以及 private。

  • public: 一般沒有特別註明就是public,所有的人都可以直接存取。
  • private: 在類別內部才可以存取。
  • protected(非常少用): 差不多是在這兩者之間,比 private 寬鬆一些,但又沒有 public 那麼自在,protected 在同一個類別內或是同一個 package,或是繼承它的子類別可以自由取用,但如果不是的話則不可存取。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
class Human
def walk #public
puts "一天一萬步 醫生遠離我"
end

private
def eat
puts "yumyum"
end

protected
def sleep
puts "Zzz"
end
end

Phil = Human.new
Phil.walk #印出一天一萬步 醫生遠離我
Phil.eat #NoMethodError
Phil.sleep #NoMethodError

Phil.eat這段code,在ruby的世界裡,指的是發送了eat這個訊息(message)給Phil這個接收者(receiver),而Private用法的規定,就是不能有接收者,也就是前面不能有小數點符號。

也有一些方法可以繞過這個規定,只要把eat這個方法當作參數傳回去即可從外部呼叫這個方法:

1
2
3
Phil.send (:eat) #印出yumyum

#可動態執行方法

Action text 是rails 6之後新增加的編輯器功能,它整合了trix編輯器的內嵌圖片、comment、quote等功能,非常容易安裝使用,以下跟大家分享我的安裝及上傳內嵌圖片到s3的經驗。

安裝action text 及active storage

  1. 首先按照rails官網指示安裝action text

    1
    rails action_text:install
  2. 在application.js引入trix跟action text

    1
    2
    require("trix")
    require("@rails/actiontext")
  3. 因為action text需搭配active storage來夾帶附件及圖片,所以一併安裝,輸入以下指令會建立兩個table(active_storage_blobs and active_storage_attachments)。

    1
    2
    rails active_storage:install
    rails db:migrate
  4. 在安裝action text的同時,也同時新增了一個actiontext.scss,可以針對編輯器的css做更改,記得把它打包到stylesheet的pack裡面。

    1
    @import "./actiontext.scss";

    開始使用action text

  5. 在你要使用編輯器的model及欄位做設定。

    1
    2
    #project.rb
    has_rich_text :content
  6. 在view上把編輯器渲染出來。

    1
    <%= form.rich_text_area :content %>

  7. 如果是在development階段開發,active storage預設會走local的儲存方式(可見development的設定),附件都會存在本機,所以現在已經可以開始進行編輯及內嵌圖片囉!

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    #development.rb
    config.active_storage.service = :local

    #storage.yml
    test:
    service: Disk
    root: <%= Rails.root.join("tmp/storage") %>

    local:
    service: Disk
    root: <%= Rails.root.join("storage") %>
  8. 預設的編輯器在上傳圖片時,會有一組caption,如果不需要的話,可以在_blob.html裡面把它拿掉。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    <figure class="attachment attachment--<%= blob.representable? ? "preview" : "file" %> attachment--<%= blob.filename.extension %>">
    <% if blob.representable? %>
    <%= image_tag blob.representation(resize_to_limit: local_assigns[:in_gallery] ? [ 800, 600 ] : [ 1024, 768 ]) %>
    <% end %>

    <figcaption class="attachment__caption">
    <% if caption = blob.try(:caption) %>
    <%= caption %>
    <% else %>
    <span class="attachment__name"><%= blob.filename %></span>
    <span class="attachment__size"><%= number_to_human_size blob.byte_size %></span>
    <% end %>
    </figcaption>
    </figure>

    在production 環境時上傳內嵌圖片到S3

  9. 在AWS S3註冊一個帳號,接著申請一個bucket及credential_key,取得它的access_key跟secret_access_key之後,在storage.yml裡面新增amazon的儲存方式,把資料填進去(這裏我使用figaro把敏感資料存成環境變數使用)。

    1
    2
    3
    4
    5
    6
    7
    #storage.yml
    amazon:
    service: S3
    access_key_id: <%= ENV["aws_access_key_id"] %>
    secret_access_key: <%= ENV["aws_secret_access_key"] %>
    region: "your region"
    bucket: "your bucket name"
  10. 設定active storage在production環境時,走amazon的儲存方式。

    1
    2
    #production.rb
    config.active_storage.service = :amazon
  11. 在S3的bucket設定跨來源資源分享(CORS),授權我們domain跟AWS資料交換,

  12. 如此一來就可以上傳圖片到S3囉!

參考資料:https://pragmaticstudio.com/tutorials/using-active-storage-in-rails

在設計model的時候,會發現有時候model需要跟自己有關連,這次要紀錄的留言及回覆功能就是一個很好的例子,回覆基本上也是留言的一種,我們可以運用self-join這個方法,建立它們之間的關聯。

註:以下範例是基於募資網站的題目,功能設計是需要在募資專案的show頁面當中,user可以留言而提案者可以針對每則留言個別回覆,以下我就針對實作的內容分成conmment跟reply兩個部分跟大家分享。

Comment

  1. 建立comment model,parent_id是為了comment和reply之間的關聯設定(別忘了rails db:migrate)

    1
    rails g model Comment user:references project:references parent_id:integer content:text
  2. 回到project和user設定has many comment,另外也限定留言不得空白

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # app/models/project.rb
    has_many :comments

    # app/models/user.rb
    has_many :comments

    # app/models/comments.rb
    belongs_to :project
    belongs_to :user
    validates :content, presence :true
  3. 設定comment的路徑

    1
    2
    3
    resources :projects, shallow: true do
    resources :comments, shallow: true, only: [:new, :create, :destroy]
    end

    這邊我只需要三個路徑,並且destroy只需要comment的id就可以抓到它進行刪除,所以使用shallow: true可以遮蔽掉前面的project,避免太冗長的網址 (完整說明可以參考官方文件https://guides.rubyonrails.org/routing.html#shallow-nesting

  4. 在project的show頁面放入comment的form

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # view/projects/show.html.erb
    <% if user_signed_in? %>
    <%= form_with model: Comment.new, url: project_comments_path(@project) do |f| %>
    <div>
    <%= f.text_area :content, placeholder: '請留言' %>
    </div>
    <%= f.submit '送出' %>
    <% end %>
    <% end %>
  5. 建立comment的controller,然後新增一個create的方法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # app/controllers/comments_controller.rb
    def create
    @project = Project.find(params[:project_id])
    @comment = current_user.comments.new(comment_params)
    @comment.project = @project
    if @comment.save
    redirect_to project_path(@project)
    end
    end

    private
    def comment_params
    params.require(:comment).permit(:content, :parent_id)
    end

    這時候如果我們在頁面上新增留言就可以順利create,但除非我們去看log紀錄,否則在畫面上還看不出來,因為我們根本還沒把它渲染出來。

  6. 在project的show頁面把comment渲染出來。

    1
    <%= render @project.comments, comment: @comment %>
    1
    2
    3
    4
    5
    6
    #views/comments/_comment.html.erb
    <div>
    [<%= comment.user.name %>]
    <%= comment.created_at.strftime("%Y/%m/%d") %>
    <%= comment.content %>
    </div>

    如果覺得comment建立時間的預設格式太冗長,可以用strftime改成自己喜歡的格式。https://apidock.com/ruby/DateTime/strftime

Reply

我們並不會建立reply的model,而是透過self-join的方法利用我們一開始在comment的model裡面建立的parent_id欄位來關聯他們,基本上reply就是comment。

https://guides.rubyonrails.org/association_basics.html#self-joins

  1. 首先我們先把comment跟reply的關係設定好

    1
    2
    3
    4
    5
    6
    7
    class Comment < ApplicationRecord
    belongs_to :project
    belongs_to :user
    has_many :replies, class_name: 'Comment', foreign_key: :parent_id, dependent: :destroy

    validates :content, presence: :true
    end
  2. 接下來就讓我們來建立reply的form吧,如果comment有任何reply就把它們印出來。這邊使用remote:true代表我們要用ajax來執行非同步處理。

    1
    2
    3
    4
    5
    6
    7
    8
    #views/comments/_comment.html.erb
    <%= link_to '回覆', new_project_comment_path(@project, parent_id: comment.id), remote: true %>

    <% if comment.replies.any? %>
    <% comment.replies.each do |reply| %>
    <%= render 'comments/reply', reply: reply %>
    <% end %>
    <% end %>
  3. 建立_reply頁面

    1
    2
    3
    4
    5
    6
    #/comments/_reply.html.erb
    <div class='ml-5'>
    [<%= reply.user.name %>]
    <%= reply.created_at.strftime("%Y/%m/%d") %>
    <%= reply.content %>
    </div>
  4. 建立reply的form,這邊也放了一個parent_id的隱藏欄位,透過comment的new action來把它填入表單

    1
    2
    3
    4
    5
    6
    7
    8
    <%= form_with model: [@project, @comment] do |f| %>
    <%= f.hidden_field :parent_id %>
    <div>
    <%= f.text_area :content, placeholder: '請回覆', class:'border-2' %>
    </div>
    <%= f.submit '送出回覆' %>
    <%= link_to '取消', project_path(@project) %>
    <% end %>
  5. 生成一個new方法,填入parent_id到隱藏欄位裡面

    1
    2
    3
    4
    5
    #comments_controller.eb
    def new
    @project = Project.find(params[:project_id])
    @comment = current_user.comments.new(parent_id: params[:parent_id])
    end
  6. 接下來我們要使用ajax方式生成回覆的資料,先建一個空的div容器,待會塞資料給它。這邊的動態id是為了後續能用JS抓到指定的那一個form。

    1
    2
    #app/views/_comment.html.erb
    <div id="reply-form-<%= comment.id %>"></div>
  7. 新建一個new.js.erb的檔案,當點擊新增回覆的按鈕時會找到它,把reply_form的資料渲染在這個位置。
    (j的用法:https://api.rubyonrails.org/classes/ActionView/Helpers/JavaScriptHelper.html#method-i-escape_javascript)

    1
    2
    #comments/new.js.erb
    document.querySelector("#reply-form-<%= @comment.parent_id %>").innerHTML = ("<%= j render 'reply_form', comment: @comment %>")
  8. 最後填完回覆內容按下送出,就可以把這筆資料存進資料庫了。

題目:找到中間值的index

Example:

1
2
3
4
5
6
7
8
Test.describe("Basic Tests") do
Test.assert_equals(gimme([2, 3, 1]), 0, 'Finds the index of middle element')
Test.assert_equals(gimme([5, 10, 14]), 1, 'Finds the index of middle element')
Test.assert_equals(gimme([1, 3, 4]), 1, 'Finds the index of middle element')
Test.assert_equals(gimme([15, 10, 14]), 2, 'Finds the index of middle element')
Test.assert_equals(gimme([-0.410, -23, 4]), 0, 'Finds the index of middle element(Negative numbers)')
Test.assert_equals(gimme([-15, -10, 14]), 1, 'Finds the index of middle element (Negative numbers)')
end

解題方向:

  1. 我們必須先找到哪個數字是中間值,所以把這個陣列按照順序排列,因為題目都是只有三個元素,所以我就取第二個,然後給他一個變數以便後面取用。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    def gimme(arr)
    middle_element = (arr.sort)[1]
    end

    =>
    Finds the index of middle element - Expected: 0, instead got: 2
    Finds the index of middle element - Expected: 1, instead got: 10
    Finds the index of middle element - Expected: 1, instead got: 3
    Finds the index of middle element - Expected: 2, instead got: 14
    Finds the index of middle element(Negative numbers) - Expected: 0, instead got: -0.41
    Finds the index of middle element (Negative numbers) - Expected: 1, instead got: -10
  2. 接下來就是要找到這個值在原本的陣列中的位置

    1
    2
    3
    4
    def gimme(arr)
    middle_element = (arr.sort)[1]
    arr.index(middle_element)
    end

題目:將駝峰式名稱拆開(好難翻啊…還是直接看範例吧!)

Example:

1
2
Test.assert_equals(solution('camelCasing'), 'camel Casing')
Test.assert_equals(solution('camelCasingTest'), 'camel Casing Test')

解題方向:

  1. 想到要拆字串,心中有幾個常用的方法浮現,chars會將它拆成個別字元,所以不是我要的,而split或許是個選項,接下來的問題就是如何找到大寫字母來做拆解條件?或許可以用常規標示法來做,於是就來直接餵狗(google)啦!我下的關鍵字是”split capital case ruby”,很幸運的在第一頁就找到解法@@
    https://stackoverflow.com/questions/3997516/how-to-split-a-camelcase-string-in-its-substrings-in-ruby

    1
    2
    3
    4
    5
    def solution(string)
    string.split(/(?=[A-Z])/)
    end

    =>Expected: "camel Casing", instead got: ["camel", "Casing"]
  2. 第一步完成將駝峰拆掉了,接下來是如何組回去字串,並且在中間加上空格。刷過幾次codewar之後,對陣列轉字串應該就不陌生了,join是大家的好朋友~我就用它來結束這一回合吧!

    1
    2
    3
    def solution(string)
    string.split(/(?=[A-Z])/).join(" ")
    end

題目:判斷數字是否為平方

1
2
3
4
5
6
7
8
9
10
describe "is_square" do
it "should work for some examples" do
Test.assert_equals (is_square (-1)), false, "-1 is not a perfect square"
Test.assert_equals (is_square 0), true, "0 is a perfect square (0 * 0)"
Test.assert_equals (is_square 3), false, "3 is not a perfect square"
Test.assert_equals (is_square 4), true, "4 is a perfect square (2 * 2)"
Test.assert_equals (is_square 25), true, "25 is a perfect square (5 * 5)"
Test.assert_equals (is_square 26), false, "26 is not a perfect square"
end
end

解題方向:

先排除掉負值,如果是平方,開根號會是整數,所以除以1會等於0,反之則否。所以直接將參數開根號去除以1去餘數0.

1
2
3
4
def is_square(x)
#x等於0,或開根號後得到整數
x >= 0 && Math.sqrt(x) % 1 == 0
end