User:Dantman/Abstract query language/examples

// 50 of users in revisions for a specific page after a certain timestamp ordered by timestamp
$dbw->select( 'revision',
	'rev_user',
	array(
		'rev_page' => $this->mArticle->getId(),
		'rev_timestamp > '.$dbw->addQuotes( $dbw->timestamp($edittime) )
	),
	__METHOD__,
	array( 'ORDER BY' => 'rev_timestamp ASC', 'LIMIT' => 50 ) );

$q->query("
	SELECT { User ?u }
	FROM { Revision ?rev }
	WHERE { ?rev:page IS ?page, ?rev:timestamp > ?edittime }
	ORDER { ?rev:timestamp }
	LIMIT 50",
	array( 'page' => $this->mArticle->getId(), 'edittime' => $edittime ) );


// A single log entry and it's associated user
$dbr->selectRow(
	array( 'logging', 'user' ),
	array( 'log_type',
	       'log_action',
	       'log_timestamp',
	       'log_user',
	       'log_namespace',
	       'log_title',
	       'log_comment',
	       'log_params',
	       'log_deleted',
	       'user_name' ),
	array( 'log_namespace' => $this->mTitle->getNamespace(),
	       'log_title' => $this->mTitle->getDBkey(),
	       'log_type' => 'delete',
	       'log_action' => 'delete',
	       'user_id=log_user' ),
	__METHOD__,
	array( 'LIMIT' => 1, 'ORDER BY' => 'log_timestamp DESC' )
);

// We might theoretically be able to skip the user from the SELECT/ONE list.
$q->query("
	ONE { LogEntry ?log, User ?u }
	FROM { LogEntry ?log }
	WHERE { ?log:title IS ?title, ?log:type IS 'delete', ?log:action IS 'delete', ?log:user IS ?u }
	ORDER { ?log:timestamp DESC }",
	array( 'title' => $this->mTitle ) );


// A list of users with a specific e-mail address
$db->select(
	'user',
	'*',
	array( 'user_email' => $data['Email'] ),
	__METHOD__
);

$q->query("SELECT { User ?u } WHERE { ?user:email IS ?email }",
	array( 'email' => $data['Email'] ));


// A list of pages in the MediaWiki: or MediaWiki_talk: namespace
$dbr->select( 'page',
	array( 'page_namespace', 'page_title' ),
	array( 'page_namespace' => array( NS_MEDIAWIKI, NS_MEDIAWIKI_TALK ) ),
	__METHOD__,
	array( 'USE INDEX' => 'name_title' )
);

// Note, in this kind of circumstance the in-built db optimization should be what decides
// to apply a USE INDEX to this abstract query based on an understanding of the database
// structure and what the best thing to do for the specific database engine is.
$q->query("
	SELECT { Page ?p }
	WHERE { ?page:title:namespace IS ?ns }",
	array( 'ns' => array( NS_MEDIAWIKI, NS_MEDIAWIKI_TALK ) ));


// Prefixindex: A list of pages and a boolean saying whether they are a redirect or not
$dbr->select( 'page',
	array( 'page_namespace', 'page_title', 'page_is_redirect' ),
	array(
		'page_namespace' => $namespace,
		'page_title' . $dbr->buildLike( $prefixKey, $dbr->anyString() ),
		'page_title >= ' . $dbr->addQuotes( $fromKey ),
	),
	__METHOD__,
	array(
		'ORDER BY'  => 'page_title',
		'LIMIT'     => $this->maxPerPage + 1,
		'USE INDEX' => 'name_title',
	)
);

$q->query("
	SELECT { Title ?title, Boolean ?is_redirect }
	FROM { Page ?page }
	WHERE {
		?page:title:namespace IS ?ns,
		?page:title:text STARTS WITH ?prefix,
		?page:title:text >= ?key,
		?page:title IS ?title,
		?page:is_redirect IS ?is_redirect
	}
	ORDER { ?page:title }
	LIMIT ?limit",
	array( 'ns' => $namespace, 'prefix', $prefixKey, 'limit' => $this->maxPerPage + 1 ) );


// A list of templates which are linked to from a specific page 
$dbr->select(
	array('pagelinks', 'page'),
	'pl_title',
	array(
		'page_id = pl_from',
		'pl_namespace' => NS_TEMPLATE,
		'page_namespace' => $disPageObj->getNamespace(),
		'page_title' => $disPageObj->getDBkey()),
	__METHOD__ );

$q->query("
	SELECT { Title ?to }
	FROM { PageLink ?link }
	WHERE {
		?page:title IS ?from,
		?link:from IS ?page,
		?link:to:title IS ?to,
		?link:to:title:namespace IS Namespace['Template']
	}",
	array( 'from' => $disPageObj ) );


// A count of the number of watchlist entries fror a user
$dbr->select( 'watchlist', 'COUNT(*) AS count', array( 'wl_user' => $user->getId() ), __METHOD__ );

$q->query("
	COUNT { WatchlistEntry ?wl } AS ?count
	WHERE { ?wl:user IS ?user }",
	array( 'user' => $user ) )


// A list of pages in a user's watchlist sorted by title namespace then text
$dbr->select(
	array( 'watchlist', 'page' ),
	array(
		'wl_namespace',
		'wl_title',
		'page_id',
		'page_len',
		'page_is_redirect',
		'page_latest'
	),
	array( 'wl_user' => $user->getId() ),
	__METHOD__,
	array( 'ORDER BY' => 'wl_namespace, wl_title' ),
	array( 'page' => array(
		'LEFT JOIN',
		'wl_namespace = page_namespace AND wl_title = page_title'
	) )
);

$q->query("
	SELECT { Page ?page }
	FROM { WatchlistEntry ?wl }
	WHERE {
		?wl:user IS ?user,
		?wl:page IS ?page
	}
	ORDER { ?page:title:namespace, ?page:title:text }",
	array( 'user' => $user ) );


// Up to 5000 titles of pages that have category links 
$dbr->select(
	array( 'page', 'categorylinks' ),
	array( 'page_namespace', 'page_title' ),
	array( 'cl_from=page_id', 'cl_to' => $name ),
	__METHOD__,
	array( 'LIMIT' => '5000' )
);

$q->query("
	SELECT { ?page:title AS ?title }
	FROM { CategoryLink ?link }
	WHERE { ?link:from IS ?page, ?link:to IS ?name }
	LIMIT 5000",
	array( 'name' => $name )
);