Clasture where in field type json no (Laravel + Eloquent)

Question

Clasture where in field type json no (Laravel + Eloquent)

Navigation

#1 by (1 votes)

1

The problem is the following, I have to bring from a select the permissions that a profile has, only the permissions are in a json field, and because if it is several permissions we have a data collection, I need to do this within a middleware:

 public function handle($request, Closure $next, $rotaSolicitada){

    try{
        $usuario = JWTAuth::parseToken()->authenticate();
    }catch (\Exception $e){
        return response()->json(['error'=> true, 'mensagem'=> 'Não foi possível autenticar no sistema', 'data'=> null], 500);
    }

    $permissao = Perfil::with('usuario')
                    ->where('id', $usuario->id)
                    ->where('recursos->rota', 'reserva')
                    ->get();

    //aqui é só para exibir o retorno (teste)   
    dump($permissao);
    return response()->json($permissao);



    $recursoSolicitado = str_replace( '@','',strstr($request->route()->getActionName(), '@'));


    return $next($request);
}

My database looks like this:

    public function up()
{
    Schema::create('perfis', function (Blueprint $table) {
        $table->increments('id');
        $table->string('descricao', 45);
        $table->json('recursos')->nullable();
        $table->char('status',1);
        $table->timestamps();
    });
}

Return of the select done right in the bank (select * from profiles)

JsonformatoftheresourcecolumnIwanttofetch:

[{"rota": "reserva",
"sref": "oQueFazer",
"ordem": "1",
"recursos": ["index", "show"],
"descricao": "Reservar",
"controller": "ReservasController"
}, {
    "rota": "reserva",
    "sref": "oQueFazer",
    "ordem": "2",
    "recursos": ["index", "show"],
    "descricao": "Reservas",
    "controller": "ReservasController"
}, {
    "rota": "usuario",
    "sref": "oQueFazer",
    "ordem": "3",
    "recursos": ["index", "show"],
    "descricao": "Usuários",
    "controller": "UsuariosController"
}, {
    "rota": "feriado",
    "sref": "oQueFazer",
    "ordem": "4",
    "recursos": ["index", "show"],
    "descricao": "Feriados",
    "controller": "FeriadosController"
}, {
    "rota": "sala",
    "sref": "home.sala",
    "ordem": "5",
    "recursos": ["index", "show"],
    "descricao": "Salas",
    "controller": "SalasController"
}]

In summary, I want to make a where in this json field, which is named recursos . I would like to display all recursos of json where rota = 'reservas .

I tried this and it did not work:

 $permissao = Perfil::with('usuario')
                    ->where('id', $usuario->id)
                    ->where('recursos->rota', 'reserva')
                    ->get();

sql json laravel laravel-eloquent

asked by anonymous 19.10.2017 / 23:28

1 answer

Passing values between screens [duplicate] What to do after preparing a Model? [closed]

score 1 · Accepted Answer

For an explanation of the site the logic would be with JSON_CONTAINS

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             1 |
+-------------------------------+

and on this other JSON Paths link, explain how to work with wildcard (*) where array for a given search would be $.[*] and the name of the field. The return of the JSON_CONTAINS can be 1 or 0 if the query is contained in the document JSON or NULL if the arguments are null or if the JSON section of the document is not identified.

An example in your code I believe to be:

$permissao = Perfil::with('usuario')
                ->where('id', $usuario->id)
                ->whereRaw('JSON_CONTAINS(recursos, "reserva", $.[*]rota)=1')
                ->get();

Complementing the answer follows the solution:

SELECT JSON_EXTRACT(recursos, '$[0].recursos') permite from perfis
WHERE
JSON_CONTAINS(recursos, '{"controller": "FeriadosController"}');