Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- User
- .joins(:clients)
- .joins('LEFT JOIN positions ON positions.client_id = clients.id')
- .select('users.*, SUM(CASE users.id WHEN clients.manager_id THEN 1 ELSE 0 END) as clients_count, '\
- 'SUM(CASE positions.status WHEN 0 THEN 1 ELSE 0 END) as active_positions_count, '\
- 'SUM(CASE positions.status WHEN 1 THEN 1 ELSE 0 END) as passive_positions_count')
- .having('SUM(CASE positions.status WHEN 0 THEN 1 ELSE 0 END) > 0 '\
- 'OR SUM(CASE positions.status WHEN 1 THEN 1 ELSE 0 END) > 0')
- .group('users.id')
- User
- .joins('INNER JOIN ('\
- 'SELECT '\
- 'clients.id, '\
- 'clients.manager_id, '\
- 'SUM(CASE WHEN positions.status = 0 THEN 1 ELSE 0 END) AS active_positions_count, '\
- 'SUM(CASE WHEN positions.status = 1 THEN 1 ELSE 0 END) AS passive_positions_count '\
- 'FROM clients '\
- 'INNER JOIN '\
- 'positions ON positions.client_id = clients.id AND positions.status IN (0, 1) '\
- 'GROUP BY clients.id'\
- ') AS sums ON sums.manager_id = users.id')
- .select('users.*, '\
- 'COUNT(sums.manager_id) AS clients_count, '\
- 'CAST(SUM(sums.active_positions_count) AS BIGINT) AS active_positions_count, '\
- 'CAST(SUM(sums.passive_positions_count) AS BIGINT) AS passive_positions_count')
- .group('users.id')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement