Yii2 Parsel

Allows developers to provide a boolean search query interface, similar to Google or Sphinx search or other full-text search (FTS) engines.

Turns a user query like 'georgia -(atlanta or decatur)' into 'georgia AND NOT (atlanta or decatur)' which is then turn into the follow SQL:

SELECT
  "ip", /* ip address */
  "visits", /* how many requests they've made */
  "city",
  "region"
FROM
/* A table similar to apaches access log.  See my extension yii2-ipFilter */
  "visitor"
WHERE
  (
    ("visitor"."ip" ILIKE '%georgia%')
    OR ("visitor"."city" ILIKE '%georgia%')
    OR ("visitor"."region" ILIKE '%georgia%')
  )
  AND ( /** marvel as we efortlessly generate a subquery */
    "ip" NOT IN (
      SELECT
        "ip"
      FROM
        "visitor"
      WHERE
        (
          ("visitor"."ip" ILIKE '%atlanta%')
          OR ("visitor"."city" ILIKE '%atlanta%')
          OR ("visitor"."region" ILIKE '%atlanta%')
        )
        OR (
          ("visitor"."ip" ILIKE '%decatur%')
          OR ("visitor"."city" ILIKE '%decatur%')
          OR ("visitor"."region" ILIKE '%decatur%')
        )
    )
  )

Example results:

Installation

The preferred way to install this extension is through composer.

Either run

php composer.phar require --prefer-dist johnsnook/yii2-parsel "*"

or add

"johnsnook/yii2-parsel": "*"

to the require section of your composer.json file.

Usage

"Look, I didn't know I could speak Parseltongue! What else don't I know about myself? Look. Maybe you can do something, even something horrible and not know you did it."

Once the extension is installed, simply use it in your code by :

$userQuery = 'good AND plenty -licorice';
$parsel = new ParselQuery([
            'userQuery' => $this->userQuery,
            'dbQuery' => Script::find()
        ]);
$parsel->dbQuery->all();

Tokens/behavior:

Fields to be search must be either text, varchar or char currently. Future versions may expand to number, dates and maybe even JSON. All search terms, except where specified bye the full match operator are wrapped in your databases wildcard of choice. Searching for "smart" is equivalent to the SQL expression '%smart%'. Search is case insensitive as long as your database's LIKE operator is. PostgreSQL will use ILIKE.

Conjunctives:

'AND' is the default behavior. "smart pretty" is the same as "smart AND pretty."

'OR' allows more results in your query: "smart OR pretty."

Operators:

Examples

See files in /examples. If it's still up, you might also be able to play with an example here

Additional Reading

PostgreSQL

Faster PostgreSQL Searches with Trigrams

Optimizing databases for fuzzy searching

MySQL

Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search

Acknowledgements

This project was built by heavily modifying the excellent "Search Query Parser" project. I de-abstracted the token structure and modified the parser class to better fit my needs. Their licsence file should be found at the root of this project.

Both projects are made possible by the amazing and lightning quick lexer library by Nikita Popov of Berlin. It's work reading his article on the subject.