sub query / nested query

[FirthTuckman]

Information

• Version of Medoo: [2.2]
• Version of PHP: [8.0]
• Type of Database: [SQLite, etc.]
• System: [Linux]

Describe the Problem

Fetched from select() or get() function:

$database->select("account", "user_name", [
"user_id" => $database->select("post", "user_id", ["comments[>]" => 40])
]);
// WHERE user_id IN (2, 51, 321, 3431)

The result of user_id maybe a very large array, for example 5000+, the pdo will report an exception:
PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1 too many SQL variables in ....Medoo.php:...

Is there a way to make the medoo generates a sql conditon for user_id, for example:
// WHERE user_id in (select user_id from post where ... )

[FirthTuckman]

A good way to resolve this problem.

Just edit the Medoo.php src code here, delete this '=' operator.

Great ! So, we can define the operator in the raw object by ourself.

Here is the usage:

// Generate the sub query sql string
ob_start();
$database->debug()->select("post", "user_id", ["comments[>]" => 40])
$subQuerySql = ob_get_clean();

// Pass the raw object
$database->select("account", "user_name", [
"user_id" => Medoo::raw("IN ( $subQuerySql )");
// "user_id" => Medoo::raw("NOT IN ( $subQuerySql )");
]);

This will generate a sub query :
SELECT "user_name" FROM "account" WHERE "user_id" IN ( SELECT "user_id" FROM "post" WHERE ... )

But be careful the side effect, you need to define the operator always when you pass the raw object.

[FirthTuckman]

BTW, it's not convenient to get the sqlstring...

ob_start();
$database->debug()->select("post", "user_id", ["comments[>]" => 40])
$subQuerySql = ob_get_clean();

Looking forward to adding a default arg for debug method, default true to echo the sqlstring, and false to return the sqlstring, so we can get the sqlstring by:

$subQuerySql = $database->debug(false)->select(......);

Or, adding a new method:

$subQuerySql = $database->sql()->select(......);