The logical expressions does not work as expected while full text search in SQLite database. Tested on XAMPP-PORTABLE 1.8.3 [PHP: 5.5.6].
Example data
There is table clubs with columns
name for the football clubcity for the citynations for the abbreviations of players' nationalities
What is important, the cities with be used for OR statements (example, London or Liverpool). The nationalities will be used for AND statements.
The PHP script that creates such table
<?php
$db = new PDO('sqlite:mydb.sqlite3');
$sql = "CREATE VIRTUAL TABLE clubs USING fts4 " .
"(name, city, nations)";
$db->exec($sql);
$sql = "DELETE FROM clubs";
$db->exec($sql);
$teams = array(
array(
"name" => "FC Chelsea",
"city" => "London",
"nations" => "ENG BEL BIH FRA SEN ESP GHA SRB NIG COL"
),
array(
"name" => "FC Arsenal",
"city" => "London",
"nations" => "ENG COL CZE FRA GER BRA ESP WAL CHI CRC"
),
array(
"name" => "Tottenham Hotspur",
"city" => "London",
"nations" => "ENG FRA NED BEL ARG AUT WAL ALG DEN CAM KOR"
),
array(
"name" => "West Ham United",
"city" => "London",
"nations" => "ENG ESP IRL SUI NZL WAL ITA CAN SCO CAM SEN FRA ARG ECU"
),
array(
"name" => "Manchester City",
"city" => "Manchester",
"nations" => "ENG ARG BEL FRA SRB BRA CIV ESP NIG"
),
array(
"name" => "Manchester United",
"city" => "Manchester",
"nations" => "ENG ESP ARG NED ITA ECU FRA BEL GER BRA"
),
array(
"name" => "FC Everton",
"city" => "Liverpool",
"nations" => "ENG ESP USA ARG CRC IRL BIH RSA BEL SCO URU"
),
array(
"name" => "FC Liverpool",
"city" => "Liverpool",
"nations" => "ENG BRA HUN FRA CRO ESP SVK CIV GER WAL POR BEL"
),
);
for($i=0; $i<count($teams); $i++) {
$sql = "INSERT INTO clubs VALUES (?, ?, ?)";
$stmt = $db->prepare($sql);
$stmt->execute(
array(
$teams[$i]["name"], $teams[$i]["city"], $teams[$i]["nations"]
)
);
}
Examples of logical expressions
The following function wil be used for the display of the results
function doQuery($db, $sql, $desc) {
$arr = array();
$q = $db->query($sql);
$rows = $q->fetchAll(PDO::FETCH_ASSOC);
echo "<tt>" . $sql . "</tt><br/>";
echo "<b> " . $desc . " (" . count($rows) . "</b>): " ;
foreach($rows as $row) {
$arr[] = $row['name'];
}
$results = implode(", ", $arr);
echo $results . "<br/>";
}
In the following examples the results will be displayed with italic to distinguish them from the other text.
One logical expression statement
1.1. Search clubs with Spanish players
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ESP')
SQL;
doQuery($db, $sql, "ESP");
There are found all teams except Tottenham
ESP (7): FC Chelsea, FC Arsenal, West Ham United, Manchester City, Manchester United, FC Everton, FC Liverpool
1.2. Search clubs with Argentine players
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG')
SQL;
doQuery($db, $sql, "ARG");
Results are also correct.
ARG (5): Tottenham Hotspur, West Ham United, Manchester City, Manchester United, FC Everton
Two logical expressions statement
2.1. Search clubs from London OR Liverpool
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:London OR city:Liverpool')
SQL;
doQuery($db, $sql, "London or Liverpool");
No problem.
London or Liverpool (6): FC Chelsea, FC Arsenal, Tottenham Hotspur, West Ham United, FC Everton, FC Liverpool
2.2. Search clubs with Spanish players AND Argentine players
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG nations:ESP')
SQL;
doQuery($db, $sql, "ARG ESP");
Implicit AND operator works fine.
ARG ESP (4): West Ham United, Manchester City, Manchester United, FC Everton
but when go for explicit AND
2.3. Search clubs with Spanish players AND Argentine players (explicit)
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG AND nations:ESP')
SQL;
doQuery($db, $sql, "ARG and ESP");
No club is found.
ARG and ESP (0):
The explicit AND does not seem to work.
Three logical expressions statement
Now the results are unexpected
3.1. Search clubs from London OR clubs from Liverpool with Argentine players
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:London OR city:Liverpool nations:ARG')
SQL;
doQuery($db, $sql, "London or Liverpool and ARG");
We would have expected all London teams selected, but instead we get only those with Argentine players
London or Liverpool and ARG (3): Tottenham Hotspur, West Ham United, FC Everton
The same result is obtained for query
SELECT * FROM clubs WHERE clubs MATCH ('city:Liverpool OR city:London nations:ARG')
It looks like OR is executed before AND: (city:Liverpool OR city:London) AND nations:ARG. But what happens when the brackets are really used around OR-statement.
3.2. Search clubs (from London OR clubs from Liverpool) with Argentine players (brackets)
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('(city:Liverpool OR city:London) nations:ARG')
SQL;
doQuery($db, $sql, "(London or Liverpool) and ARG");
No club is found.
(London or Liverpool) and ARG (0):
What if the sequence of logical expressions changes
3.3 Search clubs from Liverpool with Argentine players or clubs from London.
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:Liverpool nations:ARG OR city:London')
SQL;
doQuery($db, $sql, "Liverpool and ARG or London");
Only team from Liverpool with Argentine players is found.
Liverpool and ARG or London (1): FC Everton
3.4. Same search with switched nations:ARG and city:Liverpool.
$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG city:Liverpool OR city:London')
SQL;
doQuery($db, $sql, "ARG and Liverpool or London");
The same result as in the 3.1. example.
ARG and Liverpool or London (3): Tottenham Hotspur, West Ham United, FC Everton
Question
I got no clue how logical expressions are interpreted. Can anybody explain?