Posts tagged with "SQL"

Querying WordPress Taxonomies

Posted within Development on by Cornflower Design

To produce a mega-menu for a recent WordPress project, I had a requirement to show all the terms within one taxonomy that were associated with Posts that a specific term from a second taxonomy was also associated with. Tricky. It took a little while to get my head around the problem, but I came up with a solution that did just what was needed.

if ( !function_exists( 'get_associated_terms' ) ) {
	function get_associated_terms( $taxonomy_slug, $term_id = 0, $post_type = 'post' ) {
		global $wpdb;

		$sql = "SELECT DISTINCT $wpdb->terms.term_id, $wpdb->terms.name, $wpdb->terms.slug, $wpdb->terms.term_group, $wpdb->term_taxonomy.term_taxonomy_id, $wpdb->term_taxonomy.taxonomy, $wpdb->term_taxonomy.description, $wpdb->term_taxonomy.parent, $wpdb->term_taxonomy.count
			FROM $wpdb->terms
			INNER JOIN $wpdb->term_taxonomy ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id
			INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
			INNER JOIN $wpdb->posts ON $wpdb->term_relationships.object_id = $wpdb->posts.ID
			INNER JOIN $wpdb->term_relationships tr2 ON $wpdb->posts.ID = tr2.object_id
			INNER JOIN $wpdb->term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
			WHERE $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = %s AND $wpdb->term_taxonomy.taxonomy = %s AND tt2.term_id = %d
			ORDER BY $wpdb->terms.name";

		$safe_sql = $wpdb->prepare( $sql, $post_type, $taxonomy_slug, $term_id );
		$results = $wpdb->get_results( $safe_sql, OBJECT );

		return $results;
	}
}

Once added to the WordPress theme functions.php file or a plugin, its used in the same way you’d use one of the built-in functions, like get_terms(), except my function accepts fewer parameters. It returns an Object that can be looped through in the normal way.

$terms = get_associated_terms( 'tax1', 256 );

foreach($terms as $term) {
	echo '<a href="/tax1/' . $term->slug . '" class="' . $term->taxonomy . '">' . $term->name . '</a>';
}

The first parameter in the function is the name of the taxonomy you wish to return. The second parameter is the ID of a term within your starting taxonomy. Running my example would return an Object containing all terms within the ‘tax1′ taxonomy which are assigned to Posts that also have term 256 assigned to them.

Shrinking MSSQL Log Files Automatically

Posted within Development on by Ryan Ball

The MSSQL server for the SharePoint farm I manage always seems to be running out of room. After a little rummaging, I found the culprit – the database log files. For databases that ranged from 10MB to 45MB, the log files were as large as 450MB – which, when you calculate the space used by 50+ databases, all adds up.

To solve the issue I created a new scheduled task on the server which triggers the following script (found at the end of this post) once a week.

DECLARE @DBName varchar(255)
DECLARE @DBLogicalFileName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR

SELECT DATABASE_NAME = db_name(MaTableMasterFiles.database_id), MaTableMasterFiles.Name
FROM sys.master_files MaTableMasterFiles
WHERE

-- ONLINE
MaTableMasterFiles.State = 0

-- Only look at databases to which we have access
AND has_dbaccess(db_name(MaTableMasterFiles.database_id)) = 1

-- Not master, tempdb or model
AND db_name(MaTableMasterFiles.database_id) NOT IN ('Master','tempdb','model')

AND type_desc LIKE 'log'
GROUP BY MaTableMasterFiles.database_id, MaTableMasterFiles.name
ORDER BY 1

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY')
EXEC ('Use [' + @DBName + '] DBCC SHRINKFILE ("' + @DBLogicalFileName + '")')
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

ROW_NUMBER()

Posted within Development on by Ryan Ball

To simply returning a limited number of records while using SubSonic I added a position field to the View.

SELECT ROWID= ROW_NUMBER() OVER ( ORDER BY ProductID ASC), ProductID, [Name]
FROM Production.Product

SQL Server 2005 ROW_NUMBER()

Random Records (continued)

Posted within Development on by Ryan Ball

To return one or more records from a Microsoft Access database in a random order, you need to use the Rnd() function. When called directly from within an SQL query, the random seed is cached – resulting in your records always returning in the same order. To get around this issue, you need to ensure that a different value is passed to the Rnd() function each time it is called – this gives Access a kick and forces it to make use of the new seed.

<%
Randomize()
intRandom = Int(1000 * Rnd) + 1
sqlQuery = "SELECT Rnd(" & (-1 * (intRandom)) & " * Id), Id, Title, Description " & _
"FROM MyTable " & _
"ORDER BY 1"
Set objRS = objConn.Execute(sqlQuery)
%>

Random Records

Posted within Development on by Ryan Ball

Just adding these code snippets as a reminder for future projects.

MySQL
SELECT someColumn FROM someTable ORDER BY RAND() LIMIT 1

MS SQL Server 7.0 and above (running on Windows 2000)
SELECT TOP 1 someColumn FROM someTable ORDER BY NEWID()

MS SQL Server 7.0 and above (not running on Windows 2000) 
SELECT TOP 1 someColumn FROM someTable ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))