Transform Brute Inner Join to Laravel Join

Question

I’m updating a system that is developed in pure PHP and I’m putting it in Laravel, however, I’ve never made more than a Join and with "AND" parameters so I would like you to help me, how can I put this SELECT in the Laravel controller?

<?php 
  $users = "SELECT * FROM ma_usuario u, ma_user_tipo t, ma_user_tipo_rel r 
            WHERE ma_user_tipo.tp_usr_id=ma_user_tipo_rel.tp_usr_id 
            AND ma_usuario.usr_id=ma_user_tipo_rel.usr_id 
            AND ma_usuario.usr_status='Ativo' 
            AND r.tp_usr_id='$id' 
            ORDER BY u.usr_id";
 ?>

As are several parameters I had a knot in the brain and did not understand how to assemble.

Answer 1

Following the documentation (https://laravel.com/docs/5.5/queries#joins) you can do your query this way:

DB::table('ma_usuario')
    ->join('ma_user_tipo_rel' 'ma_usuario.usr_id', '=', 'ma_user_tipo_rel.usr_id')
    ->where([
        ['ma_usuario.usr_status', '=', 'Ativo'],
        ['ma_user_tipo_rel.tp_usr_id', '=', $id],
    ])
    ->orderBy('ma_usuario.usr_id');