Skip to content

Instantly share code, notes, and snippets.

@haakym

haakym/gwm-query.sql

Created December 21, 2017 13:27
Show Gist options
  • Save haakym/c61c268f94dd8d753d52fffc89392edb to your computer and use it in GitHub Desktop.
Save haakym/c61c268f94dd8d753d52fffc89392edb to your computer and use it in GitHub Desktop.
Download ZIP
Stupid query with group wise maximum
Raw
gwm-query.sql
select
applications.id,
applications.reference_number,
applications.status,
applications.application_type,
applications.created_at,
applications.date_awarded,
applications.speciality,
applicants.first_names,
applicants.last_name,
applicants.nationality,
users.email,
organisations.name,
qualifications.name,
last_application_actions.actions_created_at,
admin_users.email
from users
join applicants
on applicants.user_id = users.id
left join (
(
select
applications.id,
applications.reference_number,
applications.status,
applications.application_type,
applications.applicant_id,
applications.created_at,
applications.organisation_id,
applications.qualification_id,
academic_certificates.year_of_award as date_awarded,
academic_certificates.speciality as speciality
from applications
left join academic_certificates
on academic_certificates.id = applications.application_id
where applications.application_type = 'App\\Models\\AcademicCertificate'
)
UNION
(
select
applications.id,
applications.reference_number,
applications.status,
applications.application_type,
applications.applicant_id,
applications.created_at,
applications.organisation_id,
applications.qualification_id,
confirmation_of_registrations.end_date as date_awarded,
'n/a' as speciality
from applications
left join confirmation_of_registrations
on confirmation_of_registrations.id = applications.application_id
where applications.application_type = 'App\\Models\\ConfirmationOfRegistration'
)
UNION
(
select
applications.id,
applications.reference_number,
applications.status,
applications.application_type,
applications.applicant_id,
applications.created_at,
applications.organisation_id,
applications.qualification_id,
experience_letters.end_date as date_awarded,
'n/a' as speciality
from applications
left join experience_letters
on experience_letters.id = applications.application_id
where applications.application_type = 'App\\Models\\ExperienceLetter'
)
UNION
(
select
applications.id,
applications.reference_number,
applications.status,
applications.application_type,
applications.applicant_id,
applications.created_at,
applications.organisation_id,
applications.qualification_id,
fellowship_certificates.award_date as date_awarded,
'n/a' as speciality
from applications
left join fellowship_certificates
on fellowship_certificates.id = applications.application_id
where applications.application_type = 'App\\Models\\FellowshipCertificate'
)
UNION
(
select
applications.id,
applications.reference_number,
applications.status,
applications.application_type,
applications.applicant_id,
applications.created_at,
applications.organisation_id,
applications.qualification_id,
membership_certificates.award_date as date_awarded,
'n/a' as speciality
from applications
left join membership_certificates
on membership_certificates.id = applications.application_id
where applications.application_type = 'App\\Models\\MembershipCertificate'
)
UNION
(
select
applications.id,
applications.reference_number,
applications.status,
applications.application_type,
applications.applicant_id,
applications.created_at,
applications.organisation_id,
applications.qualification_id,
training_course_certificates.year_of_award as date_awarded,
training_course_certificates.course_title as speciality
from applications
left join training_course_certificates
on training_course_certificates.id = applications.application_id
where applications.application_type = 'App\\Models\\TrainingCourseCertificate'
)
) as applications
on applications.applicant_id = applicants.id
join organisations
on organisations.id = applications.organisation_id
left join qualifications
on qualifications.id = applications.qualification_id
left join (
select
gwmax_actions.application_id,
joined_actions.id as actions_id,
joined_actions.created_at as actions_created_at,
joined_actions.action_by as actions_action_by
from application_actions joined_actions
join (
select max(id) id, created_at, application_id, action_by
from application_actions
group by application_id
) gwmax_actions on joined_actions.id = gwmax_actions.id
) as last_application_actions on last_application_actions.application_id = applications.id
inner join users as admin_users
on admin_users.id = last_application_actions.actions_action_by
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment