Using galleries (or other accesses to ORDER BY FIELD() ) in WordPress over PostgreSQL using PG4WP driver

Galleries in WordPress 3.6 does not show in a setup over a PostgreSQL database using PG4WP driver.

The gallery functionality makes use of ORDER BY FIELD() MySQL command that is not handled by the PG4WP 1.3.1 driver.

Tweak the driver file ( /path to WordPress install/wp-content/pg4wp/driver_pgsql.php ) to include the following lines
//ORDER BY FIELD ISSUE -- Vitorio 2015-10
$pattern = '/ FIELD\(\s*([\w.]+\s*)((,\s*\d+)*)\s*\)/i';
if( preg_match($pattern, $sql, $matches))
$name = $matches[1];
$fields = preg_split('/\s*,\s*/', $matches[2]);
$order_by = " CASE ";
$count = 1;
foreach($fields as $field)
$order_by .= "WHEN ".$name."='".$field."' THEN ".$count++." ";
$order_by .= "ELSE ".$count." END";
$sql = preg_replace( $pattern, $order_by, $sql);
error_log( '['.microtime(true)."] Changing $pattern to $order_by in $sql\n", 3, PG4WP_LOG.'pg4wp_SELECT.log');

at the pg4wp_rewrite function, inside the
if( 0 === strpos($sql, 'SELECT'))
I put it just before
// UNIX_TIMESTAMP in MYSQL returns an integer
$pattern = '/UNIX_TIMESTAMP\(([^\)]+)\)/';
$sql = preg_replace( $pattern, 'ROUND(DATE_PART(\'epoch\',$1))', $sql);

but the position is arbitrary.


This entry was posted in IT stuff and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s