How I can make select from database WHRE in Array

Question

I need to make a database query that includes several WHERE conditions.

The problem is that I cannot correctly pass the $messages array into the request.

public function Controller(Request $request)
    {
        $firstDate = $request->input('start');
        $secondDate = $request->input('end');
        $messages = $request->input('messages');
        $data = $this->client->executeQuery( "SELECT sum('count') as 'var', 'firstname', 'surname' FROM 'table' WHERE 'surname' IN (?) AND 'time' BETWEEN ? AND ? group by 'surname' ORDER BY 'var' desc limit 10",
            [$messages, $startDate, $endDate]);
        $data = $data ->getData();

        return response()->json($data);
    }

But as a result, I get a mistake:

message: "Bind value type ARRAY for parameter 1 is not supported"

I use tarantool database. Any ideas?

Answer 1

First of all, your syntax is incorrect, it should be

executeQuery('...', $messages, $startDate, $endDate);

instead of

executeQuery('...', [$messages, $startDate, $endDate]);

(please note the redundant [] in your version).

Second, If I understand your question correctly, you need to unfold your $messages array and pass each value as a separate argument to executeQuery(). In addition, you need to dynamically construct your SQL statement with as many placeholders inside IN() as the $message array size. Something like this:

$placeholders = rtrim(str_repeat('?,', count($messages)), ',');
$params = $messages;
$params[] = $startDate;
$params[] = $endDate;

$sql = "... IN ($placeholders) AND 'time' BETWEEN ? AND ? ...";
$result = $this->client->executeQuery($sql, ...$params);