[BUG]: Incorrect table prefixes in WordPress Multisite environment

[rafaucau]

Describe the bug

The ORM doesn't properly handle table prefixes when used with WordPress Multisite. The issue originates in the Database connection class which initializes the table prefix only once, causing all subsequent database operations to use incorrect prefixes when switching between blogs.

Steps to reproduce the issue

1. Set up WordPress Multisite with at least 2 sites
2. Create a test code:

use Dbout\WpOrm\Models\Post;

add_action( 'init', function () {
	$sites = get_sites();
	foreach ( $sites as $site ) {
		switch_to_blog( $site->blog_id );
		var_dump( ( new Post() )->getTable() );
	}
});

3. Observe that table names don't change with blog switching - for example, on blog 2 we still get wp_posts instead of wp_2_posts

Expected behavior

The ORM should respect WordPress's blog switching and use correct table prefixes for all database operations after switch_to_blog() is called.

Your setup

• WordPress Version: 6.7.1
• Module version: 4.0.0
• Are you using framework?: Bedrock

Additional context

Possible solutions to consider:

1. Add WordPress hook listener in the Database class:

add_action('switch_blog', function($new_blog_id, $prev_blog_id) {
    Database::$instance = null; // This will force new instance with updated prefix on next `getInstance()` call
});

2. Modify getTablePrefix() to always use current WordPress prefix:

public function getTablePrefix()
{
    return $this->db->prefix;
}

[rafaucau]

I just noticed one more thing:

Some tables like wp_users are shared across all sites in a Multisite network and should always use the base prefix ($wpdb->base_prefix) instead of site-specific prefixes. This also applies to other potential shared tables added by plugins.

This case could be handled by adding a $useBasePrefix property to models.

switch_to_blog(2);

$post = new Post();
echo $post->getTable(); // wp_2_posts

$user = new User();
echo $user->getTable(); // wp_users (always base prefix)

[dimitriBouteille]

Hi @rafaucau

Thank you for creating this issue and adding as much detail as possible. I have never used WordPress with multisite, it's also for this reason that the library does not support multisite.

I will inquire about this part in order to fix bug and also add tests.

Unfortunately, I will not be able to fix the bug before January/February 2025. If you wish you can open a PR that I will look quickly to include it in the next version.

Best Regards,

[rafaucau]

@dimitriBouteille I have created a PR: #101