How to configure model relationships in case of not conventional naming db? Sorry I know this is not a code issue ...

[etcware]

I'm not able to 'guess' model configuration params: I have an existing database that I cannot change and I need to define a many-to-many relation between ptv_section and ptv_navigation tables described in the following models. The many-to-many relation table is:

CREATE TABLE `ptv_navigation_sections` (
  `nav_sec_id` int(10) NOT NULL AUTO_INCREMENT,
  `navigation_fk` int(10) NOT NULL DEFAULT 0,
  `section_fk` int(10) NOT NULL DEFAULT 0,
   PRIMARY KEY (`nav_sec_id`),
  UNIQUE KEY `my_unique_index_pns` (`navigation_fk`,`section_fk`),
  KEY `navigation_fk` (`navigation_fk`) USING BTREE,
  KEY `section_fk` (`section_fk`) USING BTREE,
  CONSTRAINT `navigation_fk1` FOREIGN KEY (`navigation_fk`) REFERENCES `ptv_navigation` (`navigation_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `section_fk2` FOREIGN KEY (`section_fk`) REFERENCES `ptv_sections` (`section_id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2653 DEFAULT CHARSET=utf8

SectionModel

...
        protected $table = 'ptv_sections';
    protected $primaryKey = 'section_id';
    public $hasMany = [
        'nav' => [
            'class' => NavigationModel::class,
            'joinTable' => 'ptv_navigation_sections',
            'otherField' => 'sec',
            'joinSelfAs' => 'section_fk',
            'joinOtherAs' => 'navigation_fk'
        ],
    ];
    public function getTableName() {
        return $this->table;
    }
...

NavigationModel:

        protected $table = 'ptv_navigation';
    protected $primaryKey = 'navigation_id';

    public $hasMany = [
        'sec' => [
            'class' => SectionModel::class,
            'joinTable' => 'ptv_navigation_sections',
            'otherField' => 'nav',
            'joinSelfAs' => 'navigation_fk',
            'joinOtherAs' => 'section_fk'
        ],
    ];
    public function getTableName() {
        return $this->table;
    }

[Martin-4Spaces]

It seems correct to me. Do you encounter any errors? And if so, how do I reproduce?

On 19 Jan 2022, at 11.06, Alex @.***> wrote:

I'm not able to 'guess' model configuration params: I have an existing database that I cannot change and I need to define a many-to-many relation between ptv_section and ptv_navigation tables described in the following models. The many-to-many relation table is:

CREATE TABLE ptv_navigation_sections ( nav_sec_id int(10) NOT NULL AUTO_INCREMENT, navigation_fk int(10) NOT NULL DEFAULT 0, section_fk int(10) NOT NULL DEFAULT 0, PRIMARY KEY (nav_sec_id), UNIQUE KEY my_unique_index_pns (navigation_fk,section_fk), KEY navigation_fk (navigation_fk) USING BTREE, KEY section_fk (section_fk) USING BTREE, CONSTRAINT navigation_fk1 FOREIGN KEY (navigation_fk) REFERENCES ptv_navigation (navigation_id) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT section_fk2 FOREIGN KEY (section_fk) REFERENCES ptv_sections (section_id) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=2653 DEFAULT CHARSET=utf8 SectionModel

... protected $table = 'ptv_sections'; protected $primaryKey = 'section_id'; public $hasMany = [ 'nav' => [ 'class' => NavigationModel::class, 'joinTable' => 'ptv_navigation_sections', 'otherField' => 'sec', 'joinSelfAs' => 'section_fk', 'joinOtherAs' => 'navigation_fk' ], ]; public function getTableName() { return $this->table; } ... NavigationModel:

    protected $table = 'ptv_navigation';

protected $primaryKey = 'navigation_id';

public $hasMany = [ 'sec' => [ 'class' => SectionModel::class, 'joinTable' => 'ptv_navigation_sections', 'otherField' => 'nav', 'joinSelfAs' => 'navigation_fk', 'joinOtherAs' => 'section_fk' ], ]; public function getTableName() { return $this->table; } — Reply to this email directly, view it on GitHub https://github.com/4spacesdk/CI4OrmExtension/issues/21, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACK5H5BFOBVUI47525ACXL3UW2ELRANCNFSM5MJPNQQA. Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub. You are receiving this because you are subscribed to this thread.

[etcware]

When I try to get sections related to navigations using a valued entity with the following statement:

foreach ($page_items as $navigation) {
            try {
               $navigation->secs = $navigation->secs->_getModel()->find();
            } catch (\Exception $e) {
                d($navigation->secs->_getModel()->getLastQuery()->getQuery());
                dd($e);
            }
        }

the resulting query (in the catch statement raised with the mysqli_sql_exception) is:

SELECT `ptv_sections`.*
FROM `ptv_sections`
WHERE `navs_ptv_navigation`.`navigation_id` = 184

PS mysqli_sql_exception because navs_ptv_navigation doesn't exist: the relationship table is ptv_navigation_sections.

[etcware]

PPS I have the same relationship type with users and groups (in this case I followed the naming convention and the relationship table is groups_users) and the resulting query of the corresponding code is in the following:

foreach ($page_users as $user) {
            $user->groups = $user->groups->_getModel()->find();
            dd($user->groups->_getModel()->getLastQuery()->getQuery());
        }

SELECT `groups`.*
FROM `groups`
LEFT OUTER JOIN `groups_users` `groups_users` ON `groups`.`id` = `groups_users`.`group_id`
LEFT OUTER JOIN `users` `users` ON `users`.`id` = `groups_users`.`user_id`
WHERE `users`.`id` = 1

[Martin-4Spaces]

This is a tricky one. You did everything correct. The issue is that OrmExtensiion has some assumptions about naming when doing join tables like this. I fixed that. Please try updating to latest dev-master version. And let me know if it works. https://github.com/4spacesdk/CI4OrmExtension/commit/190857475663e9744aca61a6f3664baa70cb0792

[Martin-4Spaces]

You can find a working example of your scenario here https://github.com/4spacesdk/CI4-RestOrmExtensionSample/commit/418bfb9820d45353a3a582b7d6f0fb84515591be.

[etcware]

This example is working, my code no :( anyway I close this and I look for my error. Thanks

[etcware]

It works!