Add filter for wp-db query

[filosofo]

wp-includes/wp-db.php could really use a filter to let you modify db queries.

Sometimes you just need to change field names (say if you're trying to use another app's table in place of one of WP's), and there often aren't any other ways to do it.

[markjaquith]

I'd absolutely be in favor of that. Would be very powerful. Could even be used to to modify wp-admin queries.

[ryan]

wp-db is loaded before apply_filters is available and before plugins are loaded.

[filosofo]

wp-db is loaded, but the wpdb->query function isn't called until after plugins have loaded.

I'm using this patch (to share a user table with another app), so I know it works.

[filosofo]

I just attached another patch that will apply the filter only if apply_filters is loaded, just in case someone should call wp-db.php before then.

How about that? This would be a really helpful feature to have.

[ryan]

A number of queries are run before plugins are loaded. The options table is hit several times, for example. Those queries couldn't be altered by a plugin. This reduces the utility of a filter a bit. A filter might still be worthwhile, but most folks who want to tweak wp-db create a new db class that inherits from wpdb and replaces methods it wants to change.

[markjaquith]

I like it, even with that small limitation.

[filosofo]

A filter might still be worthwhile, but most folks who want to tweak wp-db create a new db class that inherits from wpdb and replaces methods it wants to change.

I'm trying to understand how that would work. Since the wpdb class is instantiated as $wpdb before plugins are loaded (and therefore before any potential child classes), wouldn't any attempt to replace a wpdb method (as instantiated in $wpdb) be fruitless?

[majelbstoat]

I believe he means that you could do something like this:

class Mywpdb extends wpdb {

function query($query) {
  str_replace('SELECT', 'SELECT DISTINCT', $query);
  parent::query($query);
}

}

$mywpdb = new MyWpdb();
$mywpdb->get_results("SELECT id FROM post WHERE name like '%filosofo%'");

which would only get you distinct results. Not a good example obviously, but you get the idea. Nevertheless, I also would like the extra flexibility that this filter would provide.

[filosofo]

majelbstoat, I don't think your example changes the query method instantiated in $wpdb. The point of having a filter like the one I'm proposing is to change existing WordPress db requests instantiated in $wpdb--requests that we can't easily change through a filter or whatnot--not to affect new instantiations that you might use in a plugin.

My question is how this can be done through inherited classes, as I take Ryan to be saying, although I think I may be misunderstanding his point.

[masquerade]

PHP allows you to override methods in child classes. You would first make a class that extends wpdb, (preferably while sharing the same connection, it can be done), and $wpdb = new My_wpdb; This allows much more flexibility than just adding a filter on the queries.

[filosofo]

Thanks masquerade, I was just missing the obvious: instantiating the child class as $wpdb. Duh.

Sorry, I haven't had much sleep recently. :-)

So please bear with me if I'm still not thinking clearly, but wouldn't the re-defined $wpdb apply only to those db requests that occur after plugins are loaded? In other words, isn't the set of db requests that you could affect via a child class (defined in a plugin) no greater than the set of db requests that you could affect via my proposed filter?

Except it seems the child class technique has the disadvantage of making you redefine the table variables and the entire query method, when you might want just to make a simple string replacement.

[masquerade]

Except it seems the child class technique has the disadvantage of making you redefine the table variables and the entire query method, when you might want just to make a simple string replacement.

No it doesn't, see the example above. Child classes inherit all methods and class variables/constants. None of the methods even have to be rewritten, you can do your modifications to the query and then pass it to parent::method.

[filosofo]

Ah, the method inheritance works very nicely. Beautiful!

But as far as I can tell you still have to re-define the WP tables, as they're defined (in wp-settings.php) external to the wpdb class.

For example, suppose you want to correct this ambiguity like so:

class my_wpdb extends wpdb {
        function query($query) {
                parent::query(str_replace('SELECT COUNT(DISTINCT ID)', "SELECT COUNT(DISTINCT $this->posts.ID)", $query));
        }
}

$wpdb = new my_wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

That by itself will produce a db error, as now your tables are undefined. (And hopefully there aren't any other plugins that have defined custom tables prior to yours). You have to add the following:

        $wpdb->posts            = $table_prefix . 'posts';
        $wpdb->users            = $table_prefix . 'users';
        $wpdb->categories       = $table_prefix . 'categories';
        $wpdb->post2cat         = $table_prefix . 'post2cat';
        $wpdb->comments         = $table_prefix . 'comments';
        $wpdb->links            = $table_prefix . 'links';
        $wpdb->linkcategories   = $table_prefix . 'linkcategories';
        $wpdb->options          = $table_prefix . 'options';
        $wpdb->postmeta         = $table_prefix . 'postmeta';
        $wpdb->usermeta         = $table_prefix . 'usermeta';
        $wpdb->prefix           = $table_prefix;

All of which seems more cumbersome than something like

add_filter('query', create_function('$q', 'global $wpdb; return str_replace("SELECT COUNT(DISTINCT ID)", "SELECT COUNT(DISTINCT $wpdb->posts.ID)",$q);'));

[ryan]

[4153] and [4154] will help with inheritance.

So, you put your custom wp-db.php in wp-content. It sets $wpdb = true and then include wp-includes/wp-db.php. The wpdb class is loaded but not instantiated since $wpdb is already set. You then define your new class that inherits from wpdb and instantiate it as $wpdb.

[matt]

Let's call it wp-content/db.php instead of wp-db.php.

[ryan]

[4160]

[matt]

I think this is mostly fixed.

[markjaquith]

Patch for 2.0.x

[markjaquith]

Patch for trunk

[markjaquith]

While a user-replaceable DB class is nice, it's a poor solution to this problem (being unable to use a plugin to do a simple str_replace() in a SQL query.)

Any plugin that wants to filter a SQL query now has to have a complicated two-file (and two-directory) install. It also means that only one plugin can be filtering SQL queries at a time, as there can only be one db.php

Even with the caveat that pre-plugin queries couldn't be filtered, this is a useful addition.

Updated patches uploaded for 2.0.x and trunk. Is there a good reason that this shouldn't go in?

[ryan]

Okay, why not. Let 'er rip.

[markjaquith]

(In [4619]) Add ?\195?\188berpowerful "query" filter, for SQL queries. fixes #2721

[markjaquith]

(In [4620]) Add ?\195?\188berpowerful "query" filter, for SQL queries. fixes #2721

[filosofo]

Yay!!