Question: I have below tables with following fields

admins Table

```
 ->id (int)
 ->name (string)
 ->status (boolean)

```

locations Table

```
->id (int)
->name (string

```

)

I have many to many relation between this two tables via pivot table

admin\_locations

```
 ->admin_id
 ->location_id

```

My Model

```
Admin.php

public function locations()
    {
        return $this->belongsToMany('App\Location', 'admin_location');
    }

Location.php
    public function admin()
    {
        return $this->belongsToMany('App\Admin','admin_location');
    }

```

I want to fetch only admin whose status is 1 along with the relationship. I'm using the above query to fetch admin locations. How can i fetch admin locations where admin status is 1.

Answer: 
An option using `data.table` that makes use of the `measure/patterns` would be to use `melt`. In the example, the column names have common `patterns` as 'weight', 'height' which we specify it in `measure` parameter to convert it to 'long' format, then extract the numeric part with `sub` to create 'Ind'

```
library(data.table)
melt(setDT(df), measure = patterns("weight", "height"), value.name = c("W", "H"), 
  variable.name = "time")[, 
    Ind := as.integer(sub("\\D+", "", data))][order(Ind)][, .(time, Ind, W, H)]
#   time Ind  W   H
# 1:    1   1 56 151
# 2:    2   1 57 154
# 3:    3   1 56  NA
# 4:    4   1 56 153
# 5:    5   1 55 152
# 6:    1   2 76 163
# 7:    2   2 75 164
# 8:    3   2 76 165
# 9:    4   2 73 162
#10:    5   2 77 161
#11:    1   3 87 173
#12:    2   3 88  NA
#13:    3   3 87  NA
#14:    4   3 85 172
#15:    5   3 84 171
#16:    1   4 64 153
#17:    2   4 66 154
#18:    3   4 65 152
#19:    4   4 63 154
#20:    5   4 65 154

```
You can use `melt` function from `data.table` and then `cbind`-

```
setDT(df)
df <- cbind(setnames(melt(df)[grep("^H_",variable),],"value","H"),
            setnames(melt(df)[grep("^W_",variable),],"value","W"))
df <- df[,Ind:=gsub(".*_","",data)] ##cleaning Ind_
df <- df[, time:=1:.N, by = .(Ind)]
df <- df[,.(time,W,H,Ind)]

```

Output-

```
> df
    time  W   H Ind
 1:    1 56 151   1
 2:    1 76 163   2
 3:    1 87 173   3
 4:    1 64 153   4
 5:    2 57 154   1
 6:    2 75 164   2
 7:    2 88  NA   3
 8:    2 66 154   4
 9:    3 56  NA   1
10:    3 76 165   2
11:    3 87  NA   3
12:    3 65 152   4
13:    4 56 153   1
14:    4 73 162   2
15:    4 85 172   3
16:    4 63 154   4
17:    5 55 152   1
18:    5 77 161   2
19:    5 84 171   3
20:    5 65 154   4

```