[Jquery] BootMyweb (2) - 카테고리 처리, sql join

 

BootMyWeb 카테고리

판매자 매니저(Sales Manager)

• 카테고리는 난이도가 높은 작업 (난이도 최상)
• 1단 카테고리는 1:N 처리
• 2, 3단 카테고리는 self join - 트리구조로 self join

카테고리 SQL

1. Product_category 테이블 생성

## https://taiyakee.tistory.com/82
SELECT @@autocommit;
SET AUTOCOMMIT = 0; #해제

CREATE TABLE PRODUCT_CATEGORY(
	CATEGORY_ID INT PRIMARY KEY AUTO_INCREMENT,
    GROUP_ID VARCHAR(10),
    CATEGORY_LV INT, ##1,2,3
    CATEGORY_NM VARCHAR(100), ##대분류중분류소분류
    CATEGORY_DETAIL_LV INT, ##ORDER순서
    CATEGORY_DETAIL_NM VARCHAR(100), ##이름
    CATEGORY_PARENT_LV INT , ##1,2,3에 대한 부모컬럼
    CATEGORY_DETAIL_PARENT_LV INT ##ORDER순서에 대한 부모컬럼
);

COMMIT;

• ProductVO의 prod_category = group_id + category_id

 2. 더미 데이터 삽입

Category_lv: 대(1), 중(2), 소(3)
Category_detail_lv: order로 같은 분류 중에서도 순서를 나타냄

Category_parent_lv: 대(0), 중(1), 소(2)
Category_deatil_parent_lv: Category_parent_lv이 갖고있는 디테일 레벨

 3. Self Join 이용한 SQL

순서

• self조인 소 ▷ 중 ▷ 대 순서로 연결

데이터 확인

select * from product where prod_writer = 'admin' order by prod_id desc;
select * from product_category;
# 등록화면에서 처음 가져올 때
SELECT * FROM PRODUCT_CATEGORY;
# 카테고리 레벨이 1이면 대분류
SELECT * FROM PRODUCT_CATEGORY WHERE CATEGORY_LV = 1;
# 두번째 가져올 때 (GROUP_ID, CATEGORY_PARENT_LV전달)
SELECT * FROM PRODUCT_CATEGORY WHERE GROUP_ID = 'A' AND CATEGORY_PARENT_LV = 1 AND CATEGORY_DETAIL_PARENT_LV = 1;
# 세번째 가져올 때  (GROUP_ID, CATEGORY_PARENT_LV전달)
SELECT * FROM PRODUCT_CATEGORY WHERE GROUP_ID = 'A' AND CATEGORY_PARENT_LV = 2 AND CATEGORY_DETAIL_PARENT_LV = 1;
SELECT * FROM PRODUCT_CATEGORY WHERE GROUP_ID = 'A' AND CATEGORY_PARENT_LV = 2 AND CATEGORY_DETAIL_PARENT_LV = 2;

• self join 할 때 소분류의 카테고리 페어런츠 레벨과 중분류의 카테고리 레벨이 같아야 하고
• 소분류의 카테고리 디테일 페어런츠 레벨과 중분류의 카테고리 디테일 레벨이 같아야 하며
• 가장 넓은 조건으로 소분류와 중분류의 그룹 아이디가 같아야 함

SELECT
	   #A1.*,
       #A2.CATEGORY_DETAIL_NM,
       #A3.CATEGORY_DETAIL_NM,
	   #CONCAT(A1.group_ID, A1.CATEGORY_ID, A1.category_LV,  A1.category_detail_LV) AS CATEGORY_KEY, ##카테고리 키
       CONCAT(A1.group_ID, A1.CATEGORY_ID) AS CATEGORY_KEY, ##카테고리 키 (그룹 + 키 형태로 카테고리분류)
 	   CASE A1.category_parent_LV
 	        WHEN 0 THEN A1.category_detail_NM
 			WHEN 1 THEN CONCAT(A2.category_detail_NM,' > ', A1.category_detail_NM)
 			WHEN 2 THEN CONCAT(A3.category_detail_NM, ' > ', A2.category_detail_NM,' > ', A1.category_detail_NM)
 	   END as CATEGORY_NAV
