1: <?php
2: /**
3: * DataTables PHP libraries.
4: *
5: * PHP libraries for DataTables and DataTables Editor, utilising PHP 5.3+.
6: *
7: * @author SpryMedia
8: * @copyright 2016 SpryMedia ( http://sprymedia.co.uk )
9: * @license http://editor.datatables.net/license DataTables Editor
10: * @link http://editor.datatables.net
11: */
12:
13: namespace DataTables\Editor;
14: if (!defined('DATATABLES')) exit();
15:
16: use DataTables;
17:
18: /**
19: * The Options class provides a convenient method of specifying where Editor
20: * should get the list of options for a `select`, `radio` or `checkbox` field.
21: * This is normally from a table that is _left joined_ to the main table being
22: * edited, and a list of the values available from the joined table is shown to
23: * the end user to let them select from.
24: *
25: * `Options` instances are used with the {@link Field::options} method.
26: *
27: * @example
28: * Get a list of options from the `sites` table
29: * <code>
30: * Field::inst( 'users.site' )
31: * ->options( Options::inst()
32: * ->table( 'sites' )
33: * ->value( 'id' )
34: * ->label( 'name' )
35: * )
36: * </code>
37: *
38: * @example
39: * Get a list of options with custom ordering
40: * <code>
41: * Field::inst( 'users.site' )
42: * ->options( Options::inst()
43: * ->table( 'sites' )
44: * ->value( 'id' )
45: * ->label( 'name' )
46: * ->order( 'name DESC' )
47: * )
48: * </code>
49: *
50: * @example
51: * Get a list of options showing the id and name in the label
52: * <code>
53: * Field::inst( 'users.site' )
54: * ->options( Options::inst()
55: * ->table( 'sites' )
56: * ->value( 'id' )
57: * ->label( [ 'name', 'id' ] )
58: * ->render( function ( $row ) {
59: * return $row['name'].' ('.$row['id'].')';
60: * } )
61: * )
62: * </code>
63: */
64: class Options extends DataTables\Ext {
65: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
66: * Private parameters
67: */
68:
69: /** @var string Table to get the information from */
70: private $_table = null;
71:
72: /** @var string Column name containing the value */
73: private $_value = null;
74:
75: /** @var string[] Column names for the label(s) */
76: private $_label = array();
77:
78: /** @var integer Row limit */
79: private $_limit = null;
80:
81: /** @var callable Callback function to do rendering of labels */
82: private $_renderer = null;
83:
84: /** @var callback Callback function to add where conditions */
85: private $_where = null;
86:
87: /** @var string ORDERY BY clause */
88: private $_order = null;
89:
90:
91: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
92: * Public methods
93: */
94:
95: /**
96: * Get / set the column(s) to use as the label value of the options
97: *
98: * @param null|string|string[] $_ null to get the current value, string or
99: * array to get.
100: * @return Options|string[] Self if setting for chaining, array of values if
101: * getting.
102: */
103: public function label ( $_=null )
104: {
105: if ( $_ === null ) {
106: return $this;
107: }
108: else if ( is_string($_) ) {
109: $this->_label = array( $_ );
110: }
111: else {
112: $this->_label = $_;
113: }
114:
115: return $this;
116: }
117:
118: /**
119: * Get / set the LIMIT clause to limit the number of records returned.
120: *
121: * @param [type] $_ null|number Number of rows to limit the result to
122: * @return Options|string[] Self if setting for chaining, limit if getting.
123: */
124: public function limit ( $_=null )
125: {
126: return $this->_getSet( $this->_limit, $_ );
127: }
128:
129: /**
130: * Get / set the ORDER BY clause to use in the SQL. If this option is not
131: * provided the ordering will be based on the rendered output, either
132: * numerically or alphabetically based on the data returned by the renderer.
133: *
134: * @param null|string $_ String to set, null to get current value
135: * @return Options|string Self if setting for chaining, string if getting.
136: */
137: public function order ( $_=null )
138: {
139: return $this->_getSet( $this->_order, $_ );
140: }
141:
142: /**
143: * Get / set the label renderer. The renderer can be used to combine
144: * multiple database columns into a single string that is shown as the label
145: * to the end user in the list of options.
146: *
147: * @param null|callable $_ Function to set, null to get current value
148: * @return Options|callable Self if setting for chaining, callable if
149: * getting.
150: */
151: public function render ( $_=null )
152: {
153: return $this->_getSet( $this->_renderer, $_ );
154: }
155:
156: /**
157: * Get / set the database table from which to gather the options for the
158: * list.
159: *
160: * @param null|string $_ String to set, null to get current value
161: * @return Options|string Self if setting for chaining, string if getting.
162: */
163: public function table ( $_=null )
164: {
165: return $this->_getSet( $this->_table, $_ );
166: }
167:
168: /**
169: * Get / set the column name to use for the value in the options list. This
170: * would normally be the primary key for the table.
171: *
172: * @param null|string $_ String to set, null to get current value
173: * @return Options|string Self if setting for chaining, string if getting.
174: */
175: public function value ( $_=null )
176: {
177: return $this->_getSet( $this->_value, $_ );
178: }
179:
180: /**
181: * Get / set the method to use for a WHERE condition if it is to be
182: * applied to the query to get the options.
183: *
184: * @param null|callable $_ Function to set, null to get current value
185: * @return Options|callable Self if setting for chaining, callable if
186: * getting.
187: */
188: public function where ( $_=null )
189: {
190: return $this->_getSet( $this->_where, $_ );
191: }
192:
193:
194:
195: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
196: * Internal methods
197: */
198:
199: /**
200: * Execute the options (i.e. get them)
201: *
202: * @param Database $db Database connection
203: * @return array List of options
204: * @internal
205: */
206: public function exec ( $db )
207: {
208: $label = $this->_label;
209: $value = $this->_value;
210: $formatter = $this->_renderer;
211:
212: // Create a list of the fields that we need to get from the db
213: $fields = array();
214: $fields[] = $value;
215: $fields = array_merge( $fields, $label );
216:
217: // We need a default formatter if one isn't provided
218: if ( ! $formatter ) {
219: $formatter = function ( $row ) use ( $label ) {
220: $a = array();
221:
222: for ( $i=0, $ien=count($label) ; $i<$ien ; $i++ ) {
223: $a[] = $row[ $label[$i] ];
224: }
225:
226: return implode(' ', $a);
227: };
228: }
229:
230: // Get the data
231: $q = $db
232: ->query('select')
233: ->table( $this->_table )
234: ->distinct( true )
235: ->get( $fields )
236: ->where( $this->_where )
237: ->order( $this->_order );
238:
239: if ( $this->_limit !== null ) {
240: $q->limit( $this->_limit );
241: }
242:
243: $rows = $q
244: ->exec()
245: ->fetchAll();
246:
247: // Create the output array
248: $out = array();
249:
250: for ( $i=0, $ien=count($rows) ; $i<$ien ; $i++ ) {
251: $out[] = array(
252: "label" => $formatter( $rows[$i] ),
253: "value" => $rows[$i][$value]
254: );
255: }
256:
257: // Only sort if there was no SQL order field
258: if ( ! $this->_order ) {
259: usort( $out, function ( $a, $b ) {
260: return is_numeric($a['label']) && is_numeric($b['label']) ?
261: ($a['label']*1) - ($b['label']*1) :
262: strcmp( $a['label'], $b['label'] );
263: } );
264: }
265:
266: return $out;
267: }
268: }
269: