Userspice DB.php (with patch by plb) - example 1

/*
Let's find multiple users sharing the same characteristic

On the beginning we could write SQL like this:
SELECT * FROM test_table WHERE (Data=1 AND UserID=1) OR (Data=1 AND UserID=2)

Argument for get() will need to look like this:
[
    "or",
    [
        "and",
        ["Data",   "=", 1],
        ["UserID", "=", 1]
    ],
    [
        "and",
        ["Data",   "=", 1],
        ["UserID", "=", 2]
    ]
]

Here's PHP code to do this query:
*/

$query_array = ["or"];
$user_list   = [["id"=>1], ["id"=>2]];

foreach ($user_list as $user_info)
    $query_array[] = [ "and", ["Data","=",1], ["UserID","=",$user_info["id"]] ];

$result = $db -> get("test_table", $query_array);




/*
Now let's optimize this query:
SELECT * FROM test_table WHERE Data=1 AND (UserID=1 OR UserID=2)

Argument for get() will need to look like this:
[
    "and",
    ["Data", "=", 1],
    [
        "or",
        ["UserID", "=", 1],
        ["UserID", "=", 2]
    ]
]

Here's PHP code to do this query:
*/

$query_array = ["and", ["Data","=",1], ["or"]];
$user_list   = [["id"=>1], ["id"=>2]];

foreach ($user_list as $user_info)
    $query_array[2][] = ["UserID","=",$user_info["id"]];

$result = $db -> get("test_table", $query_array);




/*
With new DB.php you can use IN instead of OR:
SELECT * FROM test_table WHERE Data=1 AND UserID IN (1,2)

Argument for get() will need to look like this:
[
    "and",
    [ "Data",   "=",  1     ],
    [ "UserID", "IN", [1,2] ]
]

Here's PHP code to do this query:
*/

$query_array = ["and", ["Data","=",1], ["UserID","IN",[]]];
$user_list   = [["id"=>1], ["id"=>2]];

foreach ($user_list as $user_info)
    $query_array[2][2][] = $user_info["id"];

$result = $db -> get("test_table", $query_array);



// Alternative version

$user_list = [["id"=>1], ["id"=>2]];
$id_list   = [];

foreach ($user_list as $user_info)
    $id_list[] = $user_info["id"];

$result = $db -> get("test_table", [ "and", ["Data","=",1], ["UserID","IN",$id_list] ]);