[SQL] How to get all possible records with highest value in specific column laravel

I have four tables:

Items:
| id | name |
| ---- | ------- |
| 1 | item 1 |
| 2 | item 2 |

Item-module:
| item_id | module_id |
| ------- | --------- |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |

Module:
| id | name | type |
| ---- | --------- | ----- |
| 1 | module 1 | type_1 |
| 2 | module 2 | type_2 |
| 3 | module 3 | type_2 |
| 4 | module 4 | type_3 |

Type priority:
| id | type | priority |
| ---- | --------- | -------- |
| 1 | type_1 | 14 |
| 2 | type_2 | 20 |
| 3 | type_3 | 27 |


Item could have many modules. Modules have priorities by their type. How could I get all item modules with the highest priority? So the result from this tables should be item with 'module 2' and 'module 3' because item doesn't have 'module 4' and 'module 1' has lower priority than 'module 2' and 'module 3'. So ex of output is

items [
id: 1,
name: item 1,
modules: [
[
id: 2,
name: module 2
],
[
id: 3,
name: module 3
]
]
]


But if we add 'module 4' for item than item should have only 'module 4'. 'Module 1' should be in output only if we delete all another modules

Continue reading...