Converting a Google search query to PostgreSQL "tsquery"

Question

How can I convert a Google search query to what I can pass PostgreSQL to_tsquery ()?

If there is no existing library, how should I parse a Google search query in PHP?

For example, I would like to use the following Google-ish search query:

("used cars" OR "new cars") -ford -mistubishi 

And turn it into a string other than to_tsquery ():

 ('used cars' | 'new cars') & !ford & !mistubishi 

I can lure this out with regular expressions, but this is the best I can do. Is there any reliable method of lexical analysis? I would also like to support advanced search operators (for example, Google site and intitle :), which will be applied to various fields of the database and, therefore, they will need to be separated from the tsquery string.

UPDATE: I understand that with special operators, this is a Google WHERE clause conversion to SQL, not a Google conversion to tsquery. But the WHERE clause may contain one or more tsqueries.

For example, a Google-style query:

 ((color:blue OR "4x4") OR style:coupe) -color:red used 

Should result in a SQL WHERE clause:

 WHERE to_tsvector(description) MATCH to_tsquery('used') AND color <> 'red' AND ( (color = 'blue' OR to_tsvector(description) MATCH to_tsquery('4x4') ) OR style = 'coupe' ); 

I'm not sure if this is possible with regular expression?

Answer 1

Honestly, I think regular expressions are a way to go with something like that. The same thing, it was a fun exercise. The code below is very prototype - in fact, you will see that I did not even implement lexer itself - I just faked the output. I would like to continue it, but today I just do not have free time.

In addition, there is definitely a lot more work in terms of supporting other types of search operators, etc.

Basically, the idea is that a certain type of query is lexed and then parsed into a common format (in this case, an instance of QueryExpression), which is then output back as another type of query.

 <?php ini_set( "display_errors", "on" ); error_reporting( E_ALL ); interface ILexer { public function execute( $str ); public function getTokens(); } interface IParser { public function __construct( iLexer $lexer ); public function parse( $input ); public function addToken( $token ); } class GoogleQueryLexer implements ILexer { private $tokenStack = array(); public function execute( $str ) { $chars = str_split( $str ); foreach ( $chars as $char ) { // add to self::$tokenStack per your rules } //'("used cars" OR "new cars") -ford -mistubishi' $this->tokenStack = array( '(' , 'used cars' , 'or new cars' , ')' , '-ford' , '-mitsubishi' ); } public function getTokens() { return $this->tokenStack; } } class GoogleQueryParser implements IParser { protected $lexer; public function __construct( iLexer $lexer ) { $this->lexer = $lexer; } public function addToken( $token ) { $this->tokenStack[] = $token; } public function parse( $input ) { $this->lexer->execute( $input ); $tokens = $this->lexer->getTokens(); $expression = new QueryExpression(); foreach ( $tokens as $token ) { $expression = $this->processToken( $token, $expression ); } return $expression; } protected function processToken( $token, QueryExpression $expression ) { switch ( $token ) { case '(': return $expression->initiateSubExpression(); break; case ')': return $expression->getParentExpression(); break; default: $modifier = $token[0]; $phrase = substr( $token, 1 ); switch ( $modifier ) { case '-': $expression->addExclusionPhrase( $phrase ); break; case '+': $expression->addPhrase( $phrase ); break; default: $operator = trim( substr( $token, 0, strpos( $token, ' ' ) ) ); $phrase = trim( substr( $token, strpos( $token, ' ' ) ) ); switch ( strtolower( $operator ) ) { case 'and': $expression->addAndPhrase( $phrase ); break; case 'or': $expression->addOrPhrase( $phrase ); break; default: $expression->addPhrase( $token ); } } } return $expression; } } class QueryExpression { protected $phrases = array(); protected $subExpressions = array(); protected $parent; public function __construct( $parent=null ) { $this->parent = $parent; } public function initiateSubExpression() { $expression = new self( $this ); $this->subExpressions[] = $expression; return $expression; } public function getPhrases() { return $this->phrases; } public function getSubExpressions() { return $this->subExpressions; } public function getParentExpression() { return $this->parent; } protected function addQueryPhrase( QueryPhrase $phrase ) { $this->phrases[] = $phrase; } public function addPhrase( $input ) { $this->addQueryPhrase( new QueryPhrase( $input ) ); } public function addOrPhrase( $input ) { $this->addQueryPhrase( new QueryPhrase( $input, QueryPhrase::MODE_OR ) ); } public function addAndPhrase( $input ) { $this->addQueryPhrase( new QueryPhrase( $input, QueryPhrase::MODE_AND ) ); } public function addExclusionPhrase( $input ) { $this->addQueryPhrase( new QueryPhrase( $input, QueryPhrase::MODE_EXCLUDE ) ); } } class QueryPhrase { const MODE_DEFAULT = 1; const MODE_OR = 2; const MODE_AND = 3; const MODE_EXCLUDE = 4; protected $phrase; protected $mode; public function __construct( $input, $mode=self::MODE_DEFAULT ) { $this->phrase = $input; $this->mode = $mode; } public function getMode() { return $this->mode; } public function __toString() { return $this->phrase; } } class TsqueryBuilder { protected $expression; protected $query; public function __construct( QueryExpression $expression ) { $this->query = trim( $this->processExpression( $expression ), ' &|' ); } public function getResult() { return $this->query; } protected function processExpression( QueryExpression $expression ) { $query = ''; $phrases = $expression->getPhrases(); $subExpressions = $expression->getSubExpressions(); foreach ( $phrases as $phrase ) { $format = "'%s' "; switch ( $phrase->getMode() ) { case QueryPhrase::MODE_AND : $format = "& '%s' "; break; case QueryPhrase::MODE_OR : $format = "| '%s' "; break; case QueryPhrase::MODE_EXCLUDE : $format = "& !'%s' "; break; } $query .= sprintf( $format, str_replace( "'", "\\'", $phrase ) ); } foreach ( $subExpressions as $subExpression ) { $query .= "& (" . $this->processExpression( $subExpression ) . ")"; } return $query; } } $parser = new GoogleQueryParser( new GoogleQueryLexer() ); $queryBuilder = new TsqueryBuilder( $parser->parse( '("used cars" OR "new cars") -ford -mistubishi' ) ); echo $queryBuilder->getResult();