notificationx / vendor / wpdeveloper / query-builder / src / Query.php

Query.php in NotificationX – FOMO, Live Sales Notification, WooCommerce Sales Popup, GDPR, Social Proof, Announcement Banner & Floating Notification Bar 3.3.1, at vendor/wpdeveloper/query-builder/src/Query.php

1,462 lines 33.3 KB

Raw Download Zip

1	<?php
2
3	namespace WPDeveloper\QueryBuilder;
4
5	defined( 'ABSPATH' ) \|\| exit();
6
7	class Query {
8	/**
9	* @var string
10	*/
11	protected $id;
12
13	/**
14	* @var array
15	*/
16	protected $select = [];
17
18	/**
19	* @var null
20	*/
21	protected $from = null;
22
23	/**
24	* @var array
25	*/
26	protected $join = [];
27
28	/**
29	* @var array
30	*/
31	protected $where = [];
32
33	/**
34	* @var array
35	*/
36	protected $order = [];
37
38	/**
39	* @var array
40	*/
41	protected $group = [];
42
43	/**
44	* @var null
45	*/
46	protected $having = null;
47
48	/**
49	* @var null
50	*/
51	protected $limit = null;
52
53	/**
54	* @var int
55	*/
56	protected $offset = 0;
57
58	/**
59	* Static constructor.
60	*
61	*
62	* @since 1.0.0
63	*
64	*/
65	public static function init( $id = null ) {
66	$builder = new self();
67	$builder->id = ! empty( $id ) ? $id : uniqid();
68
69	return $builder;
70	}
71
72	/**
73	* Adds select statement.
74	*
75	* @param $statement
76	*
77	* @return $this
78	* @since 1.0.0
79	*/
80	public function select( $statement ) {
81	$this->select[] = $statement;
82
83	return $this;
84	}
85
86	/**
87	* Adds from statement.
88	*
89	* @param string $name
90	* @param bool $add_prefix
91	*
92	* @since 1.0.0
93	*
94	*/
95	public function table( $name, $add_prefix = true ) {
96	global $wpdb;
97	$table = ( $add_prefix ? $wpdb->prefix : '' ) . $name;
98	$this->from = $table;
99
100	return $this;
101	}
102
103	/**
104	* Adds from statement.
105	*
106	* @param string $from
107	* @param bool $add_prefix Should DB prefix be added.
108	*
109	* @return Query this for chaining.
110	* @global object $wpdb
111	*
112	* @since 1.0.0
113	*
114	*/
115	public function from( $from, $add_prefix = true ) {
116	global $wpdb;
117	$this->from = $this->from . ' ' . ( $add_prefix ? $wpdb->prefix : '' ) . $from;
118
119	return $this;
120	}
121
122	/**
123	* Adds search statement.
124	*
125	* @param $search
126	* @param $columns
127	* @param $joint
128	*
129	* @since 1.0.0
130	*/
131	public function search( $search, $columns, $joint = 'AND' ) {
132	if ( ! empty( $search ) ) {
133	global $wpdb;
134	foreach ( explode( ' ', $search ) as $word ) {
135	$word = '%' . $this->sanitize_value( true, $word ) . '%';
136	$this->where[] = [
137	'joint' => $joint,
138	'condition' => '(' . implode( ' OR ', array_map( function ( $column ) use ( &$wpdb, &$word ) {
139	return $wpdb->prepare( $column . ' LIKE %s', $word );
140	}, $columns ) ) . ')',
141	];
142	}
143	}
144
145	return $this;
146	}
147
148	/**
149	* Create a where statement.
150	*
151	* ->where('name', 'sultan')
152	* ->where('age', '>', 18)
153	* ->where('name', 'in', array('ayaan', 'ayaash', 'anaan'))
154	* ->where(function($q){
155	* $q->where('ID', '>', 21);
156	* })
157	*
158	* @param string $column The SQL column
159	* @param mixed $param1 Operator or value depending if $param2 isset.
160	* @param mixed $param2 The value if $param1 is an operator.
161	* @param string $joint the where type ( and, or )
162	*
163	* @return Query The current query builder.
164	*/
165	public function where( $column, $param1 = null, $param2 = null, $joint = 'and' ) {
166	global $wpdb;
167	if ( ! in_array( strtolower( $joint ), [ 'and', 'or', 'where' ] ) ) {
168	$this->exception( 'Invalid where type "' . $joint . '"' );
169	}
170
171	// when column is an array we assume to make a bulk and where.
172	if ( is_array( $column ) ) {
173	// create new query object
174	$subquery = new Query();
175	foreach ( $column as $key => $val ) {
176	$subquery->where( $key, $val, null, $joint );
177	}
178
179	$this->where = array_merge( $this->where, $subquery->where );
180
181	return $this;
182	}
183
184	if ( is_object( $column ) && ( $column instanceof \Closure ) ) {
185	// create new query object
186	$subquery = new Query();
187
188	// run the closure callback on the sub query
189	call_user_func_array( $column, array( &$subquery ) );
190	$condition = '';
191	for ( $i = 0; $i < count( $subquery->where ); ++ $i ) {
192	$condition .= ( $i === 0 ? ' ' : ' ' . $subquery->where[ $i ]['joint'] . ' ' )
193	. $subquery->where[ $i ]['condition'];
194	}
195
196	$this->where = array_merge( $this->where, array(
197	array(
198	'joint' => $joint,
199	'condition' => "($condition)"
200	)
201	) );
202
203	return $this;
204	}
205
206	// when param2 is null we replace param2 with param one as the
207	// value holder and make param1 to the = operator.
208	// However, if param1 is either 'is' or 'is not' we need to leave param2 as null.
209	// This is used for the whereNull() and whereNotNull() methods.
210	if ( is_null( $param2 ) && ! in_array( $param1, [ 'is', 'is not' ], true ) ) {
211	$param2 = $param1;
212	$param1 = '=';
213	}
214
215	// if the param2 is an array we filter it. when param2 is an array we probably
216	// have an "in" or "between" statement which has no need for duplicates.
217	if ( is_array( $param2 ) ) {
218	$param2 = array_unique( $param2 );
219	}
220
221	// Between?
222	if ( is_array( $param2 ) && strpos( $param1, 'BETWEEN' ) !== false ) {
223	$min = isset( $param2[0] ) ? $param2[0] : false;
224	$max = isset( $param2[1] ) ? $param2[1] : false;
225	if ( ! $min \|\| ! $max ) {
226	$this->exception( "BETWEEN min or max is missing" );
227	}
228
229	$min = $wpdb->prepare( is_numeric( $min ) ? '%d' : '%s', $min );
230	$max = $wpdb->prepare( is_numeric( $max ) ? '%d' : '%s', $max );
231
232	$this->where[] = [
233	'joint' => $joint,
234	'condition' => "($column BETWEEN $min AND $max)",
235	];
236
237	return $this;
238	}
239
240	// Not Between?
241	if ( is_array( $param2 ) && strpos( $param1, 'NOT BETWEEN' ) !== false ) {
242	$min = isset( $param2[0] ) ? $param2[0] : false;
243	$max = isset( $param2[1] ) ? $param2[1] : false;
244	if ( ! $min \|\| ! $max ) {
245	$this->exception( "NOT BETWEEN min or max is missing" );
246	}
247
248	$min = $wpdb->prepare( is_numeric( $min ) ? '%d' : '%s', $min );
249	$max = $wpdb->prepare( is_numeric( $max ) ? '%d' : '%s', $max );
250
251	$this->where[] = [
252	'joint' => $joint,
253	'condition' => "($column NOT BETWEEN $min AND $max)",
254	];
255
256	return $this;
257	}
258
259
260	//first check if is array if so then make a string out of array
261	//if not array but null then set value as null
262	//if not null does it contains . it could be column so dont parse as string
263	//If not column then use wpdb prepare
264	//if contains $prefix
265	$contain_join = preg_replace( '/^(\s?AND ?\|\s?OR ?)\|\s$/i', '', $param2 );
266
267	$param2 = is_array( $param2 ) ? ( '(' . implode( ',', array_map( [ $this, 'bind_value' ], $param2 ) ) . ')' ) : ( $param2 === null
268	? 'null'
269	: ( $this->is_column_reference( $param2 ) ? $param2 : $this->bind_value( $param2 ) )
270	);
271
272	$this->where[] = [
273	'joint' => $joint,
274	'condition' => implode( ' ', [ $column, $param1, $param2 ] ),
275	];
276
277	return $this;
278	}
279
280
281	/**
282	* Create an or where statement
283	*
284	* This is the same as the normal where just with a fixed type
285	*
286	* @param string $column The SQL column
287	* @param mixed $param1
288	* @param mixed $param2
289	*
290	* @return Query The current query builder.
291	*/
292	public function orWhere( $column, $param1 = null, $param2 = null ) {
293	return $this->where( $column, $param1, $param2, 'or' );
294	}
295
296	/**
297	* Create an and where statement
298	*
299	* This is the same as the normal where just with a fixed type
300	*
301	* @param string $column The SQL column
302	* @param mixed $param1
303	* @param mixed $param2
304	*
305	* @return Query The current query builder.
306	*/
307	public function andWhere( $column, $param1 = null, $param2 = null ) {
308	return $this->where( $column, $param1, $param2, 'and' );
309	}
310
311	/**
312	* Creates a where in statement
313	*
314	* ->whereIn('id', [42, 38, 12])
315	*
316	* @param string $column
317	* @param array $options
318	*
319	* @return Query The current query builder.
320	*/
321	public function whereIn( $column, array $options = array() ) {
322	// when the options are empty we skip
323	if ( empty( $options ) ) {
324	return $this;
325	}
326
327	return $this->where( $column, 'in', $options );
328	}
329
330	/**
331	* Creates a where not in statement
332	*
333	* ->whereNotIn('id', [42, 38, 12])
334	*
335	* @param string $column
336	* @param array $options
337	*
338	* @return Query The current query builder.
339	*/
340	public function whereNotIn( $column, array $options = array() ) {
341	// when the options are empty we skip
342	if ( empty( $options ) ) {
343	return $this;
344	}
345
346	return $this->where( $column, 'not in', $options );
347	}
348
349	/**
350	* Creates a where something is null statement
351	*
352	* ->whereNull('modified_at')
353	*
354	* @param string $column
355	*
356	* @return Query The current query builder.
357	*/
358	public function whereNull( $column ) {
359	return $this->where( $column, 'is', null );
360	}
361
362	/**
363	* Creates a where something is not null statement
364	*
365	* ->whereNotNull('created_at')
366	*
367	* @param string $column
368	*
369	* @return Query The current query builder.
370	*/
371	public function whereNotNull( $column ) {
372	return $this->where( $column, 'is not', null );
373	}
374
375	/**
376	* Creates a or where something is null statement
377	*
378	* ->orWhereNull('modified_at')
379	*
380	* @param string $column
381	*
382	* @return Query The current query builder.
383	*/
384	public function orWhereNull( $column ) {
385	return $this->orWhere( $column, 'is', null );
386	}
387
388	/**
389	* Creates a or where something is not null statement
390	*
391	* ->orWhereNotNull('modified_at')
392	*
393	* @param string $column
394	*
395	* @return Query The current query builder.
396	*/
397	public function orWhereNotNull( $column ) {
398	return $this->orWhere( $column, 'is not', null );
399	}
400
401
402	/**
403	* Creates a where between statement
404	*
405	* ->whereBetween('user_id', 1, 2000)
406	*
407	* @param string $column
408	*
409	* @return Query The current query builder.
410	*/
411	public function whereBetween( $column, $min, $max ) {
412	return $this->where( $column, 'BETWEEN', array( $min, $max ) );
413	}
414
415	/**
416	* Creates a where not between statement
417	*
418	* ->whereNotBetween('user_id', 1, 2000)
419	*
420	* @param string $column
421	*
422	* @return Query The current query builder.
423	*/
424	public function whereNotBetween( $column, $min, $max ) {
425	return $this->where( $column, 'NOT BETWEEN', array( $min, $max ) );
426	}
427
428	/**
429	* Creates a where date between statement
430	*
431	* ->whereDateBetween('date', '2014-02-01', '2014-02-28')
432	*
433	* @param string $column
434	*
435	* @return Query The current query builder.
436	*/
437	public function whereDateBetween( $column, $start, $end ) {
438	global $wpdb;
439	$stat_date = $wpdb->get_var( $wpdb->prepare( 'SELECT CAST(%s as DATE)', $start ) );
440	$end_date = $wpdb->get_var( $wpdb->prepare( 'SELECT CAST(%s as DATE)', $end ) );
441
442	return $this->where( $column, 'BETWEEN', array( $stat_date, $end_date ) );
443	}
444
445
446	/**
447	*
448	* @param $query
449	* @param string $joint
450	*
451	* @since 1.0.1
452	*/
453	public function whereRaw( $query, $joint = 'AND' ) {
454	$this->where[] = [
455	'joint' => $joint,
456	'condition' => $query,
457	];
458
459	return $this;
460	}
461
462
463	/**
464	* Add a join statement to the current query
465	*
466	* ->join('avatars', 'users.id', '=', 'avatars.user_id')
467	*
468	* @param array\|string $table The table to join. (can contain an alias definition.)
469	* @param string $localKey
470	* @param string $operator The operator (=, !=, <, > etc.)
471	* @param string $referenceKey
472	* @param string $type The join type (inner, left, right, outer)
473	* @param string $joint The join AND or Or
474	* @param bool $add_prefix Add table prefix or not
475	*
476	* @return Query The current query builder.
477	*/
478	public function join( $table, $localKey, $operator = null, $referenceKey = null, $type = 'left', $joint = 'AND', $add_prefix = true ) {
479	global $wpdb;
480	$type = is_string( $type ) ? strtoupper( trim( $type ) ) : ( $type ? 'LEFT' : '' );
481	if ( ! in_array( $type, [ '', 'LEFT', 'RIGHT', 'INNER', 'CROSS', 'LEFT OUTER', 'RIGHT OUTER' ] ) ) {
482	$this->exception( "Invalid join type." );
483	}
484
485	$join = [
486	'table' => ( $add_prefix ? $wpdb->prefix : '' ) . $table,
487	'type' => $type,
488	'on' => [],
489	];
490
491	// to make nested joins possible you can pass an closure
492	// which will create a new query where you can add your nested where
493	if ( is_object( $localKey ) && ( $localKey instanceof \Closure ) ) {
494	//create new query object
495	$subquery = new Query();
496	// run the closure callback on the sub query
497	call_user_func_array( $localKey, array( &$subquery ) );
498
499	$join['on'] = array_merge( $join['on'], $subquery->where );
500	$this->join = array_merge( $this->join, array( $join ) );
501
502	return $this;
503	}
504
505	// when param2 is null we replace param2 with param one as the
506	// value holder and make param1 to the = operator.
507	if ( is_null( $referenceKey ) ) {
508	$referenceKey = $operator;
509	$operator = '=';
510	}
511
512	$referenceKey = is_array( $referenceKey ) ? ( '(' . implode( ',', array_map( [ $this, 'bind_value' ], $referenceKey ) ) . ')' )
513	: ( $referenceKey === null
514	? 'null'
515	: ( $this->is_column_reference( $referenceKey ) ? $referenceKey : $this->bind_value( $referenceKey ) )
516	);
517
518	$join['on'][] = [
519	'joint' => $joint,
520	'condition' => implode( ' ', [ $localKey, $operator, $referenceKey ] ),
521	];
522
523	$this->join[] = $join;
524
525	return $this;
526	}
527
528	/**
529	* Left join same as join with special type
530	*
531	* @param array\|string $table The table to join. (can contain an alias definition.)
532	* @param string $localKey
533	* @param string $operator The operator (=, !=, <, > etc.)
534	* @param string $referenceKey
535	*
536	* @return Query The current query builder.
537	*/
538	public function leftJoin( $table, $localKey, $operator = null, $referenceKey = null ) {
539	return $this->join( $table, $localKey, $operator, $referenceKey, 'left' );
540	}
541
542	/**
543	* Alias of the `join` method with join type right.
544	*
545	* @param array\|string $table The table to join. (can contain an alias definition.)
546	* @param string $localKey
547	* @param string $operator The operator (=, !=, <, > etc.)
548	* @param string $referenceKey
549	*
550	* @return Query The current query builder.
551	*/
552	public function rightJoin( $table, $localKey, $operator = null, $referenceKey = null ) {
553	return $this->join( $table, $localKey, $operator, $referenceKey, 'right' );
554	}
555
556	/**
557	* Alias of the `join` method with join type inner.
558	*
559	* @param array\|string $table The table to join. (can contain an alias definition.)
560	* @param string $localKey
561	* @param string $operator The operator (=, !=, <, > etc.)
562	* @param string $referenceKey
563	*
564	* @return Query The current query builder.
565	*/
566	public function innerJoin( $table, $localKey, $operator = null, $referenceKey = null ) {
567	return $this->join( $table, $localKey, $operator, $referenceKey, 'inner' );
568	}
569
570	/**
571	* Alias of the `join` method with join type outer.
572	*
573	* @param array\|string $table The table to join. (can contain an alias definition.)
574	* @param string $localKey
575	* @param string $operator The operator (=, !=, <, > etc.)
576	* @param string $referenceKey
577	*
578	* @return Query The current query builder.
579	*/
580	public function outerJoin( $table, $localKey, $operator = null, $referenceKey = null ) {
581	return $this->join( $table, $localKey, $operator, $referenceKey, 'outer' );
582	}
583
584	/**
585	*
586	* @param $query
587	* @param string $joint
588	*
589	* @since 1.0.1
590	*/
591	public function joinRaw( $query, $joint = 'AND' ) {
592	$this->join['on'][] = [
593	'joint' => $joint,
594	'condition' => $query,
595	];
596
597	return $this;
598	}
599
600
601	/**
602	* Adds group by statement.
603	* ->groupBy('category')
604	* ->gorupBy(['category', 'price'])
605	*
606	* @param string $field
607	*
608	* @return Query this for chaining.
609	* @since 1.0.0
610	*
611	*/
612	public function group_by( $field ) {
613	if ( empty( $field ) ) {
614	return $this;
615	}
616
617	if ( is_array( $field ) ) {
618	foreach ( $field as $groupby ) {
619	$this->group[] = $groupby;
620	}
621	} else {
622	$this->group[] = $field;
623	}
624
625	return $this;
626	}
627
628	/**
629	* Adds having statement.
630	*
631	* ->group_by('user.id')
632	* ->having('count(user.id)>1')
633	*
634	* @param string $statement
635	*
636	* @return Query this for chaining.
637	* @since 1.0.0
638	*
639	*/
640	public function having( $statement ) {
641	if ( ! empty( $statement ) ) {
642	$this->having = $statement;
643	}
644
645	return $this;
646	}
647
648	/**
649	* Adds order by statement.
650	*
651	* ->orderBy('created_at')
652	* ->orderBy('modified_at', 'desc')
653	*
654	* @param string $key
655	* @param string $direction
656	*
657	* @return Query this for chaining.
658	* @throws Exception
659	* @since 1.0.0
660	*
661	*/
662	public function order_by( $key, $direction = 'ASC' ) {
663	$direction = trim( strtoupper( $direction ) );
664	if ( $direction !== 'ASC' && $direction !== 'DESC' ) {
665	$this->exception( 'Invalid direction value.' );
666	}
667	if ( ! empty( $key ) ) {
668	$this->order[] = $key . ' ' . $direction;
669	}
670
671	return $this;
672	}
673
674	/**
675	* Set the query limit
676	*
677	* // limit(<limit>)
678	* ->limit(20)
679	*
680	* // limit(<offset>, <limit>)
681	* ->limit(60, 20)
682	*
683	* @param int $limit
684	* @param int $limit2
685	*
686	* @return Query The current query builder.
687	*/
688	public function limit( $limit, $limit2 = null ) {
689	if ( ! is_null( $limit2 ) ) {
690	$this->offset = (int) $limit;
691	$this->limit = (int) $limit2;
692	} else {
693	$this->limit = (int) $limit;
694	}
695
696	return $this;
697	}
698
699	/**
700	* Adds offset statement.
701	*
702	* ->offset(20)
703	*
704	* @param int $offset
705	*
706	* @return Query this for chaining.
707	*
708	*/
709	public function offset( $offset ) {
710	$this->offset = $offset;
711
712	return $this;
713	}
714
715	/**
716	* Create a query limit based on a page and a page size
717	*
718	* //page(<page>, <size>)
719	* ->page(2, 20)
720	*
721	* @param int $page
722	* @param int $size
723	*
724	* @return Query The current query builder.
725	* @since 1.0.0
726	*/
727	public function page( $page, $size = 20 ) {
728	if ( ( $page = (int) $page ) <= 1 ) {
729	$page = 0;
730	}
731
732	$this->limit = (int) $size;
733	$this->offset = (int) $size * $page;
734
735	return $this;
736	}
737
738	/**
739	* Find something, means select one item by key
740	*
741	* ->find('manikdrmc@gmail.com', 'email')
742	*
743	* @param int $id
744	* @param string $key
745	*
746	* @return mixed
747	*/
748	public function find( $id, $key = 'id' ) {
749	return $this->where( $key, $id )->one();
750	}
751
752	/**
753	* Get the first result ordered by the given key.
754	*
755	* @param string $key By what should the first item be selected? Default is: 'id'
756	*
757	* @return mixed The first result.
758	*/
759	public function first( $key = 'id' ) {
760	return $this->order_by( $key, 'asc' )->one();
761	}
762
763	/**
764	* Get the last result by key
765	*
766	* @param string $key
767	*
768	* @return mixed the last result.
769	*/
770	public function last( $key = 'id' ) {
771	return $this->order_by( $key, 'desc' )->one();
772	}
773
774	/**
775	* Pluck item.
776	* ->find('post_title')
777	* @return Object
778	* @since 1.0.1
779	*/
780	public function pluck() {
781	$selects = func_get_args();
782	$this->select = $selects;
783
784	return $this->get();
785	}
786
787
788	/**
789	* Returns results from builder statements.
790	*
791	* @param int $output WPDB output type.
792	* @param callable $row_map Function callable to filter or map results to.
793	* @param bool $calc_rows Flag that indicates to SQL if rows should be calculated or not.
794	*
795	* @return Object \|\| Array
796	* @since 1.0.0
797	*
798	* @global object $wpdb
799	*
800	*/
801	public function get( $output = OBJECT, $row_map = null, $calc_rows = false ) {
802	global $wpdb;
803	do_action( 'wp_query_builder_get_builder', $this );
804	do_action( 'wp_query_builder_get_builder_' . $this->id, $this );
805
806	$query = '';
807	$this->_query_select( $query, $calc_rows );
808	$this->_query_from( $query );
809	$this->_query_join( $query );
810	$this->_query_where( $query );
811	$this->_query_group( $query );
812	$this->_query_having( $query );
813	$this->_query_order( $query );
814	$this->_query_limit( $query );
815	$this->_query_offset( $query );
816
817	// Process
818	$query = apply_filters( 'wp_query_builder_get_query', $query );
819	$query = apply_filters( 'wp_query_builder_get_query_' . $this->id, $query );
820
821	$results = $wpdb->get_results( $query, $output );
822	if ( $row_map ) {
823	$results = array_map( function ( $row ) use ( &$row_map ) {
824	return call_user_func_array( $row_map, [ $row ] );
825	}, $results );
826	}
827
828	return $results;
829	}
830
831	/**
832	* Sets the limit to 1, executes and returns the first result using get.
833	*
834	* @param string $output
835	*
836	* @return mixed The single result.
837	*/
838	public function one( $output = OBJECT ) {
839	global $wpdb;
840	do_action( 'wp_query_builder_one_builder', $this );
841	do_action( 'wp_query_builder_one_builder_' . $this->id, $this );
842
843	$this->_query_select( $query );
844	$this->_query_from( $query );
845	$this->_query_join( $query );
846	$this->_query_where( $query );
847	$this->_query_group( $query );
848	$this->_query_having( $query );
849	$this->_query_order( $query );
850	$query .= ' LIMIT 1';
851	$this->_query_offset( $query );
852
853	$query = apply_filters( 'wp_query_builder_one_query', $query );
854	$query = apply_filters( 'wp_query_builder_one_query_' . $this->id, $query );
855
856	return $wpdb->get_row( $query, $output );
857	}
858
859	/**
860	* Just return the number of results
861	*
862	* @param string\|int $column
863	*
864	* @return int
865	*/
866	public function count( $column = 1 ) {
867	global $wpdb;
868	do_action( 'wp_query_builder_count_builder', $this );
869	do_action( 'wp_query_builder_count_builder_' . $this->id, $this );
870
871	$query = 'SELECT count(' . $column . ') as `count`';
872	$this->_query_from( $query );
873	$this->_query_join( $query );
874	$this->_query_where( $query );
875	$this->_query_group( $query );
876	$this->_query_having( $query );
877
878	return intval( $wpdb->get_var( $query ) );
879	}
880
881	/**
882	* Just get a single value from the result
883	*
884	* @param string $column The index of the column.
885	* @param bool $calc_rows Flag that indicates to SQL if rows should be calculated or not.
886	*
887	* @return mixed The columns value
888	*/
889	public function column( $column = 0, $calc_rows = false ) {
890	global $wpdb;
891	do_action( 'wp_query_builder_column_builder', $this );
892	do_action( 'wp_query_builder_column_builder_' . $this->id, $this );
893
894	$query = '';
895	$this->_query_select( $query, $calc_rows );
896	$this->_query_from( $query );
897	$this->_query_join( $query );
898	$this->_query_where( $query );
899	$this->_query_group( $query );
900	$this->_query_having( $query );
901	$this->_query_order( $query );
902	$this->_query_limit( $query );
903	$this->_query_offset( $query );
904
905	return $wpdb->get_col( $query, $column );
906	}
907
908	/**
909	* Returns a value.
910	*
911	* @param int $x Column of value to return. Indexed from 0.
912	* @param int $y Row of value to return. Indexed from 0.
913	*
914	* @return mixed
915	* @global object $wpdb
916	*
917	* @since 1.0.0
918	*
919	*/
920	public function value( $x = 0, $y = 0 ) {
921	global $wpdb;
922	do_action( 'wp_query_builder_value_builder', $this );
923	do_action( 'wp_query_builder_value_builder_' . $this->id, $this );
924
925	// Build
926	// Query
927	$query = '';
928	$this->_query_select( $query );
929	$this->_query_from( $query );
930	$this->_query_join( $query );
931	$this->_query_where( $query );
932	$this->_query_group( $query );
933	$this->_query_having( $query );
934	$this->_query_order( $query );
935	$this->_query_limit( $query );
936	$this->_query_offset( $query );
937
938	return $wpdb->get_var( $query, $x, $y );
939	}
940
941
942	/**
943	* Update or insert.
944	*
945	* @param $data
946	*
947	* @return array\|string
948	*/
949	public function updateOrInsert( $data ) {
950	if ( $this->first() ) {
951	return $this->update( $data );
952	} else {
953	return $this->insert( $data );
954	}
955	}
956
957	/**
958	* Find or insert.
959	*
960	* @param $data
961	*
962	* @return array\|string
963	*/
964	public function findOrInsert( $data ) {
965	if ( $this->first() ) {
966	return $this->update( $data );
967	} else {
968	return $this->insert( $data );
969	}
970	}
971
972	/**
973	* Get max value.
974	*
975	* @param $column
976	*
977	* @return int
978	* @since 1.0.1
979	*/
980	public function max( $column ) {
981	global $wpdb;
982	$query = 'SELECT MAX(' . $column . ')';
983	$this->_query_from( $query );
984	$this->_query_join( $query );
985	$this->_query_where( $query );
986	$this->_query_group( $query );
987	$this->_query_having( $query );
988
989	return intval( $wpdb->get_var( $query ) );
990	}
991
992	/**
993	* Get min value.
994	*
995	* @param $column
996	*
997	* @return int
998	* @since 1.0.1
999	*/
1000	public function min( $column ) {
1001	global $wpdb;
1002	$query = 'SELECT MIN(' . $column . ')';
1003	$this->_query_from( $query );
1004	$this->_query_join( $query );
1005	$this->_query_where( $query );
1006	$this->_query_group( $query );
1007	$this->_query_having( $query );
1008
1009	return intval( $wpdb->get_var( $query ) );
1010	}
1011
1012	/**
1013	* Get avg value.
1014	*
1015	* @param $column
1016	*
1017	* @return int
1018	* @since 1.0.1
1019	*/
1020	public function avg( $column ) {
1021	global $wpdb;
1022	$query = 'SELECT AVG(' . $column . ')';
1023	$this->_query_from( $query );
1024	$this->_query_join( $query );
1025	$this->_query_where( $query );
1026	$this->_query_group( $query );
1027	$this->_query_having( $query );
1028
1029	return intval( $wpdb->get_var( $query ) );
1030	}
1031
1032	/**
1033	* Get sum value.
1034	*
1035	* @param $column
1036	*
1037	* @return int
1038	* @since 1.0.1
1039	*/
1040	public function sum( $column ) {
1041	global $wpdb;
1042	$query = 'SELECT SUM(' . $column . ')';
1043	$this->_query_from( $query );
1044	$this->_query_join( $query );
1045	$this->_query_where( $query );
1046	$this->_query_group( $query );
1047	$this->_query_having( $query );
1048
1049	return intval( $wpdb->get_var( $query ) );
1050	}
1051
1052	/**
1053	* Returns flag indicating if query has been executed.
1054	*
1055	* @param string $sql
1056	*
1057	* @return bool
1058	* @since 1.0.0
1059	*
1060	* @global object $wpdb
1061	*
1062	*/
1063	public function query( $sql = '' ) {
1064	global $wpdb;
1065	$query = $sql;
1066	if ( empty( $query ) ) {
1067	$this->_query_select( $query, false );
1068	$this->_query_from( $query );
1069	$this->_query_join( $query );
1070	$this->_query_where( $query );
1071	$this->_query_group( $query );
1072	$this->_query_having( $query );
1073	$this->_query_order( $query );
1074	$this->_query_limit( $query );
1075	$this->_query_offset( $query );
1076	}
1077
1078	return $wpdb->query( $query );
1079	}
1080
1081	/**
1082	* Returns query from builder statements.
1083	*
1084	* @return string
1085	* @since 1.0.0
1086	*/
1087	public function toSql() {
1088	$query = '';
1089	$this->_query_select( $query );
1090	$this->_query_from( $query );
1091	$this->_query_join( $query );
1092	$this->_query_where( $query );
1093	$this->_query_group( $query );
1094	$this->_query_having( $query );
1095	$this->_query_order( $query );
1096	$this->_query_limit( $query );
1097	$this->_query_offset( $query );
1098
1099	return $query;
1100	}
1101
1102	/**
1103	* Returns found rows in last query, if SQL_CALC_FOUND_ROWS is used and is supported.
1104	* @return array
1105	* @global object $wpdb
1106	*
1107	* @since 1.0.0
1108	*
1109	*/
1110	public function rows_found() {
1111	global $wpdb;
1112	$query = 'SELECT FOUND_ROWS()';
1113	// Process
1114	$query = apply_filters( 'wp_query_builder_found_rows_query', $query );
1115	$query = apply_filters( 'wp_query_builder_found_rows_query_' . $this->id, $query );
1116
1117	return $wpdb->get_var( $query );
1118	}
1119
1120	/**
1121	* Returns flag indicating if delete query has been executed.
1122	* @return bool
1123	* @global object $wpdb
1124	*
1125	* @since 1.0.0
1126	*
1127	*/
1128	public function delete() {
1129	global $wpdb;
1130	do_action( 'wp_query_builder_delete_builder', $this );
1131	do_action( 'wp_query_builder_delete_builder_' . $this->id, $this );
1132
1133	$query = '';
1134	$this->_query_delete( $query );
1135	$this->_query_from( $query );
1136	$this->_query_join( $query );
1137	$this->_query_where( $query );
1138
1139	return $wpdb->query( $query );
1140	}
1141
1142	/**
1143	* Update
1144	* @return bool
1145	* @global object $wpdb
1146	*
1147	* @since 1.0.0
1148	*
1149	*/
1150	public function update( $data ) {
1151	global $wpdb;
1152	$conditions = '';
1153	$this->_query_where( $conditions );
1154
1155	if ( empty( trim( $conditions ) ) ) {
1156	return false;
1157	}
1158
1159	$fields = array();
1160	foreach ( $data as $column => $value ) {
1161
1162	if ( is_null( $value ) ) {
1163	$fields[] = "`$column` = NULL";
1164	continue;
1165	}
1166
1167	$fields[] = "`$column` = " . $wpdb->prepare( is_numeric( $value ) ? '%d' : '%s', $value );
1168	}
1169
1170	$table = trim( $this->from );
1171	$fields = implode( ', ', $fields );
1172
1173	$query = "UPDATE `$table` SET $fields $conditions";
1174
1175	return $wpdb->query( $query );
1176	}
1177
1178	/**
1179	* Insert data.
1180	*
1181	* @param $data
1182	* @param array $format
1183	*
1184	* @return bool\|int
1185	* @since 1.0.1
1186	*/
1187	public function insert( $data, $format = array() ) {
1188	global $wpdb;
1189
1190	if ( false !== $wpdb->insert( trim( $this->from ), $data, $format ) ) {
1191	return $wpdb->insert_id;
1192	};
1193
1194	return false;
1195	}
1196
1197	/**
1198	* Return a cloned object from current builder.
1199	*
1200	* @return Query
1201	* @since 1.0.0
1202	*/
1203	public function copy() {
1204	return clone( $this );
1205	}
1206
1207	/**
1208	* Builds query's select statement.
1209	*
1210	* @param string &$query
1211	* @param bool $calc_rows
1212	*
1213	* @since 1.0.0
1214	*
1215	*/
1216	private function _query_select( &$query, $calc_rows = false ) {
1217	$query = 'SELECT ' . ( $calc_rows ? 'SQL_CALC_FOUND_ROWS ' : '' ) . (
1218	is_array( $this->select ) && count( $this->select )
1219	? implode( ',', $this->select )
1220	: '*'
1221	);
1222	}
1223
1224	/**
1225	* Builds query's from statement.
1226	*
1227	* @param string &$query
1228	*
1229	* @since 1.0.0
1230	*
1231	*/
1232	private function _query_from( &$query ) {
1233	$query .= ' FROM ' . $this->from;
1234	}
1235
1236	/**
1237	* Builds query's join statement.
1238	*
1239	* @param string &$query
1240	*
1241	* @since 1.0.0
1242	*
1243	*/
1244	private function _query_join( &$query ) {
1245	foreach ( $this->join as $join ) {
1246	$query .= ( ! empty( $join['type'] ) ? ' ' . $join['type'] . ' JOIN ' : ' JOIN ' ) . $join['table'];
1247	for ( $i = 0; $i < count( $join['on'] ); ++ $i ) {
1248	$query .= ( $i === 0 ? ' ON ' : ' ' . $join['on'][ $i ]['joint'] . ' ' )
1249	. $join['on'][ $i ]['condition'];
1250	}
1251	}
1252	}
1253
1254	/**
1255	* Builds query's where statement.
1256	*
1257	* @param string &$query
1258	*
1259	* @since 1.0.0
1260	*
1261	*/
1262	public function _query_where( &$query ) {
1263	for ( $i = 0; $i < count( $this->where ); ++ $i ) {
1264	$query .= ( $i === 0 ? ' WHERE ' : ' ' . $this->where[ $i ]['joint'] . ' ' )
1265	. $this->where[ $i ]['condition'];
1266	}
1267	}
1268
1269	/**
1270	* Builds query's group by statement.
1271	*
1272	* @param string &$query
1273	*
1274	* @since 1.0.0
1275	*
1276	*/
1277	private function _query_group( &$query ) {
1278	if ( count( $this->group ) ) {
1279	$query .= ' GROUP BY ' . implode( ',', $this->group );
1280	}
1281	}
1282
1283	/**
1284	* Builds query's having statement.
1285	*
1286	* @param string &$query
1287	*
1288	* @since 1.0.0
1289	*
1290	*/
1291	private function _query_having( &$query ) {
1292	if ( $this->having ) {
1293	$query .= ' HAVING ' . $this->having;
1294	}
1295	}
1296
1297	/**
1298	* Builds query's order by statement.
1299	*
1300	* @param string &$query
1301	*
1302	* @since 1.0.0
1303	*
1304	*/
1305	private function _query_order( &$query ) {
1306	if ( count( $this->order ) ) {
1307	$query .= ' ORDER BY ' . implode( ',', $this->order );
1308	}
1309	}
1310
1311	/**
1312	* Builds query's limit statement.
1313	*
1314	* @param string &$query
1315	*
1316	* @global object $wpdb
1317	*
1318	* @since 1.0.0
1319	*
1320	*/
1321	private function _query_limit( &$query ) {
1322	global $wpdb;
1323	if ( $this->limit ) {
1324	$query .= $wpdb->prepare( ' LIMIT %d', $this->limit );
1325	}
1326	}
1327
1328	/**
1329	* Builds query's offset statement.
1330	*
1331	* @param string &$query
1332	*
1333	* @global object $wpdb
1334	*
1335	* @since 1.0.0
1336	*
1337	*/
1338	private function _query_offset( &$query ) {
1339	global $wpdb;
1340	if ( $this->offset ) {
1341	$query .= $wpdb->prepare( ' OFFSET %d', $this->offset );
1342	}
1343	}
1344
1345	/**
1346	* Builds query's delete statement.
1347	*
1348	* @param string &$query
1349	*
1350	* @since 1.0.0
1351	*
1352	*/
1353	private function _query_delete( &$query ) {
1354	$query .= trim( 'DELETE ' . ( count( $this->join )
1355	? preg_replace( '/\s[aA][sS][\s\S]+.*?/', '', $this->from )
1356	: ''
1357	) );
1358	}
1359
1360	/**
1361	* Decide whether a right-hand operand is a column reference rather than a value.
1362	*
1363	* `where()` and `join()` accept either on the right: `join('nx_stats b', 'b.nx_id',
1364	* '=', 'a.nx_id')` compares two columns, while `where('title', 'LIKE', $search)`
1365	* compares a column to a value. Only a value may be bound, so the two have to be
1366	* told apart -- and the test used to be "does it contain a dot, or the table
1367	* prefix?". That is true of `a.nx_id`, but it is also true of any search term
1368	* carrying a dot, so `?s=.%27%20UNION%20SELECT...` was emitted into the statement
1369	* verbatim and the LIKE became an injection point.
1370	*
1371	* The test is now the shape of a qualified identifier -- `table.column`, each half
1372	* optionally backquoted -- so a real column reference still passes through unbound
1373	* while anything carrying a quote, space, comment marker or wildcard falls through
1374	* to {@see self::bind_value()}.
1375	*
1376	* The qualifier is required on purpose. A bare `published` is far more likely to be
1377	* a value than a column, and it was always bound under the old test too, so treating
1378	* unqualified names as columns here would silently turn `where('status',
1379	* 'published')` into a comparison against a non-existent column.
1380	*
1381	* @param mixed $value Right-hand operand.
1382	*
1383	* @return bool
1384	*/
1385	private function is_column_reference( $value ) {
1386	if ( ! is_string( $value ) \|\| '' === $value ) {
1387	return false;
1388	}
1389
1390	return (bool) preg_match( '/^`?[A-Za-z_][A-Za-z0-9_]`?\.`?[A-Za-z_][A-Za-z0-9_]`?$/', $value );
1391	}
1392
1393	/**
1394	* Bind a value into the statement through $wpdb->prepare().
1395	*
1396	* @param mixed $value Value to bind.
1397	*
1398	* @return string Quoted, escaped SQL literal.
1399	*/
1400	private function bind_value( $value ) {
1401	global $wpdb;
1402
1403	if ( null === $value ) {
1404	return 'null';
1405	}
1406	if ( is_bool( $value ) ) {
1407	return $wpdb->prepare( '%d', $value ? 1 : 0 );
1408	}
1409	if ( ! is_numeric( $value ) ) {
1410	return $wpdb->prepare( '%s', $value );
1411	}
1412
1413	// %d on a float silently truncates it, so anything with a decimal point or an
1414	// exponent takes %f. Integer strings -- '007' included -- keep %d as before.
1415	return $wpdb->prepare( ( is_float( $value ) \|\| false !== strpbrk( (string) $value, '.eE' ) ) ? '%f' : '%d', $value );
1416	}
1417
1418	/**
1419	* Sanitize value.
1420	*
1421	* @param string\|bool $callback Sanitize callback.
1422	* @param mixed $value
1423	*
1424	* @return mixed
1425	* @since 1.0.0
1426	*
1427	*/
1428	private function sanitize_value( $callback, $value ) {
1429	if ( $callback === true ) {
1430	$callback = ( is_numeric( $value ) && strpos( $value, '.' ) !== false )
1431	? 'floatval'
1432	: ( is_numeric( $value )
1433	? 'intval'
1434	: ( is_string( $value )
1435	? 'sanitize_text_field'
1436	: null
1437	)
1438	);
1439	}
1440	if ( strpos( $callback, '_builder' ) !== false ) {
1441	$callback = [ &$this, $callback ];
1442	}
1443	if ( is_array( $value ) ) {
1444	for ( $i = count( $value ) - 1; $i >= 0; -- $i ) {
1445	$value[ $i ] = $this->sanitize_value( true, $value[ $i ] );
1446	}
1447	}
1448
1449	return $callback && is_callable( $callback ) ? call_user_func_array( $callback, [ $value ] ) : $value;
1450	}
1451
1452	/**
1453	* @param $message
1454	*
1455	* @throws \Exception
1456	* @since 1.0.0
1457	*/
1458	private function exception( $message ) {
1459	throw new \Exception( $message );
1460	}
1461	}
1462