@@ 318-382 (lines=65) @@ | ||
315 | * @type string $where SQL fragment to append to the main WHERE clause. |
|
316 | * } |
|
317 | */ |
|
318 | protected function get_sql_for_query( &$query, $depth = 0 ) { |
|
319 | $sql_chunks = array( |
|
320 | 'join' => array(), |
|
321 | 'where' => array(), |
|
322 | ); |
|
323 | ||
324 | $sql = array( |
|
325 | 'join' => '', |
|
326 | 'where' => '', |
|
327 | ); |
|
328 | ||
329 | $indent = ''; |
|
330 | for ( $i = 0; $i < $depth; $i++ ) { |
|
331 | $indent .= " "; |
|
332 | } |
|
333 | ||
334 | foreach ( $query as $key => &$clause ) { |
|
335 | if ( 'relation' === $key ) { |
|
336 | $relation = $query['relation']; |
|
337 | } elseif ( is_array( $clause ) ) { |
|
338 | ||
339 | // This is a first-order clause. |
|
340 | if ( $this->is_first_order_clause( $clause ) ) { |
|
341 | $clause_sql = $this->get_sql_for_clause( $clause, $query ); |
|
342 | ||
343 | $where_count = count( $clause_sql['where'] ); |
|
344 | if ( ! $where_count ) { |
|
345 | $sql_chunks['where'][] = ''; |
|
346 | } elseif ( 1 === $where_count ) { |
|
347 | $sql_chunks['where'][] = $clause_sql['where'][0]; |
|
348 | } else { |
|
349 | $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )'; |
|
350 | } |
|
351 | ||
352 | $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] ); |
|
353 | // This is a subquery, so we recurse. |
|
354 | } else { |
|
355 | $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 ); |
|
356 | ||
357 | $sql_chunks['where'][] = $clause_sql['where']; |
|
358 | $sql_chunks['join'][] = $clause_sql['join']; |
|
359 | } |
|
360 | } |
|
361 | } |
|
362 | ||
363 | // Filter to remove empties. |
|
364 | $sql_chunks['join'] = array_filter( $sql_chunks['join'] ); |
|
365 | $sql_chunks['where'] = array_filter( $sql_chunks['where'] ); |
|
366 | ||
367 | if ( empty( $relation ) ) { |
|
368 | $relation = 'AND'; |
|
369 | } |
|
370 | ||
371 | // Filter duplicate JOIN clauses and combine into a single string. |
|
372 | if ( ! empty( $sql_chunks['join'] ) ) { |
|
373 | $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) ); |
|
374 | } |
|
375 | ||
376 | // Generate a single WHERE clause with proper brackets and indentation. |
|
377 | if ( ! empty( $sql_chunks['where'] ) ) { |
|
378 | $sql['where'] = '( ' . "\n " . $indent . implode( ' ' . "\n " . $indent . $relation . ' ' . "\n " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')'; |
|
379 | } |
|
380 | ||
381 | return $sql; |
|
382 | } |
|
383 | ||
384 | /** |
|
385 | * Generate SQL JOIN and WHERE clauses for a "first-order" query clause. |
@@ 413-477 (lines=65) @@ | ||
410 | * @type string $where SQL fragment to append to the main WHERE clause. |
|
411 | * } |
|
412 | */ |
|
413 | protected function get_sql_for_query( &$query, $depth = 0 ) { |
|
414 | $sql_chunks = array( |
|
415 | 'join' => array(), |
|
416 | 'where' => array(), |
|
417 | ); |
|
418 | ||
419 | $sql = array( |
|
420 | 'join' => '', |
|
421 | 'where' => '', |
|
422 | ); |
|
423 | ||
424 | $indent = ''; |
|
425 | for ( $i = 0; $i < $depth; $i++ ) { |
|
426 | $indent .= " "; |
|
427 | } |
|
428 | ||
429 | foreach ( $query as $key => &$clause ) { |
|
430 | if ( 'relation' === $key ) { |
|
431 | $relation = $query['relation']; |
|
432 | } elseif ( is_array( $clause ) ) { |
|
433 | ||
434 | // This is a first-order clause. |
|
435 | if ( $this->is_first_order_clause( $clause ) ) { |
|
436 | $clause_sql = $this->get_sql_for_clause( $clause, $query, $key ); |
|
437 | ||
438 | $where_count = count( $clause_sql['where'] ); |
|
439 | if ( ! $where_count ) { |
|
440 | $sql_chunks['where'][] = ''; |
|
441 | } elseif ( 1 === $where_count ) { |
|
442 | $sql_chunks['where'][] = $clause_sql['where'][0]; |
|
443 | } else { |
|
444 | $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )'; |
|
445 | } |
|
446 | ||
447 | $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] ); |
|
448 | // This is a subquery, so we recurse. |
|
449 | } else { |
|
450 | $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 ); |
|
451 | ||
452 | $sql_chunks['where'][] = $clause_sql['where']; |
|
453 | $sql_chunks['join'][] = $clause_sql['join']; |
|
454 | } |
|
455 | } |
|
456 | } |
|
457 | ||
458 | // Filter to remove empties. |
|
459 | $sql_chunks['join'] = array_filter( $sql_chunks['join'] ); |
|
460 | $sql_chunks['where'] = array_filter( $sql_chunks['where'] ); |
|
461 | ||
462 | if ( empty( $relation ) ) { |
|
463 | $relation = 'AND'; |
|
464 | } |
|
465 | ||
466 | // Filter duplicate JOIN clauses and combine into a single string. |
|
467 | if ( ! empty( $sql_chunks['join'] ) ) { |
|
468 | $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) ); |
|
469 | } |
|
470 | ||
471 | // Generate a single WHERE clause with proper brackets and indentation. |
|
472 | if ( ! empty( $sql_chunks['where'] ) ) { |
|
473 | $sql['where'] = '( ' . "\n " . $indent . implode( ' ' . "\n " . $indent . $relation . ' ' . "\n " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')'; |
|
474 | } |
|
475 | ||
476 | return $sql; |
|
477 | } |
|
478 | ||
479 | /** |
|
480 | * Generate SQL JOIN and WHERE clauses for a first-order query clause. |
@@ 619-683 (lines=65) @@ | ||
616 | * @type string $where SQL fragment to append to the main WHERE clause. |
|
617 | * } |
|
618 | */ |
|
619 | protected function get_sql_for_query( $query, $depth = 0 ) { |
|
620 | $sql_chunks = array( |
|
621 | 'join' => array(), |
|
622 | 'where' => array(), |
|
623 | ); |
|
624 | ||
625 | $sql = array( |
|
626 | 'join' => '', |
|
627 | 'where' => '', |
|
628 | ); |
|
629 | ||
630 | $indent = ''; |
|
631 | for ( $i = 0; $i < $depth; $i++ ) { |
|
632 | $indent .= " "; |
|
633 | } |
|
634 | ||
635 | foreach ( $query as $key => $clause ) { |
|
636 | if ( 'relation' === $key ) { |
|
637 | $relation = $query['relation']; |
|
638 | } elseif ( is_array( $clause ) ) { |
|
639 | ||
640 | // This is a first-order clause. |
|
641 | if ( $this->is_first_order_clause( $clause ) ) { |
|
642 | $clause_sql = $this->get_sql_for_clause( $clause, $query ); |
|
643 | ||
644 | $where_count = count( $clause_sql['where'] ); |
|
645 | if ( ! $where_count ) { |
|
646 | $sql_chunks['where'][] = ''; |
|
647 | } elseif ( 1 === $where_count ) { |
|
648 | $sql_chunks['where'][] = $clause_sql['where'][0]; |
|
649 | } else { |
|
650 | $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )'; |
|
651 | } |
|
652 | ||
653 | $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] ); |
|
654 | // This is a subquery, so we recurse. |
|
655 | } else { |
|
656 | $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 ); |
|
657 | ||
658 | $sql_chunks['where'][] = $clause_sql['where']; |
|
659 | $sql_chunks['join'][] = $clause_sql['join']; |
|
660 | } |
|
661 | } |
|
662 | } |
|
663 | ||
664 | // Filter to remove empties. |
|
665 | $sql_chunks['join'] = array_filter( $sql_chunks['join'] ); |
|
666 | $sql_chunks['where'] = array_filter( $sql_chunks['where'] ); |
|
667 | ||
668 | if ( empty( $relation ) ) { |
|
669 | $relation = 'AND'; |
|
670 | } |
|
671 | ||
672 | // Filter duplicate JOIN clauses and combine into a single string. |
|
673 | if ( ! empty( $sql_chunks['join'] ) ) { |
|
674 | $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) ); |
|
675 | } |
|
676 | ||
677 | // Generate a single WHERE clause with proper brackets and indentation. |
|
678 | if ( ! empty( $sql_chunks['where'] ) ) { |
|
679 | $sql['where'] = '( ' . "\n " . $indent . implode( ' ' . "\n " . $indent . $relation . ' ' . "\n " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')'; |
|
680 | } |
|
681 | ||
682 | return $sql; |
|
683 | } |
|
684 | ||
685 | /** |
|
686 | * Turns a single date clause into pieces for a WHERE clause. |