Query companies and the number of WordPress support subscriptions and users.

orbis-query-companies-number-support-subscriptions.sql

SELECT
	company.name,
	company_subscriptions.number_support_subbscriptions,
	company_users.number_company_users
FROM
	orbis_companies AS company
		LEFT JOIN
	(
		SELECT
			subscription.company_id,
			COUNT( subscription.id ) AS number_support_subbscriptions
		FROM
			orbis_subscriptions AS subscription
				INNER JOIN
			orbis_subscription_types AS product
					ON subscription.type_id = product.id
		WHERE
			product.name IN (
				'WordPress onderhoud XS',
				'WordPress onderhoud S',
				'WordPress onderhoud M'
			)
				AND
			(
				subscription.cancel_date IS NULL
					OR
				subscription.expiration_date > NOW()
			)
		GROUP BY
			subscription.company_id
	) AS company_subscriptions
			ON company_subscriptions.company_id = company.id
		LEFT JOIN
	(
		SELECT
			user_company_p2p.p2p_to AS company_post_id,
			COUNT( user_company_p2p.p2p_id ) AS number_company_users
		FROM
			wp_p2p AS user_company_p2p
		WHERE
			user_company_p2p.p2p_type = 'orbis_users_to_companies'
		GROUP BY
			user_company_p2p.p2p_to
	) AS company_users
			ON company_users.company_post_id = company.post_id
WHERE
	company_subscriptions.number_support_subbscriptions > 0
GROUP BY
	company.id
ORDER BY
	number_company_users
;

Also see https://gist.github.com/remcotolsma/d72c00d46a39f6bc3b8b0184e46cec09 for hosting subscriptions.