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?

score 1 · Accepted Answer · answered Mar 17 '20 at 23:51

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);

score 0 · Answer 2 · answered Mar 16 '20 at 14:02

0

Arrays are unsupported in SQL now, so you can't bind values of array type (exactly what error message says). You can track progress of supporting this feature by subscribing to corresponding issue: https://github.com/tarantool/tarantool/issues/4762

answered Mar 16 '20 at 14:02

NikitaRock

353
2
9

Thank you! I'll keep an eye on it! – Станислав Кравчук Mar 16 '20 at 14:39

score 0 · Answer 3 · answered Mar 16 '20 at 14:09

0

try this

$messages = $request->input('messages');
$messages = implode(',',$messages);

then your code

answered Mar 16 '20 at 14:09

Rudra

704
8
16

Before writing here, I tried a lot of things. Including this one. The result is the same. Thank you! – Станислав Кравчук Mar 16 '20 at 14:38

How I can make select from database WHRE in Array

3 Answers3