Database Create View

I’m not sure what you’re trying to view. I know all the various tables in the backend are built by extending the WP_List_Table class. See the various source files in wp-admin/includes/class-wp-*-list-table.php for how this is done. I hope this helps.

If not, perhaps try describing more specifically what you are trying to do.

I’m not trying to view anything – I’m trying to create a database view in much the same way as creating a new database table.

This is specifically to summarise (dynamically) the data stored by a plugin so that I don’t have to search through hundreds of thousands of rows and do a SUM() as part of a complex, custom SQL query.

I had hoped that I could use the functionality that plugins use to create a new table in the database (with dbDelta) but it doesn’t seem to work for views.

I think I understand what you’re trying to do now, I don’t think it’s possible to leverage that functionality in the way you are expecting, but I’m not totally sure.

I am pretty sure you can generate a view similar to what you envision by using WP_List_Table. There is a learning curve in using it, but once you get how it works, it’s a powerful tool. You still end up with a complex query, but it’s built dynamically in the prepare_items() method based on the state of various form elements.

The display() method is then used to display the results of the query in table form. This all typically appears in the WP backend, but I see no reason it would not work on the frontend as well if need be.

If this is not close to what you are seeking, I’m afraid I just don’t get it and I’m sorry to take up your time.

I’m not familiar with WP_List_Table, but going on the text at the top of class-wp-list-table.php, it is a “Base class for displaying a list of items in an ajaxified HTML table.”

If my assumption is correct – that it is a class to do things like building alternate post list pages on the Dashboard (etc.) – then that’s not what I’m trying to do.

I may be completely wrong, of course, but if it’s a complex query it’ll still suck even if it would do what I want – without a summary database view, the query is so heavy it effectively shuts down the site while it runs… for 2+ minutes!

I’m trying to build a database view, behind the scenes in the MySQL database. In other words, I’m trying to run SQL like this:

CREATE ALGORITHM = UNDEFINED VIEW wp_wti_totals ( post_id, value )
AS SELECT post_id, SUM( value ) FROM wp_wti_like_post GROUP BY post_id

But dynamically, with the correct DB prefixes and stuff. I was trying to do it with dbDelta, as shown below, but it doesn’t work (presumably ‘cos it’s a view, not a table):

$table_name = "{$wpdb->prefix}wti_totals";
if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
	$sql = "CREATE ALGORITHM = UNDEFINED VIEW {$wpdb->prefix}wti_totals ( post_id, value )
		AS SELECT post_id, SUM( value )
		FROM {$wpdb->prefix}wti_like_post
		GROUP BY post_id" ;
	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
	dbDelta( $sql );
}

Hope that makes it clearer.

Ah! I get it now! Sorry for being so dense. I’m a dunce when it comes to SQL, so I’ll be no help at all. This really has nothing to do with WordPress. Perhaps you can find a mySQL forum where people actually know something about this? Or simply try stackexchange, lots of knowledge there too. Sorry I couldn’t help, good luck on your quest.

Not dense at all – it was good of you to try to help with what you thought I wanted!

Unfortunately, this is very much a WP issue: the SQL itself is perfectly acceptable… WP just won’t run it or needs some special prodding to do so, which is the problem I have. It will run custom queries, no problem; it will create tables (apparently, though I haven’t tried it); it just won’t build a view. Pffft!

Thanks again for trying to help. ??

I’m at least a little slow, I didn’t realize dbDelta was a WP function, thus my claim of not being a WP issue. Out of curiosity, I reviewed the function’s source code. It’s clear that it only accepts certain types of queries and ignores the rest. Your’s is one of those ignored, so dbDelta is a dead end for you.

Have you tried using $wpdb->query()? Other than some housekeeping, it essentially just passes your query straight through to mysql_query() without the need for you to setup a DB connection. Maybe I’m missing something but I would think any acceptable SQL should be able to be passed this way successfully. This is the method ultimately called by dbDelta() when it’s done doing whatever it is it does.

If I may ask, what’s so special about dbDelta? I see how it’s useful to core WP developers to script DB upgrades, beyond that, I don’t really understand what it’s doing that’s so important.

Sorry for the long wait (only a week, feels like a month!).

First things first: dbDelta was just because I found an article stating it was the standard way to create a table, so I figured it would be the same for a view. I didn’t check much beyond there when it didn’t work!

The $wpdb->query() suggestion works, as long as all the {$wpdb->prefix} tags are in place (I had them as just “$wpdb” originally, which was causing even more problems), so a big THANK YOU for that suggestion.

The simplest solution turned out to be the one that works. ??

Oh, and here’s the final code snippet:

$table_name = "{$wpdb->prefix}wti_totals";
if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
	$sql = "CREATE VIEW {$wpdb->prefix}wti_totals ( post_id, value )
		    AS SELECT post_id, SUM( value )
		FROM {$wpdb->prefix}wti_like_post
		GROUP BY post_id" ;
	$result = $wpdb->query($sql);
}