CI 묻고 답하기

제목 ci4 액티브 레코드 subquery union compileSelect 작동방식은 ?
카테고리 CI 4 관련
글쓴이 darkninja 작성시각 2022/11/27 15:23:33
댓글 : 3 추천 : 0 스크랩 : 0 조회수 : 4110   RSS
->select("b.username, a.table, a.board_table, a.comment_table, ".$u_field, false)
$sql = $builder->getCompiledSelect(false);

$query = $builder->get();

위에 처럼 하면 sql에 값이  b.username 에 넘어오지 않고

아래 처럼 하면 되는 것 같은데 ...

$sql = $builder->getCompiledSelect();

$query = $builder->get(null,0,false);

getCompiledSelect(bool $reset = true): string 에서 호출하는 

resetSelect() 에서 뭔가를 하는 것 닽은데 ...

고수님들 설명을 부탁 드립니다.

fromSubquery 두개 사용은 안되는 거 같습니다;;

정상적인 결과가 안 나오네요.

하나만 사용시에는 모든게 정상입니다.

fromSubquery 을 join 으로 바꾸니 제대로 됩니다

sql 에 기본적인 지식이 없다보니..

 

 

$sql = $builder->getCompiledSelect(); // 이줄을 첨가하지 않으면 에러 납니다.

$query = $builder->get(null,0,false);

if ($query->getNumRows() > 0) { $cnt = $query->getNumRows(); }


$builder->limit($per_page, ($page_index - 1) * $per_page);

$query = $builder->get();

