Laravel 6 API RESTful with many to many relationship filtered

Question

I'm developing a Multilanguage API with laravel 6 and I've got this situation in my database:

Categories
id
other not relevants fields

Languages:
id
name
code

Category_Language
id
language_id
category_id
name --> this is the name of the category in the specific language.

Now I've 2 models, the first for Category

class Category extends Model
{    
    public function languages()
    {
        return $this->belongsToMany('App\Models\v1\Language')->withTimestamps()->withPivot('name');
    }
}

and the second Model

class Language extends Model
{
    protected $fillable = ['code', 'name', 'image_id', 'enabled'];

    public function categories() {
        return $this->belongsToMany('App\Models\v1\Category')->withTimestamps()->withPivot('name');
    }
}

In my logic (I'm using Service Pattern) I've used this approach on create Category, every time I Pass a JSON object like this:

{
  "names": [
    {
      "languageId": 1,
      "name": "Hello"
    },
    {
      "languageId": 2,
      "name": "Hola"
    }
  ]
}

First of all I create a category (validate if the id of the language is really stored in database) and then with many to many laravel power I attach the language and the name in the category model like this:

 foreach($request->names as $name) {
    $category->languages()->attach($name['languageId'], ['name' => $name['name']]);
 }

Now this seems to work perfectly and it's good to retrieve all the category without filtering languages using API Resource like this:

public function toArray($request)
{
    $category = [];
    $category['id'] = $this->id;
    $category['languages'] = [];

    $category['languages'] = $this->languages->map(function ($language) {
        return [
            'languageId' => $language->id,
            'languageCode' => $language->code,
            'languageName' => $language->name,
            'categoryName' => $language->pivot->name,
        ];
    });

    return $category;
}

and this is the output for 3 categories with 3 languages (faker data):

array:3 [
  "data" => array:3 [
    0 => array:4 [
      "id" => 1
      "languages" => array:3 [
        0 => array:4 [
          "languageId" => 1
          "languageCode" => "pa"
          "languageName" => "Kacey Trantow"
          "categoryName" => "quasi"
        ]
        1 => array:4 [
          "languageId" => 2
          "languageCode" => "ne"
          "languageName" => "Mr. Alexandre Heathcote"
          "categoryName" => "perferendis"
        ]
        2 => array:4 [
          "languageId" => 3
          "languageCode" => "kj"
          "languageName" => "Mr. Misael Robel"
          "categoryName" => "repudiandae"
        ]
      ]
      "imageUrl" => null
      "enabled" => true
    ]
    1 => array:4 [
      "id" => 2
      "languages" => array:3 [
        0 => array:4 [
          "languageId" => 1
          "languageCode" => "pa"
          "languageName" => "Kacey Trantow"
          "categoryName" => "non"
        ]
        1 => array:4 [
          "languageId" => 2
          "languageCode" => "ne"
          "languageName" => "Mr. Alexandre Heathcote"
          "categoryName" => "vitae"
        ]
        2 => array:4 [
          "languageId" => 3
          "languageCode" => "kj"
          "languageName" => "Mr. Misael Robel"
          "categoryName" => "suscipit"
        ]
      ]
      "imageUrl" => null
      "enabled" => true
    ]
    2 => array:4 [
      "id" => 3
      "languages" => array:3 [
        0 => array:4 [
          "languageId" => 1
          "languageCode" => "pa"
          "languageName" => "Kacey Trantow"
          "categoryName" => "molestiae"
        ]
        1 => array:4 [
          "languageId" => 2
          "languageCode" => "ne"
          "languageName" => "Mr. Alexandre Heathcote"
          "categoryName" => "esse"
        ]
        2 => array:4 [
          "languageId" => 3
          "languageCode" => "kj"
          "languageName" => "Mr. Misael Robel"
          "categoryName" => "beatae"
        ]
      ]
      "imageUrl" => null
      "enabled" => true
    ]
  ]

now, the problem is when I want to FILTER the categories passing ONLY 1 specific Language, a classical use case when a user use ONLY 1 language during navigations so, If I want ONLY 1 language (and 1 name in pivot relations of category) what kind of operation I need?

I've organized the service with specific filters and ordering for any collections but this kind of need seems to make me crazy!

So this is my final getCategories method that build filter, ordering and include dinamically:

-

imaging so to have a query string like this:

http://localhost:8000/api/v1/categories?orderBy=id:asc&include=language&language.code=EN

and I want a response like this:

array:3 [
      "data" => array:3 [
        0 => array:4 [
          "id" => 1
          "languages" => array:3 [
            0 => array:4 [
              "languageId" => 1
              "languageCode" => "EN"
              "languageName" => "English"
              "categoryName" => "quasi"
            ]
          ]
        ]
        1 => array:4 [
          "id" => 2
          "languages" => array:3 [
            0 => array:4 [
              "languageId" => 1
              "languageCode" => "EN"
              "languageName" => "English"
              "categoryName" => "non"
            ]
          ]
        ]
        2 => array:4 [
          "id" => 3
          "languages" => array:3 [
            0 => array:4 [
              "languageId" => 1
              "languageCode" => "EN"
              "languageName" => "English"
              "categoryName" => "molestiae"
            ]
          ]
        ]
      ]

I need something like this but GENERALLY, because we have others entities that use the same logic for multilanguage system.

To clarify what I want is like a join query like this:

SELECT *
FROM categories ca INNER JOIN category_language cl ON ca.id =cl.category_id
                 INNER JOIN languages lan ON lan.id = cl.language_id
WHERE lan.code = 'EN';

You will retrieve a list of categories only in ENGLISH languages...

Thanks for any suggestion and help!

OMR · Answer 1 · 2020-06-01T06:09:37.640

0

you can make join in eloquent like:

$language_code=$request->input('language_code');

    $values=Category::query()->join('category_language','category_language.category_id','=','categories.id')
->join('languages','languages.id','=','category_language.language_id')
->where('languages.code','=',$language_code)
->select('categories.*','languages.code')
    ->orderBy('categories.id')->get();

edited Jun 01 '20 at 06:09

answered May 31 '20 at 20:41

OMR

11,736
5
20
35

It's a little more different, whereHas don't act as filter for langauges in categories array. please see my last edit of the post for better explanation – BobbyLinux May 31 '20 at 21:10

BobbyLinux · Accepted Answer · 2020-05-31T22:02:01.307

I don't think this is exactly the most elegant way of doing this but, I've solved with a little workaround.

First of all, I don't pass language code in query string but, in Header paramas using X-localization params, like explained in this tutorial for laravel with multilanguages.

Second, I always return query->paginate() in service method when retrieve all the resources:

public function getCategories(Request $request)
{
    $sort = $this->buildSort($request->sort ?? '', 'id', 'asc');
    $where = $this->buildWhere($request->where ?? '');
    $includes = $this->buildWith($request->include ?? '');
    $query = Category::orderBy($sort[0], $sort[1]);

    if (!empty($include)) {
        $query = $query->with($includes);
    }

    if (!empty($where)) {
        $query = $query->where($where);
    }

    return $query->paginate();

}

Finally, I intercept the request header into the Category API Resource and if is setted the X-localization param, I filter in the map the languages array like this:

public function toArray($request)
{
    $languageCode = $request->header('X-localization') ?? null;

    $category = [];
    $category['id'] = $this->id;
    $category['languages'] = [];

    $languages = $this->languages;

    if ($languageCode) {
        $languages = $languages->where('code', '=', $languageCode);
    }

    $languages = $languages->map(function ($language) use ($languageCode, $category) {
        return [
            'languageId' => $language->id,
            'languageCode' => $language->code,
            'languageName' => $language->name,
            'categoryName' => $language->pivot->name,
        ];
    });

    $category['languages'] = $languages;


    return $category;

}

The result is exactly what I want without using pure SQL in my project (fake data):

array:3 [
  "data" => array:3 [
    0 => array:4 [
      "id" => 1
      "languages" => array:1 [
        0 => array:4 [
          "languageId" => 1
          "languageCode" => "ho"
          "languageName" => "Dott. Orfeo Sartori"
          "categoryName" => "rem"
        ]
      ]
      "imageUrl" => null
      "enabled" => true
    ]
    1 => array:4 [
      "id" => 2
      "languages" => array:1 [
        0 => array:4 [
          "languageId" => 1
          "languageCode" => "ho"
          "languageName" => "Dott. Orfeo Sartori"
          "categoryName" => "accusamus"
        ]
      ]
      "imageUrl" => null
      "enabled" => true
    ]
    2 => array:4 [
      "id" => 3
      "languages" => array:1 [
        0 => array:4 [
          "languageId" => 1
          "languageCode" => "ho"
          "languageName" => "Dott. Orfeo Sartori"
          "categoryName" => "totam"
        ]
      ]
      "imageUrl" => null
      "enabled" => true
    ]
  ]
  "links" => array:4 [
    "first" => "http://localhost/api/v1/categories?page=1"
    "last" => "http://localhost/api/v1/categories?page=1"
    "prev" => null
    "next" => null
  ]
  "meta" => array:7 [
    "current_page" => 1
    "from" => 1
    "last_page" => 1
    "path" => "http://localhost/api/v1/categories"
    "per_page" => 15
    "to" => 3
    "total" => 3
  ]
]

Process finished with exit code 1

Laravel 6 API RESTful with many to many relationship filtered

2 Answers2