CommonTableExpressionQuery::runQuery()   A
last analyzed

Complexity

Conditions 1
Paths 1

Size

Total Lines 14

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 6
CRAP Score 1

Importance

Changes 0
Metric Value
cc 1
nc 1
nop 2
dl 0
loc 14
ccs 6
cts 6
cp 1
crap 1
rs 9.7998
c 0
b 0
f 0
1
<?php
2
3
/**
4
 * This class will use the Common Table Expression
5
 * technique to make a recursive request on the database.
6
 * On mysql it will fallback to a stored procedure doing the same.
7
 *
8
 * The choice of a Common Table Expression or a standard query is done automatically
9
 */
10
namespace Rocket\Taxonomy\Utils;
11
12
use Illuminate\Support\Facades\DB;
13
14
/**
15
 * Class using the DB's internal Recursive query mechanism to query term hierarchies.
16
 */
17
class CommonTableExpressionQuery extends RecursiveQuery implements RecursiveQueryInterface
18
{
19
    /**
20
     * Execute the query with the current PDO connection.
21
     * Will log the time taken manually
22
     *
23
     * @param string $raw_query
24
     * @param int $id
25
     * @return \Illuminate\Support\Collection
26
     */
27 54
    protected function runQuery($raw_query, $id)
28
    {
29 54
        $query = str_replace(':id', $id, $raw_query);
30
31 54
        $start = microtime(true);
32
33
        // Does not work as a prepared statement; we have to execute it directly
34 54
        $results = DB::getReadPdo()->query($query)->fetchAll(\PDO::FETCH_OBJ);
35
36
        // Log the query manually
37 54
        DB::logQuery($raw_query, [$id], round((microtime(true) - $start) * 1000, 2));
38
39 54
        return $results;
40
    }
41
42
    /**
43
     * Prepare the complete query to run on the DB to get the data recursively.
44
     *
45
     * @param string $initial The initial query bootstrapping the recursive query
46
     * @param string $recursive The query to dig deeper in the hierarchy
47
     * @return string
48
     */
49 54
    protected function assembleQuery($initial, $recursive)
50
    {
51 54
        $tmp_tbl = 'name_tree';
52 54
        $recursive = str_replace(':tmp_tbl', $tmp_tbl, $recursive);
53
54 54
        $final = "select distinct * from $tmp_tbl";
55
56 54
        if (DB::connection()->getDriverName() == 'mysql') {
57 19
            return "Call WITH_EMULATOR('$tmp_tbl', '$initial', '$recursive', '$final', 0, 'ENGINE=MEMORY');";
58
        }
59
60 35
        return "WITH RECURSIVE $tmp_tbl AS ($initial UNION ALL $recursive) $final;";
61
    }
62
63
    /**
64
     * Get all ancestors of a term
65
     *
66
     * @param int $id The term ID
67
     * @return \Illuminate\Support\Collection
68
     */
69 30
    public function getAncestry($id)
70
    {
71 30
        $tbl = $this->hierarchyTable;
72 30
        $recursive = "SELECT c.term_id, c.parent_id from $tbl as c join :tmp_tbl as p on p.parent_id = c.term_id";
73
74 30
        $raw_query = $this->assembleQuery($this->getAncestryInitialQuery(), $recursive);
75
76 30
        return $this->runQuery($raw_query, $id);
77
    }
78
79
    /**
80
     * Get all descendants of a term.
81
     *
82
     * @param int $id The term ID
83
     * @return \Illuminate\Support\Collection
84
     */
85 30
    public function getDescent($id)
86
    {
87 30
        $tbl = $this->hierarchyTable;
88 30
        $recursive = "SELECT c.term_id, c.parent_id from $tbl as c join :tmp_tbl as p on c.parent_id = p.term_id";
89
90 30
        $raw_query = $this->assembleQuery($this->getDescentInitialQuery(), $recursive);
91
92 30
        return $this->runQuery($raw_query, $id);
93
    }
94
}
95