FuelPHPのQueryビルダーを使ってPostgreSQLの「distinct on」を使う場合のメモ。
FuelPHPのQueryビルダーではdistinctはあってもdistinct onはないので拡張する必要があります。
手順1
fuel/core/classes/database/query/builder/select.php をfuel/app/classes/core/select.phpにコピーします。
手順2
コピーしたクラスを下記の通りに修正します。
|
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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
<?php class Database_Query_Builder_Select extends \Fuel\Core\Database_Query_Builder_Select { // distinct on public $_distinct_on = false; /** * distinct on ($distinct_on) ... * @param string $distinct_on false or カラム名 */ public function distinct_on($distinct_on = false) { $this->_distinct_on = $distinct_on; return $this; } /** * Compile the SQL query and return it. * * @param mixed $db Database_Connection instance or instance name * * @return string */ public function compile($db = null) { if ( ! $db instanceof \Database_Connection) { // Get the database instance $db = $this->_connection ?: \Database_Connection::instance($db); } // Callback to quote identifiers $quote_ident = array($db, 'quote_identifier'); // Callback to quote tables $quote_table = array($db, 'quote_table'); // Start a selection query $query = 'SELECT '; if ($this->_distinct_on !== false) { $query .= "DISTINCT ON ($this->_distinct_on) "; } if ($this->_distinct === TRUE) { // Select only unique results $query .= 'DISTINCT '; } if (empty($this->_select)) { // Select all columns $query .= '*'; } else { // Select all columns $query .= implode(', ', array_unique(array_map($quote_ident, $this->_select))); } if ( ! empty($this->_from)) { // Set tables to select from $query .= ' FROM '.implode(', ', array_unique(array_map($quote_table, $this->_from))); } if ( ! empty($this->_join)) { // Add tables to join $query .= ' '.$this->_compile_join($db, $this->_join); } if ( ! empty($this->_where)) { // Add selection conditions $query .= ' WHERE '.$this->_compile_conditions($db, $this->_where); } if ( ! empty($this->_group_by)) { // Add sorting $query .= ' GROUP BY '.implode(', ', array_map($quote_ident, $this->_group_by)); } if ( ! empty($this->_having)) { // Add filtering conditions $query .= ' HAVING '.$this->_compile_conditions($db, $this->_having); } if ( ! empty($this->_order_by)) { // Add sorting $query .= ' '.$this->_compile_order_by($db, $this->_order_by); } if ($this->_limit !== NULL) { // Add limiting $query .= ' LIMIT '.$this->_limit; } if ($this->_offset !== NULL) { // Add offsets $query .= ' OFFSET '.$this->_offset; } return $query; } /** * Reset the query parameters * @return $this */ public function reset() { $this->_select = array(); $this->_from = array(); $this->_join = array(); $this->_where = array(); $this->_group_by = array(); $this->_having = array(); $this->_order_by = array(); $this->_distinct = false; $this->_limit = null; $this->_offset = null; $this->_last_join = null; $this->_parameters = array(); $this->_distinct_on = false; return $this; } |
手順3
fuel/app/bootstrap.phpのAutoloaderにクラスを追加する
|
1 2 3 4 5 |
\Autoloader::add_classes(array( // Add classes you want to override here // Example: 'View' => APPPATH.'classes/view.php', 'Database_Query_Builder_Select' => APPPATH.'classes/core/select.php', )); |
以上で拡張完了です。
例）使い方
|
1 2 3 4 5 |
$select = DB::select_array([['users.id' => 'id'], ['users.grpup' => 'group'])->from('users'); $select->distinct_on('users.group'); $select->order_by('users.group'); $result = $select->execute(); // select distinct on ("users"."group") "users"."id" as "id", "users"."group" as "group" from users order by "users"."group"; |