Skip to content

Instantly share code, notes, and snippets.

@ixmatus ixmatus/gist:5249467
Created Mar 26, 2013

Embed
What would you like to do?
SELECT
COUNT(ranktrack_urls.id)
FROM
ranktrack_urls
JOIN users ON ranktrack_urls.user_id = users.id
JOIN pools ON pools.id = (
SELECT
id
FROM
pools
WHERE
user_id = ranktrack_urls.user_id
AND pools.removed = 0
AND pools.service_id = 2
UNION
SELECT
id
FROM
pools
WHERE
user_id = ranktrack_urls.user_id
AND pools.removed = 0
AND pools.service_id IN(1, 3)
LIMIT 1
)
JOIN subscriptions ON ranktrack_urls.user_id = subscriptions.user_id
JOIN pivot_ranktrack_url_google_settings ON ranktrack_urls.id = pivot_ranktrack_url_google_settings.ranktrack_url_id
LEFT JOIN ranktrack_features ON(
SELECT
ranktrack_feature_id
FROM
pivot_ranktrack_features
WHERE
pool_id = pools.id
AND removed = 0
)= ranktrack_features.id
LEFT JOIN ranktrack_google_languages ON ranktrack_google_languages.id =(
SELECT
ranktrack_google_language_id
FROM
pivot_ranktrack_url_google_settings
WHERE
removed = 0
AND ranktrack_url_id = ranktrack_urls.id
)
LEFT JOIN ranktrack_google_tlds ON ranktrack_google_tlds.id =(
SELECT
ranktrack_google_tld_id
FROM
pivot_ranktrack_url_google_settings
WHERE
removed = 0
AND ranktrack_url_id = ranktrack_urls.id
)
LEFT JOIN countries ON countries.cdh =(
SELECT
country_cdh
FROM
pivot_ranktrack_url_google_settings
WHERE
removed = 0
AND ranktrack_url_id = ranktrack_urls.id
)
WHERE
ranktrack_urls.removed = 0
AND subscriptions.active = 1
AND users.removed = 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.