has_and_belongs_to_many opponent table primary key is not correctly choose

[EthannSchneider]

Hello,

Here how to reproduce like me

class Task extends ActiveRecord\Model {
    public static array $belongs_to = ["creator" => ["class_name" => "User", "foreign_key" => "created_by"]];
    public static array $has_many = ["working_sessions" => []];
    public static array $has_and_belongs_to_many = [
        "users" => [
            "join_table" => "users_tasks",
        ]
    ];
}

class User extends ActiveRecord\Model {
    public static array $has_many = ["created_tasks" => ["class_name" => "Task", "foreign_key" => "created_by"], "working_sessions" => []];
    public static array $has_and_belongs_to_many = [
        "tasks" => [
            "join_table" => "users_tasks",
        ]
    ];
}

class WorkingSession extends ActiveRecord\Model {
    public static array $belongs_to = ["user" => [], "task" => []];
}

$me = User::find(1);

foreach ($me->tasks as $task) {
    echo $task->title . "\n";
}

The error

PHP Fatal error:  Uncaught ActiveRecord\Exception\DatabaseException: couldn't execute query on ActiveRecord\Adapter\MysqlAdapter. user: ethannschneider: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.user_id' in 'on clause' in /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Connection.php:378
Stack trace:
#0 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Table.php(300): ActiveRecord\Connection->query('SELECT `tasks`....', Array)
#1 [internal function]: ActiveRecord\Table->find_by_sql('SELECT `tasks`....', Array, false, Array)
#2 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Relation.php(979): iterator_to_array(Object(Generator))
#3 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Relation.php(886): ActiveRecord\Relation->_to_a(Array)
#4 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Relationship/HasAndBelongsToMany.php(54): ActiveRecord\Relation->to_a()
#5 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Model.php(696): ActiveRecord\Relationship\HasAndBelongsToMany->load(Object(User))
#6 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Model.php(655): ActiveRecord\Model->initRelationships('tasks')
#7 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Model.php(448): ActiveRecord\Model->read_attribute('tasks')
#8 /private/tmp/test-active-record-php/main.php(17): ActiveRecord\Model->__get('tasks')
#9 {main}
  thrown in /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Connection.php on line 378

Fatal error: Uncaught ActiveRecord\Exception\DatabaseException: couldn't execute query on ActiveRecord\Adapter\MysqlAdapter. user: ethannschneider: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.user_id' in 'on clause' in /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Connection.php:378
Stack trace:
#0 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Table.php(300): ActiveRecord\Connection->query('SELECT `tasks`....', Array)
#1 [internal function]: ActiveRecord\Table->find_by_sql('SELECT `tasks`....', Array, false, Array)
#2 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Relation.php(979): iterator_to_array(Object(Generator))
#3 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Relation.php(886): ActiveRecord\Relation->_to_a(Array)
#4 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Relationship/HasAndBelongsToMany.php(54): ActiveRecord\Relation->to_a()
#5 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Model.php(696): ActiveRecord\Relationship\HasAndBelongsToMany->load(Object(User))
#6 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Model.php(655): ActiveRecord\Model->initRelationships('tasks')
#7 /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Model.php(448): ActiveRecord\Model->read_attribute('tasks')
#8 /private/tmp/test-active-record-php/main.php(17): ActiveRecord\Model->__get('tasks')
#9 {main}
  thrown in /private/tmp/test-active-record-php/vendor/php-patterns/activerecord/lib/Connection.php on line 378

I've printed the generated sql and i noticed that

SELECT `tasks`.* FROM tasks INNER JOIN users_tasks ON (tasks.id = users_tasks.id) INNER JOIN users ON users.user_id = users_tasks.user_id WHERE users. id = ?

users.user_id = users_tasks.user_id

The users.user_id should be users.id

i've looked into the HasAndBelongsToMany.php and found that

public function construct_inner_join_sql(Table $from_table, bool $using_through = false, ?string $alias = null): string
{
    $other_table = Table::load($this->class_name);
    $associated_table_name = $other_table->table;
    $from_table_name = $from_table->table;
    $foreign_key = $this->options['foreign_key'];
    $join_primary_key = $this->options['association_foreign_key'];
    $linkingTableName = $this->options['join_table'];
    $res = 'INNER JOIN ' . $linkingTableName . " ON ($from_table_name.$foreign_key = " . $linkingTableName . ".$foreign_key) "
        . 'INNER JOIN ' . $associated_table_name . ' ON ' . $associated_table_name . '.' . $join_primary_key . ' = ' . $linkingTableName . '.' . $join_primary_key;

    return $res;
}

join_primary_key is duplicated there should be a $associated_table_primary_key variable

[EthannSchneider]

sorry duplicate of #119

[shmax]

Hey, thanks for reporting! Yes, there is an existing issue, but I think the original reporter sort of ran out of steam when it came to updating tests and stuff like that. PRs welcome if you want to pick up where he left off:

#120