code_junkie

Converting a Google search query to a PostgreSQL “tsquery”

Nov 11th, 2011
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.70 KB | None | 0 0
  1. ("used cars" OR "new cars") -ford -mistubishi
  2.  
  3. ('used cars' | 'new cars') & !ford & !mistubishi
  4.  
  5. ((color:blue OR "4x4") OR style:coupe) -color:red used
  6.  
  7. WHERE to_tsvector(description) MATCH to_tsquery('used')
  8. AND color <> 'red'
  9. AND ( (color = 'blue' OR to_tsvector(description) MATCH to_tsquery('4x4') )
  10. OR style = 'coupe'
  11. );
  12.  
  13. <?php
  14.  
  15. ini_set( "display_errors", "on" );
  16. error_reporting( E_ALL );
  17.  
  18. interface ILexer
  19. {
  20. public function execute( $str );
  21. public function getTokens();
  22. }
  23.  
  24. interface IParser
  25. {
  26. public function __construct( iLexer $lexer );
  27. public function parse( $input );
  28. public function addToken( $token );
  29. }
  30.  
  31. class GoogleQueryLexer implements ILexer
  32. {
  33. private $tokenStack = array();
  34.  
  35. public function execute( $str )
  36. {
  37. $chars = str_split( $str );
  38. foreach ( $chars as $char )
  39. {
  40. // add to self::$tokenStack per your rules
  41. }
  42.  
  43. //'("used cars" OR "new cars") -ford -mistubishi'
  44. $this->tokenStack = array(
  45. '('
  46. , 'used cars'
  47. , 'or new cars'
  48. , ')'
  49. , '-ford'
  50. , '-mitsubishi'
  51. );
  52. }
  53.  
  54. public function getTokens()
  55. {
  56. return $this->tokenStack;
  57. }
  58. }
  59.  
  60. class GoogleQueryParser implements IParser
  61. {
  62. protected $lexer;
  63.  
  64. public function __construct( iLexer $lexer )
  65. {
  66. $this->lexer = $lexer;
  67. }
  68.  
  69. public function addToken( $token )
  70. {
  71. $this->tokenStack[] = $token;
  72. }
  73.  
  74. public function parse( $input )
  75. {
  76. $this->lexer->execute( $input );
  77. $tokens = $this->lexer->getTokens();
  78.  
  79. $expression = new QueryExpression();
  80.  
  81. foreach ( $tokens as $token )
  82. {
  83. $expression = $this->processToken( $token, $expression );
  84. }
  85.  
  86. return $expression;
  87. }
  88.  
  89. protected function processToken( $token, QueryExpression $expression )
  90. {
  91. switch ( $token )
  92. {
  93. case '(':
  94. return $expression->initiateSubExpression();
  95. break;
  96. case ')':
  97. return $expression->getParentExpression();
  98. break;
  99. default:
  100. $modifier = $token[0];
  101. $phrase = substr( $token, 1 );
  102. switch ( $modifier )
  103. {
  104. case '-':
  105. $expression->addExclusionPhrase( $phrase );
  106. break;
  107. case '+':
  108. $expression->addPhrase( $phrase );
  109. break;
  110. default:
  111. $operator = trim( substr( $token, 0, strpos( $token, ' ' ) ) );
  112. $phrase = trim( substr( $token, strpos( $token, ' ' ) ) );
  113. switch ( strtolower( $operator ) )
  114. {
  115. case 'and':
  116. $expression->addAndPhrase( $phrase );
  117. break;
  118. case 'or':
  119. $expression->addOrPhrase( $phrase );
  120. break;
  121. default:
  122. $expression->addPhrase( $token );
  123. }
  124. }
  125. }
  126. return $expression;
  127. }
  128. }
  129.  
  130. class QueryExpression
  131. {
  132. protected $phrases = array();
  133. protected $subExpressions = array();
  134. protected $parent;
  135.  
  136. public function __construct( $parent=null )
  137. {
  138. $this->parent = $parent;
  139. }
  140.  
  141. public function initiateSubExpression()
  142. {
  143. $expression = new self( $this );
  144. $this->subExpressions[] = $expression;
  145. return $expression;
  146. }
  147.  
  148. public function getPhrases()
  149. {
  150. return $this->phrases;
  151. }
  152.  
  153. public function getSubExpressions()
  154. {
  155. return $this->subExpressions;
  156. }
  157.  
  158. public function getParentExpression()
  159. {
  160. return $this->parent;
  161. }
  162.  
  163. protected function addQueryPhrase( QueryPhrase $phrase )
  164. {
  165. $this->phrases[] = $phrase;
  166. }
  167.  
  168. public function addPhrase( $input )
  169. {
  170. $this->addQueryPhrase( new QueryPhrase( $input ) );
  171. }
  172.  
  173. public function addOrPhrase( $input )
  174. {
  175. $this->addQueryPhrase( new QueryPhrase( $input, QueryPhrase::MODE_OR ) );
  176. }
  177.  
  178. public function addAndPhrase( $input )
  179. {
  180. $this->addQueryPhrase( new QueryPhrase( $input, QueryPhrase::MODE_AND ) );
  181. }
  182.  
  183. public function addExclusionPhrase( $input )
  184. {
  185. $this->addQueryPhrase( new QueryPhrase( $input, QueryPhrase::MODE_EXCLUDE ) );
  186. }
  187. }
  188.  
  189. class QueryPhrase
  190. {
  191. const MODE_DEFAULT = 1;
  192. const MODE_OR = 2;
  193. const MODE_AND = 3;
  194. const MODE_EXCLUDE = 4;
  195.  
  196. protected $phrase;
  197. protected $mode;
  198.  
  199. public function __construct( $input, $mode=self::MODE_DEFAULT )
  200. {
  201. $this->phrase = $input;
  202. $this->mode = $mode;
  203. }
  204.  
  205. public function getMode()
  206. {
  207. return $this->mode;
  208. }
  209.  
  210. public function __toString()
  211. {
  212. return $this->phrase;
  213. }
  214. }
  215.  
  216. class TsqueryBuilder
  217. {
  218. protected $expression;
  219. protected $query;
  220.  
  221. public function __construct( QueryExpression $expression )
  222. {
  223. $this->query = trim( $this->processExpression( $expression ), ' &|' );
  224. }
  225.  
  226. public function getResult()
  227. {
  228. return $this->query;
  229. }
  230.  
  231. protected function processExpression( QueryExpression $expression )
  232. {
  233. $query = '';
  234. $phrases = $expression->getPhrases();
  235. $subExpressions = $expression->getSubExpressions();
  236.  
  237. foreach ( $phrases as $phrase )
  238. {
  239. $format = "'%s' ";
  240. switch ( $phrase->getMode() )
  241. {
  242. case QueryPhrase::MODE_AND :
  243. $format = "& '%s' ";
  244. break;
  245. case QueryPhrase::MODE_OR :
  246. $format = "| '%s' ";
  247. break;
  248. case QueryPhrase::MODE_EXCLUDE :
  249. $format = "& !'%s' ";
  250. break;
  251. }
  252. $query .= sprintf( $format, str_replace( "'", "\'", $phrase ) );
  253. }
  254.  
  255. foreach ( $subExpressions as $subExpression )
  256. {
  257. $query .= "& (" . $this->processExpression( $subExpression ) . ")";
  258. }
  259. return $query;
  260. }
  261. }
  262.  
  263. $parser = new GoogleQueryParser( new GoogleQueryLexer() );
  264.  
  265. $queryBuilder = new TsqueryBuilder( $parser->parse( '("used cars" OR "new cars") -ford -mistubishi' ) );
  266.  
  267. echo $queryBuilder->getResult();
Add Comment
Please, Sign In to add comment