Error while trying to JOIN another DB with different column names

[j54j6]

Used Version: 0.1.0 Python Version: 3.10.7

Hey,

today I tried to join a Table based on 2 unequal names column names (Table blueprint ahead).

My SQL Statement is the following:

 SELECT D.device_name, D.lastSeen, D.error_count_since_last_reboot, D.device_firmware_version, D.device_firmware_version_installed, C.icon from devices AS D JOIN deviceTypes AS C ON C.typeID = D.device_firmware_type;

My code is the following:

db.select('devices(D)', 
 'D.device_name, D.lastSeen, D.error_count_since_last_reboot, D.device_firmware_version, D.device_firmware_version_installed, C.icon', 
  join = {'deviceTypes(C)[>]': {'typeID', 'device_firmware_type'}}
)

The table schemes are the following:

Devices:

devices | CREATE TABLE `devices` (
  `device_id` int(11) NOT NULL AUTO_INCREMENT,
  `mac_address` varchar(32) NOT NULL,
  `device_name` varchar(32) NOT NULL,
  `device_update_token` varchar(64) DEFAULT NULL,
  `device_update_password` varchar(128) DEFAULT NULL,
  `used_chip` varchar(32) NOT NULL,
  `device_hostname` varchar(32) NOT NULL DEFAULT '''not_set''',
  `device_firmware_type` int(32) NOT NULL,
  `device_firmware_version` int(12) NOT NULL DEFAULT 0,
  `device_firmware_version_installed` int(12) NOT NULL DEFAULT 0,
  `device_fs_version` varchar(12) NOT NULL DEFAULT '0.1',
  `device_fs_version_installed` varchar(12) NOT NULL DEFAULT '0.1',
  `device_version` varchar(12) NOT NULL DEFAULT '1.0',
  `last_updated` int(11) NOT NULL DEFAULT 0,
  `last_update_check` int(11) NOT NULL DEFAULT 0,
  `device_state` tinyint(4) NOT NULL DEFAULT 0,
  `device_is_locked` tinyint(4) NOT NULL DEFAULT 0,
  `device_update_modify_locked` tinyint(4) NOT NULL DEFAULT 0,
  `device_to_destroy` tinyint(4) NOT NULL DEFAULT 0,
  `device_is_destroyed` tinyint(4) NOT NULL DEFAULT 0,
  `force_update` tinyint(4) NOT NULL DEFAULT 0,
  `ip_address` varchar(32) DEFAULT '0.0.0.0',
  `ip_netmask` varchar(32) DEFAULT '0.0.0.0',
  `ip_gateway` varchar(32) DEFAULT '0.0.0.0',
  `dns_server` varchar(32) DEFAULT '0.0.0.0',
  `ssid` varchar(64) DEFAULT 'not set',
  `uptime` int(11) DEFAULT 0,
  `lastSeen` datetime DEFAULT NULL,
  `error_count_since_last_reboot` int(11) NOT NULL DEFAULT 0,
  `last_error_message_captured` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`device_id`),
  KEY `device_firmware_type` (`device_firmware_type`),
  KEY `device_firmware_version` (`device_firmware_version`),
  KEY `device_firmware_version_installed` (`device_firmware_version_installed`),
  CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`device_firmware_type`) REFERENCES `deviceTypes` (`typeID`),
  CONSTRAINT `devices_ibfk_2` FOREIGN KEY (`device_firmware_version`) REFERENCES `software` (`releaseID`),
  CONSTRAINT `devices_ibfk_3` FOREIGN KEY (`device_firmware_version_installed`) REFERENCES `software` (`releaseID`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 | 

and the deviceTypes table:

deviceTypes | CREATE TABLE `deviceTypes` (
  `typeID` int(11) NOT NULL AUTO_INCREMENT,
  `typename` varchar(32) NOT NULL,
  `usedChip` varchar(25) NOT NULL DEFAULT 'esp8266',
  `typeDescription` varchar(512) NOT NULL,
  `path` varchar(100) NOT NULL DEFAULT 'general',
  `disabled` tinyint(4) DEFAULT 0,
  `icon` varchar(50) NOT NULL DEFAULT 'bi-cpu',
  PRIMARY KEY (`typeID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 | ```

I'm not exactly sure if this is a Bug or if I'm to stupid to read the doc. correctly :^) - but imho I think I need to use this:
>>Documentation on PyPi.org

Join on different columns: JOIN "Customers" AS "C" ON "C"."CustomerID"="O"."OtherID" join = { 'Customers(C)[><]': {'CustomerID', 'OtherID'} }

The error I get from python (Flask) is: 
```        def __init__( [Open an interactive python shell in this frame] 

            self, key, val, maintable=None

        ):  # pylint: disable=too-many-branches

            matching = re.match(JoinTerm.REGEX_KEY, key)

            if not matching:

>>>>     raise JoinParseError("Unrecognized table in JOIN.")    <<<<

            self.jointype = matching.group(1)

            self.table = TableFrom(matching.group(2), alias=matching.group(3))

            self.onfields = []

            fieldtable = self.table.alias or self.table.table

            if isinstance(maintable, TableFrom):

    medoo.exception.JoinParseError: Unrecognized table in JOIN. ```

Hope you can help me ^^

[pwwang]

No, it's the documentation.

It should be:

   join = {'[><]deviceTypes(C)': {'typeID': 'device_firmware_type'}}
            ^^^^                          ^

[j54j6]

Thank you, this works fine :)