FACET clause. This Sphinx specific extension enables faceted search with subtree optimization. It is capable of returning multiple result sets with a single SQL statement, without the need for complicated multi-queries. FACET clauses should be written at the very end of SELECT statements with spaces between them.
FACET {expr_list} [BY {expr_list}] [ORDER BY {expr | FACET()} {ASC | DESC}] [LIMIT [offset,] count]
SELECT * FROM test FACET brand_id FACET categories;
SELECT * FROM test FACET brand_name BY brand_id ORDER BY brand_name ASC FACET property;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
$sql = <<<SQL SELECT * $tag_fields FROM index_rt {$where} {$order} {$limit} OPTION field_weights=(content=20, description=10,parts_tag_list=5) FACET category_id ORDER BY category_id ASC FACET aid LIMIT 1000 FACET bid ORDER BY COUNT(*) DESC LIMIT 0 FACET c_num > 0 FACET 0 ; SQL; $result_sets = array( 'questions' => array(), 'category_count' => array(), ); $count_fields = array( 'category_count', 'a_count', 'b_count', 'c_count', 'total', ); # WHAT A HACK!! $mysqli = $db->conn_id; if ($mysqli->multi_query($sql)) { if ($result = $mysqli->store_result()) { # while ($row = $result->fetch_assoc()) { $result_sets['ids'][] = $row['id']; } $result->free(); } $i = 0; while ($mysqli->more_results() && $mysqli->next_result()) { if ($result = $mysqli->store_result()) { # while ($row = $result->fetch_row()) { $result_sets[$count_fields[$i]][$row[0]] = $row[1]; } $result->free(); } $i++; } if (isset($result_sets['total'][0])) { $result_sets['total'] = $result_sets['total'][0]; } } |
SphinxQL FACET is Awesome by @sskaje: https://sskaje.me/2015/06/sphinxql-facet-awesome/
Link to this post!