Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bad join parsing with multiple keys #170

Closed
EdaCZ opened this issue May 30, 2017 · 0 comments
Closed

Bad join parsing with multiple keys #170

EdaCZ opened this issue May 30, 2017 · 0 comments

Comments

@EdaCZ
Copy link
Contributor

@EdaCZ EdaCZ commented May 30, 2017

  • bug report? yes
  • feature request? no
  • version: 2.4.2, PHP 7.0.19, MySQL 5.7

Description

When there are two foreign keys on table Operator, one Operator.countryId -> Country.id and second Operator.countryId, Operator.regionOrder -> Region (id, order), when you want to order selection of operators by joined column, it throws notice Array to string conversion on /Database/Table/SqlBuilder.php:787.

$this->db->table('Operator')->order('countryId.id')->count();

Caution: It depends on order of foreign keys, in which they are stored in database.

Nette Database should choose simple key for joining, but now it chooses two-column foreign key (depends on order of FKs in DB).

Steps To Reproduce

  • Install nette sandbox
  • install SQL scheme
CREATE TABLE `Country` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `short` varchar(10) COLLATE utf8_czech_ci NOT NULL,
  `title` varchar(50) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `Operator` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) COLLATE utf8_czech_ci NOT NULL,
  `countryId` int(4) unsigned NOT NULL,
  `regionOrder` int(1) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Operator_ibfk_6` (`countryId`,`regionOrder`),
  KEY `regionOrder` (`regionOrder`),
  CONSTRAINT `Operator_ibfk_11` FOREIGN KEY (`countryId`) REFERENCES `Country` (`id`),
  CONSTRAINT `Operator_ibfk_12` FOREIGN KEY (`countryId`, `regionOrder`) REFERENCES `Region` (`id`, `order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `Region` (
  `id` int(4) unsigned NOT NULL,
  `order` int(1) unsigned NOT NULL,
  `title` varchar(50) COLLATE utf8_czech_ci NOT NULL,
  `countryId` int(4) unsigned NOT NULL,
  PRIMARY KEY (`id`,`order`),
  KEY `countryId` (`countryId`),
  KEY `order` (`order`),
  CONSTRAINT `Region_ibfk_2` FOREIGN KEY (`countryId`) REFERENCES `Country` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
  • call code:
$this->db->table('Operator')->order('countryId.id')->count();
  • if it did not throw an error, change order of foreign keys, in which they are stored, on table Operator
@EdaCZ EdaCZ mentioned this issue Jul 20, 2019
dg added a commit that referenced this issue Jul 22, 2019
@dg dg closed this Jul 22, 2019
dg added a commit that referenced this issue Jul 22, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.