FROM PRODUCT_CATEGORY A1
LEFT OUTER JOIN PRODUCT_CATEGORY A2
ON A1.CATEGORY_PARENT_LV = A2.CATEGORY_LV AND A1.CATEGORY_DETAIL_PARENT_LV = A2.CATEGORY_DETAIL_LV AND A1.GROUP_ID = A2.GROUP_ID
LEFT OUTER JOIN PRODUCT_CATEGORY A3
ON A2.CATEGORY_PARENT_LV = A3.CATEGORY_LV AND A2.CATEGORY_DETAIL_PARENT_LV = A3.CATEGORY_DETAIL_LV
ORDER BY CATEGORY_NAV ASC;

• 추가적인 조건으로 카테고리 키 필요?

SQL 테이블 확인 (N : 1)

 

---

카테고리 처리 순서

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class CategoryVO {

	private int category_id;
	private String group_id;
	private int category_lv;
	private String category_nm;
	private int category_detail_lv;
	private String category_detail_nm;
	private int category_parent_lv;
	private int category_detail_parent_lv;
	
	//다 String으로 가도 됨, 나중에 어차피 String으로 변환해서 사용
}

1. 등록화면에서 비동기 데이터로드 - 대분류

<script th:inline="javascript">
	//화면이 로드가 일어난 후에 실행 (카테고리 데이터)
	//basic layout에 보면 jquery를 가지고 오도록 되어있기때문에 사용가능
	$(document).ready(function () {
		$.ajax({
			url: "../getCategory",
			type: "get",
			success: function (result) {
				console.log(result)
				var str = "";
				str += '<ul class="categoryList" style="position: relative;" onclick="getCategory_List(event);" >';

				//반복문 돌리면서 li 태그 넣기
				result.forEach(function (item, index) {
					str += '<li><a href="#" data-set=' + JSON.stringify(item) + '>' + item.category_detail_nm + '</a></li>';
				})

				str += '</ul>';
				$(".categoryListWrap").append(str); //자식으로 추가
			},
			error: function (err) {
				alert("카테고리 조회에 실패했습니다. 담당자에게 문의하세요.")
			}
		})

	});
</script>

//대분류 카테고리 요청
	@Autowired
	private ProductService productService;
	
	@GetMapping("/getCategory")
	public List<CategoryVO> getCategory() { //무조건 카테고리 레벨=1인 데이터가 restAPI가 줄 것은 없음
		return productService.getCategory();
	}

<!-- 카테고리 대분류 -->
  	<select id="getCategory" resultType="CategoryVO">
  		select * from PRODUCT_CATEGORY
  		where category_lv = 1
  	</select>

 

• 컬럼명이 다 나오면 안되므로 map으로 처리
• 화면에서 쓰는 이름 따로 DB에서 쓰는 이름 따로

2. 누르면 두세번째 카테고리 조회

/* 카테고리 */
function getCategory_List(e) {
	e.preventDefault(); //a태그니까 고유이벤트중지
	if( e.target.tagName != 'A') return; //a태그가 아니면 태그검증
	var obj = $(e.target).data("set"); //(클릭한 대상에 jquery의 data를 쓰면) 데이터셋을 가져옴, js에서는 dataSet 사용했음

	//토글색처리 - css 처리
	$(e.currentTarget).find("a").removeClass("sub_menu_select");
	$(e.target).addClass("sub_menu_select");
	//태그처리
	if(obj.category_lv == 1 || obj.category_lv == 2) { //3단 카테고리니까 1, 2까지만 클릭하면 됨
		console.log('1lv');
		$().loading(); //로딩
		$(e.currentTarget).category_remove(); //이전 카테고리삭제

		//////////////////////////////////////////////////
		//비동기콜백에서 category_create() 호출
		//비동기호출후 category_set() 호출
		//category_create(); //다음 카테고리생성
		
		console.log(obj);
		$.ajax({
			url: "../getCategoryChild/" + obj.group_id + "/" + obj.category_lv + "/" + obj.category_detail_lv,
			type: "get",
			success: function(result) {
			console.log(result);
			category_create(result);
			},
			error: function(err) {
				alert("카테고리 조회에 실패했습니다. 관리자에게 문의해주세요.");
			}
		})
		//////////////////////////////////////////////////
	}
	
	//0220 17:10
	//카테고리 키값 처리 (선택한 값의 group_id, category_pk를 처리)
	$(e.target).category_set();
	
}

