Too much long alias for joined table

[h4kuna]

• bug report? yes
• version: 2.4 dev-master#d56890f959f3cff135a4bbef36e8cfa380a3cb9c

Description

Hi,

if i create relation over relation table, then condition prefix is bad generated. Same problem is in ORDER BY.

I know response.id where is 1:n relation to response_files and here is only 1:1 relation to table files.

Output sql is right but, in condition WHERE table files is bad.

SELECT `files`.* 
FROM `files` 
LEFT JOIN `response_files` ON `files`.`id` = `response_files`.`files_id` 
WHERE (`files`.`response_files`.`responses_id` IN (16)) -- files is bad

sql schema

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE `files` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) COLLATE utf8_czech_ci NOT NULL COMMENT 'originální název',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `files` (`id`, `filename`) VALUES
(1, 'foo'),
(2, 'bar'),
(3, 'zog');

CREATE TABLE `responses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `insert_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `responses` (`id`) VALUES
(16),
(17);

CREATE TABLE `response_files` (
  `responses_id` bigint(20) unsigned NOT NULL,
  `files_id` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `file_id_response_id` (`files_id`,`responses_id`),
  KEY `responses_id` (`responses_id`),
  CONSTRAINT `response_files_ibfk_2` FOREIGN KEY (`files_id`) REFERENCES `files` (`id`),
  CONSTRAINT `response_files_ibfk_4` FOREIGN KEY (`responses_id`) REFERENCES `responses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `response_files` (`responses_id`, `files_id`) VALUES
(16,    1),
(16,    2),
(17,    3);

prepare environment

git clone git@github.com:nette/database.git nette-database
cd nette-database
composer install
mkdir temp
touch run.php

php script run.php

<?php
require __DIR__ . '/vendor/autoload.php';

$config = [
    'dsn' => 'mysql:host=127.0.0.1;dbname=nette_test',
    'user' => 'root',
    'password' => ''
];

$connection = new Nette\Database\Connection($config['dsn'], $config['user'], $config['password']);
$storage = new Nette\Caching\Storages\FileStorage(__DIR__ . '/temp');
$structure = new \Nette\Database\Structure($connection, $storage);
$context = new Nette\Database\Context($connection, $structure);

$filesA = $context->table('responses')->where('id', 16)->fetch()->related('files', ':response_files.responses_id');

foreach ($filesA as $file) {
    dump($file);
}

$filesB = $context->table('responses')->where('id', 17)->fetch()->related('files', ':response_files.responses_id');
foreach ($filesB as $file) {
    dump($file);
}

[h4kuna]

I try search where can be a problem and found this regular in method SqlBuilder::getColumnChainsRegxp match bad chain, link is online view. Because it is called in preg_replace_callback where must replace all chain.

[h4kuna]

I only find where can be bug, because i don't undestand of context or if is good way for fix. I try manuly replace files. a get new exception. Fatal error: Uncaught exception 'Nette\MemberAccessException' with message 'Cannot read an undeclared column ':response_files.responses_id'.' in /usr/share/nginx/html/nette-database/src/Database/Table/ActiveRow.php on line 299 Yes :response_files.responses_id doesn't exist, but why it is need? I try prepare PR for all the time, but now i haven't solution. :)

[h4kuna]

I try new way how to fix. Update ActiveRow

# This patch file was generated by NetBeans IDE
# It uses platform neutral UTF-8 encoding and \n newlines.
--- a/src/Database/Table/ActiveRow.php
+++ b/src/Database/Table/ActiveRow.php
@@ -150,6 +150,10 @@
     */
    public function related($key, $throughColumn = NULL)
    {
+       if ($throughColumn && substr($throughColumn, 0, 1) === ':') {
+           return $this->getTable()->createSelectionInstance($key)
+                   ->where($throughColumn, $this->getPrimary());
+       }
        $groupedSelection = $this->table->getReferencingTable($key, $throughColumn, $this[$this->table->getPrimary()]);
        if (!$groupedSelection) {
            throw new Nette\MemberAccessException("No reference found for \${$this->table->getName()}->related($key).");

Now can return Selection and it works perfect.

$responses = $context->table('responses')->where('id', 16)->fetch();
$files = $responses->related('files', ':response_files.responses_id');

foreach ($files as $file) {
    dump($file);
}

Is it possible?

It is similar as

$responses = $context->table('responses')->where('id', 16)->fetch();

$files = $responses->getTable()->createSelectionInstance('files')->where(':response_files.responses_id', $responses->id);
foreach ($files as $file) {
    dump($file);
}

But method ActiveRow::getTable() has annotation internal.

[hrach]

Is it possible?

Probably not, because calling createSelection() will destry contant-number-of-queries feature.