QueryBuilder and select

[Joozty]

Is it possible to use QueryBuilder for select function?

Thanks

[mochalygin]

Yep. Do you have any problems with it?

[argayash]

You can use \Doctrine\DBAL\Query\QueryBuilder, but not \Doctrine\ORM\QueryBuilder

We couldn't find a way to override class \Doctrine\ORM\QueryBuilder

[Joozty]

@mochalygin , @argayash Thanks guys

I have a problem with simple query:

$conn = $this->get('doctrine.dbal.clickhouse_connection');
$qb = $conn->createQueryBuilder()
           ->from("sample_table")
           ->select("sample_table.sample_column")
           ->groupBy("sample_table.sample_column");

$s = $conn->prepare($qb->getSql());
$s->execute();

This is my Stack Traces:

Doctrine\DBAL\DBALException:
An exception occurred while executing 'SELECT sample_table.sample_colunm FROM sample_table GROUP BY sample_table.sample_column':

Can`t find meta

  at vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:131
  at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(QueryException), 'SELECT sample_table.sample_column FROM sample_table GROUP BY sample_table.sample_column', array())
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php:177)
  at Doctrine\DBAL\Statement->execute()
     (src/AnalyticsDashboardBundle/Controller/ApiController.php:166)
  at AnalyticsDashboardBundle\Controller\ApiController->getUniqueInstitutions(object(Request), object(Logger))
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:151)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:68)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php:202)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (web/app_dev.php:29)
  at require('/data/web/analytics-dashboard/web/app_dev.php')
     (vendor/symfony/symfony/src/Symfony/Bundle/WebServerBundle/Resources/router.php:42)

When I run this query via command line there is no problem and it returns my all requested data.

[mochalygin]

@Joozty Can you provide table structure also?

[Joozty]

@mochalygin I simplified the query (changed the names) but this is how I create a table:

CREATE TABLE sample_table (
     sample_column FixedString(15),
     column_one String,
     column_two Int16,
     column_three Float32
) ENGINE = TinyLog

[mochalygin]

@argayash

This is the reason. Need to fix or limit smi2 version.

https://github.com/smi2/phpClickHouse/commit/4b0305b8d78f466d96af52df0e02acea19253861

Would you?))

[argayash]

@mochalygin yes, sure

[mochalygin]

@isublimity @argayash It seems what recent 0.18.05.09 smi2/phpclickhouse works unproper if query ends with ' FORMAT JSON'

[isublimity]

i`m check today, and try fix

[isublimity]

In https://github.com/FriendsOfDoctrine/dbal-clickhouse/blob/master/src/ClickHouseStatement.php#L303

IF (strtoupper(substr($sql, 0, 6)) === 'SELECT') {
   // ....
  // ....
}

phpClickHouse : only $client->select() auto add format json

I think true way use

IF (strtoupper(substr(trim($sql), 0, 6)) === 'SELECT') {
     $this->rows = $this->smi2CHClient->select($sql)->rows();
} else {
  $result=$this->smi2CHClient->write($sql);
}

[argayash]

@Joozty try new release 1.2.0

[Joozty]

@argayash @mochalygin Thanks, guys. It works. I had a problem with memory but when I increased memory limit in my php.ini it worked fine.

(1/1) OutOfMemoryException
Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4194312 bytes)

in Statement.php line 233
at Statement->init()
in Statement.php line 454
at Statement->rows()
in ClickHouseStatement.php line 316
at ClickHouseStatement->processViaSMI2()
in ClickHouseStatement.php line 281
at ClickHouseStatement->execute()
in Statement.php line 169
at Statement->execute()
in ApiController.php line 168
at ApiController->getUniqueInstitutions()
in HttpKernel.php line 151
at HttpKernel->handleRaw()
in HttpKernel.php line 68
at HttpKernel->handle()
in Kernel.php line 202
at AppKernel->handle()
in app_dev.php line 29
in router.php line 42
at {main}()
in router.php