codeigniter4-schemas/src/Handlers/DatabaseHandler.php

[crustamet]

// Create a relation $relation = new Relation(); $relation->type = 'belongsTo'; $relation->singleton = true; $relation->table = $this->stripPrefix($foreignKey->foreign_table_name);

Why type is always belongsTo ?

When i try to connect a many to one it triggers a relation exception that the table[0] is not related to table[1]

but in table[1] has a foreign_key with table[0]

I think code is missing for this case. It should know from relation schema that a foreign relation exists.

Tables : connectors id_connector id_menu

menu id_menu title

$menu = new menuModel() $menu->with('connectors')->findAll();

im getting this ERROR Table {0} is not known to be related to {1}

[MGatner]

It looks like you are specifying belongsTo in a schema file - is that was your code is up top?

$relation->type = 'belongsTo';

Check the schema that is actually being detected by running spark schemas database model file and reviewing the output, particularly the relation from menu to connectors. (You can also post the output here.)

If you need to specify a relationship that isn't being detected properly just create a file in app/Schemas/, see an example one in the tests support: tests/_support/Schemas/Good/Products.php

[crustamet]

I get it. but why this doesn`t work automatically and letting us to decide what relation type is when getting items from model. All relations are inside the schema it can search easily for connectors to see that it has an relation to menu. Do you guys decided that this will not be implemented ?

[MGatner]

Let's start over - I think I misunderstood your issue. It looks like the problem might be that foreign key relationships are only being created in one direction - that is, it creates the belongsTo relationship from table1 to table2, but not the corresponding hasMany (sometimes called manyToOne) relationship from table2 to table1.

Did I get that right? Looking at the code it seems that is indeed the case: https://github.com/tattersoftware/codeigniter4-schemas/blob/65048b2720b52804b7bd30ecbd34d9772dcc558f/src/Handlers/DatabaseHandler.php#L128

[crustamet]

Yes this is the problem, with this fix, no one needs to code anymore the basic logics for any project. You just create the database and in the code you specify what you need so you get it from the schema.

Yes the schema generated only makes belongsTo relationships and does not create any other relations like hasMany.. manyToOne etc..

[MGatner]

Got it! Sorry to have misunderstood the first time. I will deploy a fix shortly.

[crustamet]

I don`t know how you will do this, because you have to search all relations and generate possible relation schemes only for hasMany.. i will check your code after you deploy. I am very interested in this Function.

I made a project but with old php code not with the modern stuff. And i saw all cases that i have to solve.

And i only solved 1-n, 1-1, cases. But for n-n cases this was something else..

If this will gonna be implemented here this will gonna be amazing men :) Thanks.. i can help you if i can with whatever you want for this to work.

[MGatner]

Thanks for the offer! The auto-detection is based off two source: foreign keys and naming convention. As I understand it, in your case, the foreign key is detected and it creates the belongsTo relationship to the foreign key target table - however, the target table doesn't get the corresponding relationship coming back.

For all other relationships they will only be detected if the database tables and columns are named correctly. I will be adding docs on the conventions, but basically if you want users and groups to be mantToMany you need a pivot table groups_users with columns named group_id and user_id.

[MGatner]

Solved by b417925904194c91fae41c5290f4051deee4dc01. Please test and report back.

[crustamet]

This change works, but another issue is appearing, the problem i didnt solve on my old algorith is when i change the foreign key from the connectors im getting this error

I changed Table connectors id_connector _idmenu

TO connectors id_connector _idmenus

Rerun the schema cache

mysqli_sql_exception #1054 Unknown column connectors.id_menu

This problem is because you are using the primary key of MENU trying to find the connectors.id_menu

But this is not good like this, it already has the information into the RELATION of the connectors.id_menu from the database.

So when i change the field from id_menu to id_menus the relation still exists. We should not get this errors.

But yes the problem is fixed with what you did already.

If you can just get the relation information of the connectors table you will have there the foreign key of it that is equal to the primary key of the table.

