WordPress.org

Make WordPress Core

Opened 8 years ago

Closed 7 years ago

Last modified 7 years ago

#2721 closed defect (bug) (fixed)

Add filter for wp-db query

Reported by: filosofo Owned by:
Milestone: 2.1 Priority: normal
Severity: normal Version: 2.1
Component: Administration Keywords: wp-db has-patch query
Focuses: Cc:

Description

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.

Attachments (4)

wp-db.diff (463 bytes) - added by filosofo 8 years ago.
wp-db.2.diff (503 bytes) - added by filosofo 8 years ago.
query_filter_20.diff (591 bytes) - added by markjaquith 7 years ago.
Patch for 2.0.x
query_filter_TRUNK.diff (591 bytes) - added by markjaquith 7 years ago.
Patch for trunk

Download all attachments as: .zip

Change History (26)

filosofo8 years ago

comment:1 markjaquith8 years ago

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

comment:2 ryan8 years ago

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

comment:3 filosofo8 years ago

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.

filosofo8 years ago

comment:4 filosofo8 years ago

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.

comment:5 ryan8 years ago

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.

comment:6 markjaquith8 years ago

I like it, even with that small limitation.

comment:7 filosofo8 years ago

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?

comment:8 majelbstoat8 years ago

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.

comment:9 filosofo8 years ago

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.

comment:10 masquerade8 years ago

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.

comment:11 filosofo8 years ago

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.

comment:12 masquerade8 years ago

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.

comment:13 filosofo8 years ago

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);'));

comment:14 ryan8 years ago

[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.

comment:15 matt8 years ago

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

comment:17 matt7 years ago

  • Resolution set to fixed
  • Status changed from new to closed

I think this is mostly fixed.

markjaquith7 years ago

Patch for 2.0.x

markjaquith7 years ago

Patch for trunk

comment:18 markjaquith7 years ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

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?

comment:19 ryan7 years ago

Okay, why not. Let 'er rip.

comment:20 markjaquith7 years ago

  • Resolution set to fixed
  • Status changed from reopened to closed

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

comment:21 markjaquith7 years ago

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

Note: See TracTickets for help on using tickets.