list.php
151 lines
| 1 | <?php |
| 2 | /** |
| 3 | * Incapsulates behavior for list of database records |
| 4 | * |
| 5 | * @author Pavel Kulbakin <p.kulbakin@gmail.com> |
| 6 | */ |
| 7 | class PMXE_Model_List extends PMXE_Model { |
| 8 | |
| 9 | /** |
| 10 | * Total number of records in database which correspond last getBy rule without paging |
| 11 | * @var int |
| 12 | */ |
| 13 | protected $total = 0; |
| 14 | |
| 15 | /** |
| 16 | * Joined tables |
| 17 | * @var array |
| 18 | */ |
| 19 | protected $joined = array(); |
| 20 | /** |
| 21 | * Columns to select from database |
| 22 | * @var string |
| 23 | */ |
| 24 | protected $what = '*'; |
| 25 | /** |
| 26 | * Sets table to use in conjuction with primary list table |
| 27 | * @param string $table Table to join |
| 28 | * @param string $on Condition to join |
| 29 | * @param string $type Join type (INNER, OUTER, etc) |
| 30 | * @return PMXE_Model_List |
| 31 | */ |
| 32 | public function join($table, $on, $type = 'INNER') { |
| 33 | $this->joined[] = ( ! is_null($type) ? $type . ' ' : '') . 'JOIN ' . $table . ' ON ' . $on; |
| 34 | return $this; |
| 35 | } |
| 36 | |
| 37 | /** |
| 38 | * Set columns to be selected from database |
| 39 | * @param array $columns |
| 40 | * @return PMXE_Model_List |
| 41 | */ |
| 42 | public function setColumns($columns) { |
| 43 | is_array($columns) or $columns = func_get_args(); |
| 44 | $this->what = implode(', ', $columns); |
| 45 | return $this; |
| 46 | } |
| 47 | |
| 48 | /** |
| 49 | * Read records from database by specified fields and values |
| 50 | * When 1st parameter is an array, it's expected to be an associative array of field => value pairs to read data by |
| 51 | * When 2nd parameter is a scalar, it's expected to be a field name and second parameter - it's value |
| 52 | * |
| 53 | * @param string|array[optional] $field |
| 54 | * @param mixed[optional] $value |
| 55 | * @param string[optional] $orderBy Ordering rule |
| 56 | * @param int[optional] $page Paging paramter used to limit number of records returned |
| 57 | * @param int[optional] $perPage Page size when paging parameter is used (20 by default) |
| 58 | * @return PMXE_Model_List |
| 59 | */ |
| 60 | public function getBy($field = NULL, $value = NULL, $orderBy = NULL, $page = NULL, $perPage = NULL, $groupBy = NULL) { |
| 61 | if (is_array($field) or is_null($field)) { // when associative array is submitted, do not expect second paramter to be $value, but act as if there is no $value parameter at all |
| 62 | $groupBy = $perPage; $perPage = $page; $page = $orderBy; $orderBy = $value; $value = NULL; |
| 63 | } |
| 64 | ! is_null($perPage) or $perPage = 20; // set default value for page length |
| 65 | $page = intval($page); |
| 66 | |
| 67 | $sql = "FROM $this->table "; |
| 68 | $sql .= implode(' ', $this->joined); |
| 69 | if ( ! is_null($field)) { |
| 70 | $sql .= " WHERE " . $this->buildWhere($field, $value); |
| 71 | } |
| 72 | if ( ! is_null($groupBy)) { |
| 73 | $sql .= " GROUP BY $groupBy"; |
| 74 | } |
| 75 | is_null($orderBy) and $orderBy = implode(', ', $this->primary); // default sort order is by primary key |
| 76 | $sql .= " ORDER BY $orderBy"; |
| 77 | if ($page > 0) { |
| 78 | $sql = "SELECT SQL_CALC_FOUND_ROWS $this->what $sql LIMIT " . intval(($page - 1) * $perPage) . ", " . intval($perPage); |
| 79 | } else { |
| 80 | $sql = "SELECT $this->what $sql"; |
| 81 | } |
| 82 | // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built from $this->table (from $wpdb->prefix), $this->what / $orderBy / $groupBy / $this->joined set via internal API only; WHERE values bound through buildWhere()/$wpdb->prepare() |
| 83 | $result = $this->wpdb->get_results($sql, ARRAY_A); |
| 84 | if (is_array($result)) { |
| 85 | foreach ($result as $i => $row) { |
| 86 | foreach ($row as $k => $v) { |
| 87 | if (is_serialized($v)) { |
| 88 | $result[$i][$k] = unserialize($v); |
| 89 | } |
| 90 | } |
| 91 | } |
| 92 | if ($page > 0) { |
| 93 | $this->total = intval($this->wpdb->get_var('SELECT FOUND_ROWS()')); |
| 94 | } else { |
| 95 | $this->total = count($result); |
| 96 | } |
| 97 | $this->exchangeArray($result); |
| 98 | } else { |
| 99 | $this->total = 0; |
| 100 | $this->clear(); |
| 101 | } |
| 102 | return $this; |
| 103 | } |
| 104 | |
| 105 | /** |
| 106 | * Count records in table |
| 107 | * @param string|array $field |
| 108 | * @param mixed[optional] $value |
| 109 | * @return int |
| 110 | */ |
| 111 | public function countBy($field = NULL, $value = NULL) { |
| 112 | $sql = "SELECT COUNT(*) FROM $this->table "; |
| 113 | $sql .= implode(' ', $this->joined); |
| 114 | if ( ! is_null($field)) { |
| 115 | $sql .= " WHERE " . $this->buildWhere($field, $value); |
| 116 | } |
| 117 | // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built from $this->table (from $wpdb->prefix) and internal joins; WHERE values bound through buildWhere()/$wpdb->prepare() |
| 118 | return intval($this->wpdb->get_var($sql)); |
| 119 | } |
| 120 | |
| 121 | /** |
| 122 | * Method returns number of rows in database which correspond last getBy query |
| 123 | * @return int |
| 124 | */ |
| 125 | public function total() { |
| 126 | return $this->total; |
| 127 | } |
| 128 | |
| 129 | /** |
| 130 | * Converts elements to instances of specifield class. If includeFields are provided only fields listed are included |
| 131 | * @param string[optoinal] $elementClass |
| 132 | * @param array[optional] $includeFields |
| 133 | * @return PMXE_Model_List |
| 134 | */ |
| 135 | public function convertRecords($elementClass = NULL, $includeFields = NULL) { |
| 136 | ! is_null($elementClass) or $elementClass = preg_replace('%List$%', 'Record', get_class($this)); |
| 137 | if ( ! is_subclass_of($elementClass, PMXE_Plugin::PREFIX . 'Model_Record')) { |
| 138 | throw new Exception( esc_html( "Provideded class name $elementClass must be a subclass of " . PMXE_Plugin::PREFIX . 'Model_Record' ) ); |
| 139 | } |
| 140 | $records = $this->exchangeArray(array()); |
| 141 | foreach ($records as $r) { |
| 142 | $data = (array)$r; |
| 143 | if ( ! is_null($includeFields)) { |
| 144 | $data = array_intersect_key($data, array_flip($includeFields)); |
| 145 | } |
| 146 | $this[] = new $elementClass($data); |
| 147 | } |
| 148 | return $this; |
| 149 | } |
| 150 | |
| 151 | } |