If this works i can test other functionalities, but until this is not working i can`t test other stuff..

[MGatner]

So when i change the field from id_menu to id_menus the relation still exists

It sounds like you are just working with a cached version of the schema. Be sure to overwrite it first by running spark schema database model file -export cache. If you are still having issues please post a copy of your schema - i.e. output of spark schema cache.

[crustamet]

yes i am overwrite the file i am deleting the cache file when i change the column from the database from id_menu to id_menus

[crustamet]

Wait maybe i checked wrong, i will check again and come back to you

[crustamet]

Yeap it is the same problem.

This is because

For all other relationships they will only be detected if the database tables and columns are named correctly. I will be adding docs on the conventions, but basically if you want users and groups to be mantToMany you need a pivot table groups_users with columns named group_id and user_id.

Why only be detected if ? because all the informations are inside the database relation

[crustamet]

I can explain more perfectly if you still don`t know what i am talking about.

Again, Your code worked perfectly the schema knows now the relation but it only knows if they have the same field name for both of the tables.

[crustamet]

This does not work with the following tables

menu id_menu

connectors id_connector id_menus

The relations exists even though you modify name of the column.

[MGatner]

In your above example Schemas would not detect a relationship between menu and connectors unless id_menus is defined as a foreign key to menu.id_menu.

If you have that foreign key in place then it is probably a bug and I would like to see your schema output; if you don't have the foreign key you will need to provide a supplemental schema file to a assist the autodetect (see the new Autodetection section in README).

[crustamet]

i can give it to you but i have a private database :( i just cannot share this here

[crustamet]

i will create another database with only this case i will share it with you tomorow.

[MGatner]

A subset example of the bug would be good. You can also email a copy to support@tattersoftware.com if you feel comfortable.

[crustamet]

Yes you were right, it worked with only this new case, but for my only my case it is still not working.

Im sending yout the schema here it should work.

DB TEST : https://projects.protobyte.app/c4index/nativity_asdfasdfasd.sql SCHEMA GENERATED : https://projects.protobyte.app/c4index/schema

Please use these 2 files to check if they work. for me it doesnt.

[MGatner]

Thank you for sharing those - that will make troubleshooting a lot easier.

I see in your generated schema that frontpanel_connectors belongsTo frontpanel_menus and frontpanel_menus hasMany frontpanel_connectors. I believe this is what you were hoping for - what is still wrong? You should be able to use them like this:

$menus = $menuModel->with('connectors')->findAll();
foreach ($menus[0]->connectors as $connector)
...
$connectors = $connectorModel->with('menus')->findAll();
print_r($connector->menu);

[crustamet]

im getting error

Unknown column connectors.id_menu

while the field is id_menu_modified.

Can you test the database if works ? with your Tatter/Relations ? Maybe i have in the database id_parent that is recursively ?

I dont know what is happening but im getting an error with the files i gave you.

[MGatner]

It looks like you changed the field from id_menu to id_menu_modified but the schema is still detecting the old name. I see in your schema that the foreign key itself is listed incorrectly:

["foreignKeys"]=>
        {
          =>
         ["frontpanel_connectors_ibfk_1"]=>
           {
           =>
            "Tatter\Schemas\Structures\ForeignKey"
            ["constraint_name"]=>
            string(28) "frontpanel_connectors_ibfk_1"
            ["table_name"]=>
            string(21) "frontpanel_connectors"
            ["column_name"]=>
            string(7) "id_menu"
            ["foreign_table_name"]=>
            string(16) "frontpanel_menus"
            ["foreign_column_name"]=>
            string(7) "id_menu"
          }
        }

Note the column_name is "id_menu". This is taken directly from CodeIgniter 4's database command getForeignKeyData(), so it might be a bug in the framework itself.

If you still have this configuration live, could you please run the following and share the output?

$db = db_connect($db);
$keys = $db->getForeignKeyData('frontpanel_connectors');
dd($keys);

[crustamet]

No no, the schema is generated wrong, pls import the sql that i gave you and try to generate schema.

[crustamet]

Yes i will run this configuration.. i will post it here after. I understand what you are saying now, i didn`t read all. Sorry.

[MGatner]

@crustamet I believe this was resolved, but would you please test against version 2.0 and let me know?