Drupal 7: SQL Query To Print A List Of The URL Alias Created For Nodes And Users

SELECT
	CASE
		WHEN n.nid IS NOT NULL THEN n.nid
		ELSE u.uid
	END AS 'id',
	CASE
		WHEN n.title IS NOT NULL THEN n.title
		ELSE u.name
	END AS 'title',
	CASE
		WHEN n.type IS NOT NULL THEN n.type
		ELSE 'user'
	END AS 'type',
	ua.alias
FROM
	url_alias ua
LEFT JOIN node n ON
	n.nid = REPLACE(ua.source, 'node/', '')
LEFT JOIN users u ON
	u.uid = REPLACE(ua.source, 'user/', '')
WHERE
	(n.type IN ('page', 'article')
		AND n.status = 1)
	OR u.status = 1;

Note that this only returns active users and published nodes of the type 'page' and 'article'. Useful for migration purposes.

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
1 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.