return $query->getResult();

 

 

    public function all_search_paginated_active($search_table, $search_field, $field_or, $text_or, $search_text, $page_index=1, $per_page=10, &$cnt=0) 
    {                   
        $union = $this->all_search_paginated_union($search_table, $search_field, $field_or, $text_or, $search_text); 

        $user_subquery = 
		        $this->db
                     ->table("users");
					
        $u_field = " `a`.`id`, `a`.`user_id`, `a`.`user_name`, `a`.`category_name`, `a`.`subject`, `a`.`reply_count`, `a`.`reg_date`, `a`.`modify_date`, `a`.`contents`, `a`.`tag`";

        $builder = $this->db
		                ->newQuery()
                        ->select("users.username, a.table, a.board_table, a.comment_table, ".$u_field, false)
                        ->fromSubquery($union, "a");
                        //->fromSubquery($user_subquery, "b")
                        ->join("users", "users.id = a.user_id");
                        //->orderBy("reg_date", "desc");
        
		//$sql = $builder->getCompiledSelect(false);
		//$sql = $builder->getCompiledSelect();

        //$query = $builder->get();
        $query = $builder->get(null,0,false);

        if ($query->getNumRows() > 0) {
            $cnt = $query->getNumRows();
        }

		//echo $sql;
        echo $this->db->GetLastQuery();
		echo '<br>';
		echo $cnt;
		echo '<br>';
		echo '<br>';

        //$builder = $this->db
		//                ->newQuery()
        //                ->select("b.username, a.table, a.board_table, a.comment_table, ".$u_field, false)
        //                ->fromSubquery($union, "a")
        //                ->fromSubquery($user_subquery, "b")
        //                ->where("a.user_id", "b.id")
        //                ->orderBy("reg_date", "desc")
		//    			//->limit($per_page, ($page_index - 1) * $per_page)
		//				;
		//$sql = $builder->getCompiledSelect();

        $builder->orderBy("modify_date", "desc");
        $builder->limit($per_page, ($page_index - 1) * $per_page);
		$query = $builder->get();
        //$query = $builder->get($per_page, ($page_index - 1) * $per_page);

		//echo $sql;
        echo $this->db->GetLastQuery();
		echo '<br>';
		echo '<br>';
        
        return $query->getResult();
    }
    public function all_search_paginated_union($search_table, $search_field, $field_or, $text_or, $search_text) 
    {
        $s_field = " `id`, `user_id`, `user_name`, `category_name`, `subject`, `reply_count`, `reg_date`, `modify_date`, `contents`, `tag` ";

        $union = 0;
        if (is_array($search_table)) {
            foreach($search_table as $table) {
                $builder = 
				    $this->db
                         ->table($table)
                         ->select("'".$table."' as `table`, '".$table."' as `board_table`, '".$table."_comment' as `comment_table`, ".$s_field, false);
                    
				$builder = $this->search_orlike($builder, $search_table, $search_field, $field_or, $text_or, $search_text); 
                    
				$builder->orderBy('id', 'DESC');

                if ($union) {
                    $union = $union->union($builder);
    			}	
				else {
                    $union = $builder;
                }
            }
		}		
        else {
            $builder = 
		        $this->db
                     ->table($table)
                     ->select("'".$search_table."' as `table`, '".$search_table."' as `board_table`, '".$search_table."_comment' as `comment_table`, ".$s_field, false);
                   
			$builder = $this->search_orlike($builder, $search_table, $search_field, $field_or, $text_or, $search_text); 
                    
			$builder->orderBy('id', 'DESC');

            $union = $builder;
        }           
		
		return $union;
	}					
    public function search_orlike($builder, $search_table, $search_field, $field_or, $text_or, $search_text) 
    {
        $f = "";
        $t = "";

        if (is_array($search_field)) {
            if (is_array($search_text)) {
                foreach($search_field as $field) {
                    if ($field_or == "or") {
                        $builder->orGroupStart();
                    }
                    else {
                        $builder->GroupStart();
                    }
                    foreach($search_text as $text) {
                        if ($text_or =='or') {
                          $builder->orLike($field, $text);
                        }
                        else {
                          $builder->Like($field, $text);
                        }   
                    }   
                    $builder->GroupEnd();
                }
            } else {
                foreach($search_field as $field) {
                    if ($field_or == "or") {
                        $builder->orGroupStart();
                    }
                    else {
                        $builder->GroupStart();
                    }
                    if ($field_or =='or') {
                      $builder->orLike($field, $search_text);
                    }
                    else {
                      $builder->Like($field, $search_text);
                    }   
                    $builder->GroupEnd();
                }
            }
        } else {
            if (is_array($search_text)) {
                $builder->GroupStart();
                foreach($search_text as $text) {
                    if ($text_or =='or') {
                      $builder->orLike($search_field, $text);
                    }
                    else {
                      $builder->Like($search_field, $text);
                    }   
                }
                $builder->GroupEnd();
            } else {
                if ($text_or =='or') {
                  $builder->orLike($search_field, $search_text);
                }
                else {
                  $builder->Like($search_field, $search_text);
                }   
            }
        }   
        
        return $builder;        
    }   
    public function getCompiledSelect(bool $reset = true): string
    {
        $select = $this->compileSelect();

        if ($reset === true) {
            $this->resetSelect();
        }

        return $this->compileFinalQuery($select);
    }

    protected function resetSelect()
    {
        $this->resetRun([
            'QBSelect'   => [],
            'QBJoin'     => [],
            'QBWhere'    => [],
            'QBGroupBy'  => [],
            'QBHaving'   => [],
            'QBOrderBy'  => [],
            'QBNoEscape' => [],
            'QBDistinct' => false,
            'QBLimit'    => false,
            'QBOffset'   => false,
            'QBUnion'    => [],
        ]);

        if (! empty($this->db)) {
            $this->db->setAliasedTables([]);
        }

        // Reset QBFrom part
        if (! empty($this->QBFrom)) {
            $this->from(array_shift($this->QBFrom), true);
        }
    }
    public function from($from, bool $overwrite = false): self
    {
        if ($overwrite === true) {
            $this->QBFrom = [];
            $this->db->setAliasedTables([]);
        }

        foreach ((array) $from as $table) {
            if (strpos($table, ',') !== false) {
                $this->from(explode(',', $table));
            } else {
                $table = trim($table);

                if ($table === '') {
                    continue;
                }

                $this->trackAliases($table);
                $this->QBFrom[] = $this->db->protectIdentifiers($table, true, null, false);
            }
        }

        return $this;
    }

 

 다음글 관리자 페이지 로그인이 한번에 안되는 문제 (1)
 이전글 CI3 조회페이지에서 $CI->db->tra... (1)

댓글

한대승(불의회상) / 2022/11/28 15:32:28 / 추천 0

올려주신 코드로는 정확히 무엇을 하고 싶은지 잘 이해가 안되는군요.

서브 쿼리에 관련된 부분은 메뉴얼에 잘 나와 있습니다.

http://ci4doc.cikorea.net/database/query_builder.html#subqueries

<?php

$subquery = $db->table('users');
$builder  = $db->table('jobs')->fromSubquery($subquery, 'alias');
$query    = $builder->get();
// Produces: SELECT * FROM `jobs`, (SELECT * FROM `users`) `alias`

샘플 쿼리와 아래 주석으로 표시된 결과를 잘 확인하시면 원하시는 쿼리를 만들어 내실수 있을겁니다.

darkninja / 2022/11/28 18:51:42 / 추천 0