//카테고리세팅
$.fn.category_set = function() {
	var category_id = this.data("set").category_id;
	var group_id = this.data("set").group_id;
	$("input[name='prod_category']").val(group_id + category_id ); //name이 prod_category인 곳에 추가
}
//이전카테고리 삭제JS
//1번 분류 다 선택하고, 2번 누르면 기존에 눌렸던거 다 없어져야 하니까
$.fn.category_remove = function() {
	while(this.next().length != 0) {
		$(this).next().remove();
	}
}
//다음카테고리 생성JS
function category_create(data) {

	//예시데이터
	/*var data = [
	 {category_lv: 2, group_id: 'B', category_detail_nm: '값선택', category_detail_parent_nm: '값선택'},
	 {category_lv: 2, group_id: 'B', category_detail_nm: '값선택', category_detail_parent_nm: '값선택'},
	 {category_lv: 2, group_id: 'B', category_detail_nm: '값선택', category_detail_parent_nm: '값선택'}
  ];*/

	var category = "";
	category += '<ul class="categoryList" style="position: relative;" onclick="getCategory_List(event);" >';
	data.forEach(function(result, index) {
		category += '<li><a href="#" data-set='+ JSON.stringify(result) +'>'+ result.category_detail_nm +'</a></li>';
	});
	category += '</ul>';

	$(".categoryListWrap").append(category);
}

특정 text 찾기: ctrl + h

---

N : 1 vs 1 : N 기본

N : 1

• N:1 에서 N쪽에 처리할 데이터를 붙임

1 : N

• 1:N 쪽은 N쪽을 list 처리

결과과

전체 코드

CREATE TABLE MEMO(
	MNO INT PRIMARY KEY AUTO_INCREMENT,
  WRITER VARCHAR(50) NOT NULL,
	MEMO VARCHAR(200) NOT NULL
);

insert into MEMO(writer, memo) values('aaa', 'aaa');
insert into MEMO(writer, memo) values('aaa', 'bbb');

CREATE TABLE USERS(
	ID VARCHAR(50) PRIMARY KEY,
    PW VARCHAR(50) NOT NULL,
    NAME VARCHAR(50),
    EMAIL VARCHAR(50) UNIQUE
);

insert into users(ID, pw, name, email) values('aaa', '1234', '홍길동', 'aaa@naver.com');

select * from memo;
select * from users;

## n : 1의 조인
select * from memo m left outer	join users u on m.WRITER = u.id;

## 1 : n의 조인
select * from users u left outer join memo m on u.id = m.writer;

## 1번 방법
select * from product as p
left outer join(
	SELECT
	   #A1.*,
       #A2.CATEGORY_DETAIL_NM,
       #A3.CATEGORY_DETAIL_NM,
	   #CONCAT(A1.group_ID, A1.CATEGORY_ID, A1.category_LV,  A1.category_detail_LV) AS CATEGORY_KEY, ##카테고리 키
       CONCAT(A1.group_ID, A1.CATEGORY_ID) AS CATEGORY_KEY, ##카테고리 키 (그룹 + 키 형태로 카테고리분류)
 	   CASE A1.category_parent_LV
 	        WHEN 0 THEN A1.category_detail_NM
 			WHEN 1 THEN CONCAT(A2.category_detail_NM,' > ', A1.category_detail_NM)
 			WHEN 2 THEN CONCAT(A3.category_detail_NM, ' > ', A2.category_detail_NM,' > ', A1.category_detail_NM)
 	   END as CATEGORY_NAV
FROM PRODUCT_CATEGORY A1
LEFT OUTER JOIN PRODUCT_CATEGORY A2
ON A1.CATEGORY_PARENT_LV = A2.CATEGORY_LV AND A1.CATEGORY_DETAIL_PARENT_LV = A2.CATEGORY_DETAIL_LV AND A1.GROUP_ID = A2.GROUP_ID
LEFT OUTER JOIN PRODUCT_CATEGORY A3
ON A2.CATEGORY_PARENT_LV = A3.CATEGORY_LV AND A2.CATEGORY_DETAIL_PARENT_LV = A3.CATEGORY_DETAIL_LV
ORDER BY CATEGORY_NAV ASC
) as c
on c.category_key = p.PROD_CATEGORY
where PROD_WRITER = 'admin'
and prod_name like '%아이%' ## +조건 +조건
order by prod_price asc, prod_id desc
limit 0, 10;


