Project: Okul Database · Hub: Okul Database — Reference
Reference: media_types & media_roles
The media table uses a polymorphic pattern:
media.media_type_id→ which entity type owns this filemedia.media_role_id→ what role/purpose the file serves for that entitymedia.row_id→ the ID of the owning entity
media_types
| id | name | Related Table |
|---|---|---|
| 1 | Article | articles |
| 2 | School | schools |
| 3 | User | users |
| 4 | College | colleges |
| 5 | Gallery | galleries |
| 6 | Announcement | announcements |
| 7 | Scholarship | scholarships |
| 8 | CustomerAgreement | customer_agreements |
| 9 | Achievements | achievements |
| 10 | AnnouncementEvents | announcement_events |
| 11 | AgreementDocuments | agreement_documents |
media_roles
| id | role | media_type_id | Description |
|---|---|---|---|
| 1 | cover | 1 (Article) | Article cover image |
| 2 | content | 1 (Article) | Article inline image |
| 3 | profile | 2 (School) | School profile photo |
| 4 | gallery | 2 (School) | School gallery photo |
| 5 | profile | 3 (User) | User avatar |
| 6 | thumb | 4 (College) | College thumbnail |
| 7 | cover | 4 (College) | College cover image |
| 8 | content | 4 (College) | College content image |
| 9 | listing | 5 (Gallery) | Gallery listing image |
| 10 | content | 5 (Gallery) | Gallery content image |
| 11 | content | 6 (Announcement) | Announcement image |
| 12 | Kapak ve İçerik | 7 (Scholarship) | Scholarship cover + content |
| 13 | İçerik | 7 (Scholarship) | Scholarship content image |
| 14 | video | 2 (School) | School video |
| 15 | agreement | 8 (CustomerAgreement) | Signed agreement document |
| 16 | logo | 2 (School) | School logo |
| 17 | announcement-achievements | 9 | Achievement announcement |
| 18 | announcement-events | 10 | Event announcement |
| 19 | agreement-documents | 11 | Agreement supporting docs |
Fetching School Media
-- All school photos (profile + gallery)
SELECT m.*, mt.name as type_name, mr.role
FROM media m
JOIN media_types mt ON mt.id = m.media_type_id
JOIN media_roles mr ON mr.id = m.media_role_id
WHERE m.media_type_id = 2 -- School
AND m.row_id = <school_id>
AND m.deleted_at IS NULL
ORDER BY mr.role, m.created_at;
-- Only profile photos
WHERE m.media_type_id = 2 AND m.media_role_id = 3 AND m.row_id = <school_id>
-- Count schools with at least one gallery photo
SELECT COUNT(DISTINCT row_id) as schools_with_gallery
FROM media
WHERE media_type_id = 2 AND media_role_id = 4 AND deleted_at IS NULL;Related
- domain-content-education — Content domain