The situation I found is as follows: I have three tables, stores, cities and states.
store columns - id, cities_id, name ...
city columns - id, states_id, name
states columns - id, name, initials
I'm creating an API, and I want my localhost:8000/v1/api/store endpoint to fetch all store records and bring information about cities and states in the same json, I would like the return json to be:
{
"msg":"Resources found.",
"data":{
"id":1,
"totvs_code":"34",
"name":"test",
"cities_id":1400100,
"address":"tested street",
"district":"district",
"zip code":"13348380",
"active":0,
"cities":{
"id":1400100,
"states_id":14,
"name":"Boa Vista",
"States":{
"id":14,
"name":"Sao Paulo",
"initials":"SP"
}
}
}
}
Currently, the result when accessing the get store endpoint is:
{
"msg":"Resources found.",
"date":[
{
"id":1,
"totvs_code":"34",
"name":"test",
"cities_id":1400100,
"address":"testeeee street",
"district":"district",
"zip_code":"13348380",
"active":0,
"cities":{
"id":1400100,
"states_id":14,
"name":"Boa Vista"
}
}
]
}
I have no idea how to create this relationship in the models and call it in the controller, could you help me? Thank you very much.
Model Store
class Store extends Model
{
use HasFactory;
public $timestamps = false;
protected $fillable = ['id', 'totvs_code', 'name', 'cities_id', 'address', 'district', 'zip_code', 'active'];
public function cities(){
return $this->belongsTo('App\Models\Citie'); //PERTENCE A - BelongsTo
}
}
Model Citie
class Citie extends Model
{
use HasFactory;
public $timestamps = false;
protected $fillable = ['id', 'states_id', 'name'];
public function states(){
return $this->belongsTo('App\Models\State', 'states_id');
}
}
Model State
class State extends Model
{
use HasFactory;
public $timestamps = false;
protected $fillable = ['id', 'name', 'initials'];
}
**StoreController - index get all **
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index(Request $request)
{
$storeRepository = new StoreRepository($this->store);
//query está sendo montada
if($request->has('atributos_cidade')) { //selecionar atributos específico de reward_group / relacionamento
$atributos_cidade = 'cities:id,' . $request->atributos_cidade;
$storeRepository->selectRelationshipAttributes($atributos_cidade);
} else {
$storeRepository->selectRelationshipAttributes('cities');
};
//query está sendo montada
if ($request->has('filtro')) {
$storeRepository->filter($request->filtro);
}
//query está sendo montada
if ($request->has('atributos')) {
$storeRepository->selectAttributes($request->atributos);
}
//query montada, get será realizado.
return response()->json([
'msg' => 'Recursos encontrados.',
'data' => $storeRepository->getResults()
], 200);
}
Store Controller - show get especific
/**
* Display the specified resource.
*
* @param \App\Models\Store $store
* @return \Illuminate\Http\Response
*/
public function show($id)
{
$store = $this->store->find($id);
if ($store == null) {
return response()->json([
'msg' => 'Recurso pesquisado não existe.'
],
404);
}
return response()->json([
'msg' => 'Recurso encontrado.',
'data' => $store
], 200);
}
Designer Patterns - Repositories>AbstractRepository
abstract class AbstractRepository{
protected $model;
public function __construct(Model $model) {
$this->model = $model;
}
public function selectRelationshipAttributes($attributes){
$this->model = $this->model->with($attributes);
//a query está sendo montada
}
public function filter($filters){
$filters = explode(';', $filters);
foreach($filters as $key => $condition) {
$c = explode(':', $condition);
//$c[0] - coluna
//$c[1] - condição
//$c[2] - valor
$this->model = $this->model->where($c[0], $c[1], $c[2]);
}
//a query está sendo montada
}
public function selectAttributes($attributes){
$this->model = $this->model->selectRaw($attributes);
//a query está sendo montada
}
//a query foi montada, basta realizar o select no banco.
public function getResults(){
return $this->model->get();
}
}
StoreRepository
<?php
namespace App\Repositories;
class StoreRepository extends AbstractRepository{
}
While waiting for someone's answer, I did a little more in-depth research and found that to do these nesting just type the methods inside "with", separating them with ".", see an example below:
$shop = $this->shop->with('cities.states')->find($id);
That way my return json came exactly as needed:
{
"msg":"Recurso encontrado.",
"data":{
"id":1,
"totvs_code":"34",
"name":"teste",
"cities_id":1400100,
"address":"testeeee rua",
"district":"bairro",
"zip_code":"13348380",
"active":0,
"cities":{
"id":1400100,
"states_id":14,
"name":"Boa Vista",
"states":{
"id":14,
"name":"Roraima",
"initials":"RR"
}
}
}