SQL To Query Builder

$users = DB::table('requisitions')
    ->join('user_can_approve', function ($join) {
        $join
            ->on('requisitions.type', '=', 'user_can_approve.requisition_type')
            ->where('requisitions.level', '=', 'user_can_approve.requisition_level');
    })
    ->join('users', 'users.id', '=', 'user_can_approve.user_id')
    ->where('users.id', '=', $userId)
    ->select('requisitions.id')
    ->get();

Please let me know if worked;

HI HENRIQUESALVAN,

I tried your solution but it is not working .. it returns an empty matrix even though there is data in the database correctly

Can you use the examples in the documentation and the above example and work it out.

There are quite a few examples in the query building section of the docs.

Perhaps change function ($join) { to function ($query) {.

{ "id": 1, "name": "asdasdasd", "date": null, "level": null, "type": null, "status": null, "created_at": null, "updated_at": null, "user_id": 1, "requisition_type": 1, "requisition_level": 1, "email": "[email protected]", "email_verified_at": null, "password": "asdasdasdasd", "remember_token": null }, { "id": 1, "name": "asdasdasd", "date": null, "level": null, "type": null, "status": null, "created_at": null, "updated_at": null, "user_id": 1, "requisition_type": 2, "requisition_level": 1, "email": "[email protected]", "email_verified_at": null, "password": "asdasdasdasd", "remember_token": null } ]

$users = DB::table('requisitions','s')

        ->rightJoin('user_can_approves', function ($query) {
            $query
                ->on('requisitions.type', '=', 'user_can_approves.requisition_type')
                ->where('requisitions.level', '=', 'user_can_approves.requisition_level');
        })
        ->rightJoin('users', 'users.id', '=', 'user_can_approves.user_id')
        ->where('users.id', '=', 1)
        ->get();

@ibrahimks worked the way you put above?

@ibrahimks It seems weird that the query you posted in the beginning works and the query by @henriquesalvan does not. AS far as I can tell it is absolutely the same (maybe except he wrote $userId instead of $currentUsersID). You can change ->get() to ->toSql() to ensure that it is indeed the same :)

Are you certain that your own query works?

@sinnbeck I have implemented the same code and cannot be returned,

Collection {#292 ▼ #items: [] }

First it all, you really need the fields of user on your query? If not, your query can be like this:

$userId = 1;

$requisitions = DB::table('requisitions')
    ->join('user_can_approve', function ($join) use ($userId) {
        $join
            ->on(
                'user_can_approve.requisition_type', '=', 'requisitions.type'
            )
            ->where(
                'user_can_approve.requisition_level', '=', 'requisitions.level'
            )
            ->where(
                'user_can_approve.user_id', '=', $userId
            );
    })
    ->select('requisitions.id')
    #->select('requisitions.*')
    ->get();

So, with this SQL you will have a list of all requisitions that can be approved by the user 1.