Skip to content

Instantly share code, notes, and snippets.

@Lewiscowles1986 Lewiscowles1986/README.md
Last active Mar 7, 2019

Embed
What would you like to do?
Lazy load from an intelligent place

Lazy Loading (the smart way)

  1. Not all times when we have a Team will we want a list of User's or, approvers
  2. When we select UserApprover entries we want it to be per-team where deleted is False
  3. When we select FinancialApprover entries we want it to be per-team where deleted is False
  4. We may wish to select both UserApprover and FinancialApprover entries for a Team

Data model (simplified)

image

1. Selecting Team without User's

Select Team without User's

SELECT * FROM teams WHERE name = ?;

Select multiple Team without User's

SELECT * FROM teams /* [WHERE...] */;

2. Select UserApprover entries for Team that are not deleted

SELECT UA.*, U.handle, T.name FROM user_approvers UA
LEFT JOIN users U ON UA.user_id = U.id AND U.deleted = 'f'
LEFT JOIN teams T ON UA.team_id = T.id AND T.deleted = 'f'
WHERE T.name = ? AND UA.deleted = 'f'

3. Select FinancialApprover entries for Team that are not deleted

SELECT FA.*, U.handle, T.name FROM financial_approvers FA
LEFT JOIN users U ON FA.user_id = U.id AND U.deleted = 'f'
LEFT JOIN teams T ON FA.team_id = T.id AND T.deleted = 'f'
WHERE T.name = ? AND FA.deleted = 'f'

4. Select both UserApprover and TeamApprover entries for a Team

SELECT UA.*, U.handle, T.name FROM user_approvers UA
LEFT JOIN users U ON UA.user_id = U.id AND U.deleted = 'f'
LEFT JOIN teams T ON UA.team_id = T.id AND T.deleted = 'f'
WHERE T.name = ? AND UA.deleted = 'f'
UNION
SELECT FA.*, 'financial_approver' AS type, U.handle, T.name FROM financial_approvers FA
LEFT JOIN users U ON FA.user_id = U.id AND U.deleted = 'f'
LEFT JOIN teams T ON FA.team_id = T.id AND T.deleted = 'f'
WHERE T.name = ? AND FA.deleted = 'f'
@startuml
class User {
+id
+handle : unique
+created_at
+updated_at
+deleted
}
class Team {
+id
+name : unique
+created_at
+updated_at
+deleted
}
class FinancialApprover {
+id
+team_id
+user_id
+created_at
+updated_at
+deleted
}
class UserApprover {
+id
+team_id
+user_id
+type
+created_at
+updated_at
+deleted
}
UserApprover }-- Team
FinancialApprover }-- Team
User ^-- FinancialApprover
User ^-- UserApprover
@enduml
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.