Eloquent Query

Hi all,

I have a difficult situation to convert classical query to Eloquent query. If someone could look a little bit much appreciate.

I have 3 models Post, GroupTag and TagCode models with GroupTag->hasMany(TagCode)

posts table 
| id  | tag|views|
| ----| ---- |-------|
|1|ABX|10|
|2|BCY|10|
|3|ABCDE|10|
|4|BCE|10|

group_tags Table
| id  | name|
| ----| ---- |
|1|group_A|
|2|group_B|

Tag_code Table
| id  | code|groupTag_id|
| ----| ---- |-------|
|1|AB|1|
|2|ABC|1|
|3|ABD|1|
|4|BC|2|
|5|BCD|2|
|6|BCE|2|

What I want to accomplish is to have in post table the corespondent group_tag based on the relation that tag_code.code regexp concat("^",post.tag) order by length(post_tag) DESC limit 1. In other words to find the longest match code and get the correspondent group_tag, sum over views and group by group_tag.

The expected results should be:

group_tag|sum(views)|
|------------|---------------|
|A|20|
|B|20|