search

nette / database

💾 A database layer with a familiar PDO-like API but much more powerful. Building queries, advanced joins, drivers for MySQL, PostgreSQL, SQLite, MS SQL Server and Oracle.
https://doc.nette.org/database
Other
492 stars 105 forks source link

MySqlDriver driver knows about subqueries now. #265

Closed smuuf closed 3 years ago

smuuf commented 3 years ago

As MySQL/MariaDB clearly does support subqueries.

This enables proper building of queries like...

$table('table_a')
  ->where(
    'table_a.column_one IN',
    $table('table_b')->select('value')
  )

... without executing the inner query first.

See https://dev.mysql.com/doc/refman/8.0/en/subqueries.html and https://mariadb.com/kb/en/subqueries/

dg commented 3 years ago

Thanks

lulco commented 3 years ago

Hi @smuuf and @dg

We just found problematic case here with subqueries:

$table('table_a')
  ->where(
    'table_a.column_one IN',
    $table('table_b')->select('value')->limit(100)
  )

Adding limit to subquery throws error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Mysql doc https://dev.mysql.com/doc/refman/8.0/en/subquery-restrictions.html reads:

MySQL does not support LIMIT in subqueries for certain subquery operators
dg commented 3 years ago

I temporarily reverted it until we found a solution.