SpikeTheLobster

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

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

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

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.

Thanks to a prompt on Stackoverflow, I figured out what was wrong. As usual, assumption is the mother of all screwups!

I assumed “$wpdb->wti_like_post” converted to “DBprefix_wti_like_post”, as “$wpdb->posts” converts to “DBprefix_posts”. My assumption was wrong. It doesn’t convert to anything at all, as far as I can see, presumably because it’s not a built-in WordPress table.

What’s needed is “{$wpdb->prefix}wti_like_post“, which forces the DB prefix into place.

It works now.

Thanks very much to both commenters for the mental prompt from asking how the value comes out!

In case it will help other people, some discoveries:

2. This is a bad idea. Setting post_status to ‘trash’ via SQL apparently circumvents some built-in functionality, the practical result of which is that the posts get deleted but the attachments and everything else stay put. Very messy.

I was doing an UPDATE query with $wpdb->get_results(). Instead, it’s better to do a SELECT query with $wpdb->get_results() and pass the results to a foreach loop, like this (to trash posts after 30 days):

$dayquery = "SELECT * FROM $wpdb->posts
             WHERE $wpdb->posts.post_status = 'publish'
             AND $wpdb->posts.post_type = 'post'
             AND $wpdb->posts.post_date < DATE_SUB(NOW(), INTERVAL 30 DAY)";

$result = $wpdb->get_results($dayquery);
foreach ($result as $post) {
	setup_postdata($post);
	$postid = $post->ID;
	wp_delete_post($postid);
}

Hopefully this will fully trash items (including attachments).

EDIT: Removed. Figured it out.

Oh dear. ??

I had forgotten that syntax errors always look like they’re saying one thing is wrong when they actually mean another. Now I feel very silly.

Thanks very much, so obvious!

Thanks Aaron. No idea why the site wouldn’t let me do it… unless it’s Just Another Chrome Weirdout. ??

Works a treat! I chose “Custom” and entered “+90 days” (without quotes) and it set the date 90 days in the future, at the same time as the post was created.

Thanks very much, Aaron!

Thanks Aaron. I did have a look at the function description, but those things are written in some kind of bizarre, unhelpful language instead of normal English. ??

I’ll give that a shot and report back.

Ditto on this request: it defaults to the same day/time as the post is made, which is kinda… yeah.

I figure it needs something as a Custom “Default Date/Time Duration” setting, but since the only help on that is “see PHP strtotime function”, I’m rather lost.

A pointer on formatting a 90-day thing would be nice.

And as a feature request, how about an “x days” option in that dropdown? ??

@terence: if you upload the 2.7.9 version by FTP, overwriting the new one, you get all your options back (assuming you didn’t change stuff to try to fix the new one).

All the MB options – widgets dropped, header gone, settings defaulted, etc.

When I reverted back to the old version (2.7.9) they were all still stored, so everything’s fine. I just have to remember not to update the theme on the half-dozen sites where I use it!