포럼 구버젼의 소스에서 검색하는 부분을 조금 달리 한건데

로컬에서 마구 수집한 자료를 검색하는 용도로 만들려고 하는 중입니다

http://localhost/ci-426/index.php/Search/view_all_active/farm,gam/subject,contents/or/or/simple/%EA%B0%90,%ED%83%9C%EC%B6%94

조인하는 부분은 없어도 되는데

포럼구버젼소스를 그대로 바꾸려고 하다보니 엉뚱하게 진행되었습니다

 

같은 구조로 된 여러개의 테이블에서

제목이나 본문에 있는

여러개의 문자열을 검색하는 용도입니다

에러난 부분은 조인으로 바꾸어서 해결되었습니다

 

컨트롤러 호출부분입니다

	public function view_all_active($search_table, $search_field, $field_or, $text_or, $simple_list="", $search_text="", $page_index=1) 
	{
		if (empty($search_text)) {
            $this->response->redirect(site_url('/'));
		}	

        session_set_search($search_table, $search_field, $field_or, $text_or, $search_text, $page_index);

        $search_text_encode = $search_text;
        $search_text_decode = $search_text;

		$text_encode = $this->session->get('text_encode');
        if ($text_encode == "myencode") {
            $search_text_decode = decode_str_array($search_text);
        }  
        else if ($text_encode == "urlencode") {
            $search_text_decode = urldecode($search_text);
        }  

        $table_array = explode(',', $search_table);
        $field_array = explode(',', $search_field);
        $text_array_decode = explode(',', $search_text_decode); //search_text_decode

        if ($simple_list == "") {
            $simple_list = $this->session->get('simple_list');
		}	
	    $this->session->set('simple_list', $simple_list);

        if ($simple_list == "simple") {
            $per_page = $this->per_page;
            $view_name = "search_all";
            $url_name = "view_all_active";
            $view_simple_list = "list"; 
        }
        else {
            $per_page = $this->per_page_list;
            $view_name = "searchlist_all";
            $url_name = "view_all_active";
            $view_simple_list = "simple"; 
        }			

    	$Surround_Count = $this->Surround_Count;
        $cnt = 0;
    	$boards = $this->board_model->all_search_paginated_active($table_array, $field_array, $field_or, $text_or, $text_array_decode, $page_index, $per_page, $cnt);

    	$redirect_url = 'search/'.$url_name.'/'.$search_table.'/'.$search_field.'/'.$field_or.'/'.$text_or.'/'.$simple_list.'/'.$search_text_encode;
    	$pager_base_url = ROOT_PATH.'/'.$redirect_url;

        $page = $page_index;
        $total_rows = $cnt;
        $template_name = 'default_full';
        $uri_segment = 10;
        $group = 'default';

        //$pager = $this->board_model->pager;
        $pager = service('pager');
    
        $pager->setPath($pager_base_url, $group); // Additionally you could define path for every group.

        $pageLinks = $pager->makeLinks($page, $per_page, $total_rows, $template_name, $uri_segment, $group);    

        $t = intval(($total_rows-1) / $per_page) + 1;
        if ($page_index > $t) {
            $this->response->redirect(site_url($redirect_url.'/'.$t));
        }
		
		$data = Array(
			'head_data' => Array(
  				'title' => '통합검색 : '.$search_text_decode,
			),	
			'view_data' => Array(
				'boards' => $boards,
                'pageLinks' => $pageLinks,
    	        'Surround_Count' => $Surround_Count,
                'session' => $this->session,
                'db' => $this->db,
				'cut_contents' => $this->cut_contents,
				'board_controller' => $this->board_controller,
				'search_controller' => $this->search_controller,
				'search_table' => $search_table,
				'search_field' => $search_field, 
				'field_or' => $field_or, 
				'text_or' => $text_or, 
				'simple_list' => $view_simple_list, 
				'search_text_encode' => $search_text_encode,
				'page_index' => $page_index,
				'url_name' => $url_name,
			    'no_more_board' => '',
			),
		);		
		$data = array_replace_recursive($this->data, $data);
		
		if ($total_rows <= $per_page) {
			$view_data['no_more_board'] = 'no more board !';
		}

        $this->render_page($view_name, $data);
	}

 

한대승(불의회상) / 2022/11/29 18:27:39 / 추천 0

mysql은 서브쿼리보다 join의 성능이 좋아서 가능하면 서브쿼리를 join으로 변환하도록 가이드합니다.

문제 해결을 위해 많은 수고를 하셨네요.