## 2번 방법
select p.*,
	   c.category_nav
from (
	select *
    from product
	where PROD_WRITER = 'admin'
	and prod_name like '%삼성%' ## +조건 +조건
	order by prod_price asc, prod_id desc
	limit 0, 10
) p
left outer join (
	SELECT
	   #A1.*,
       #A2.CATEGORY_DETAIL_NM,
       #A3.CATEGORY_DETAIL_NM,
	   #CONCAT(A1.group_ID, A1.CATEGORY_ID, A1.category_LV,  A1.category_detail_LV) AS CATEGORY_KEY, ##카테고리 키
       CONCAT(A1.group_ID, A1.CATEGORY_ID) AS CATEGORY_KEY, ##카테고리 키 (그룹 + 키 형태로 카테고리분류)
 	   CASE A1.category_parent_LV
 	        WHEN 0 THEN A1.category_detail_NM
 			WHEN 1 THEN CONCAT(A2.category_detail_NM,' > ', A1.category_detail_NM)
 			WHEN 2 THEN CONCAT(A3.category_detail_NM, ' > ', A2.category_detail_NM,' > ', A1.category_detail_NM)
 	   END as CATEGORY_NAV
FROM PRODUCT_CATEGORY A1
LEFT OUTER JOIN PRODUCT_CATEGORY A2
ON A1.CATEGORY_PARENT_LV = A2.CATEGORY_LV AND A1.CATEGORY_DETAIL_PARENT_LV = A2.CATEGORY_DETAIL_LV AND A1.GROUP_ID = A2.GROUP_ID
LEFT OUTER JOIN PRODUCT_CATEGORY A3
ON A2.CATEGORY_PARENT_LV = A3.CATEGORY_LV AND A2.CATEGORY_DETAIL_PARENT_LV = A3.CATEGORY_DETAIL_LV
ORDER BY CATEGORY_NAV ASC
) c
on p.PROD_CATEGORY = c.category_key;

###################################################################################################################
SELECT
	   #A1.*,
       #A2.CATEGORY_DETAIL_NM,
       #A3.CATEGORY_DETAIL_NM,
	   #CONCAT(A1.group_ID, A1.CATEGORY_ID, A1.category_LV,  A1.category_detail_LV) AS CATEGORY_KEY, ##카테고리 키
       CONCAT(A1.group_ID, A1.CATEGORY_ID) AS CATEGORY_KEY, ##카테고리 키 (그룹 + 키 형태로 카테고리분류)
 	   CASE A1.category_parent_LV
 	        WHEN 0 THEN A1.category_detail_NM
 			WHEN 1 THEN CONCAT(A2.category_detail_NM,' > ', A1.category_detail_NM)
 			WHEN 2 THEN CONCAT(A3.category_detail_NM, ' > ', A2.category_detail_NM,' > ', A1.category_detail_NM)
 	   END as CATEGORY_NAV
FROM PRODUCT_CATEGORY A1
LEFT OUTER JOIN PRODUCT_CATEGORY A2
ON A1.CATEGORY_PARENT_LV = A2.CATEGORY_LV AND A1.CATEGORY_DETAIL_PARENT_LV = A2.CATEGORY_DETAIL_LV AND A1.GROUP_ID = A2.GROUP_ID
LEFT OUTER JOIN PRODUCT_CATEGORY A3
ON A2.CATEGORY_PARENT_LV = A3.CATEGORY_LV AND A2.CATEGORY_DETAIL_PARENT_LV = A3.CATEGORY_DETAIL_LV
ORDER BY CATEGORY_NAV ASC;

select * from product;

 

---

오늘 하루

기억에 남는 부분

- 

- 

 

어려운 부분

- 

- 

 

문제 해결 부분

- 

-