Laravel (5.7) Eloquent Many to Many with query on both sides












2















Say I have a users, roles, and a pivot table. I have belongsToMany set up for both Role and User.



User Model:



<?php

namespace App;

...
use AppModelsRole;

class User extends Authenticatable
{
...
public function roles()
{
return $this->belongsToMany(Role::class, "UserRoles", "userId", "roleId")
->withPivot("read", "write", "update", "delete")
->withTimestamps();
}
}


Role Model:



<?php

namespace AppModels;

...
use AppUser;

class Role extends Model
{
...
public function users()
{
return $this->belongsToMany(User::class, "UserRoles", "roleId", "userId")
->withPivot("read", "write", "update", "delete")
->withTimestamps();
}

}


Table "Users":



userId username
1 admin
2 johndoe
3 menghour


Table "Roles":



roleId roleName
1 Admin
2 HR
3 Account


Pivot Table "UserRoles":



id     userId   roleId   read   write   update   delete
1 1 1 1 1 1 1
2 1 2 1 1 1 1
3 1 3 1 1 1 1
4 2 2 1 0 0 0
5 3 3 1 1 1 0


My question is how I can get filter in pivot table.



For example: I want to filter only userId or roleId or both userId and roleId



if I select user = admin in blade template, I want to get all roles that user have.
expected result:



username    roleName

admin Admin
admin HR
admin Account


if I select option role = HR in blade template,, I want to get all user that role have.
expected result:



username    roleName

admin HR
johndoe HR


if I select option user = admin and role = HR, I want to get only specific user and role.
expected result:



username    roleName

admin HR


I've tired:



User::whereHas("roles", function ($query) use ($request) {
if ($request->userId) {
$query->where("userRoles.userId", "=", $request->userId);
}
if ($request->roleId) {
$query->where("userRoles.roleId", "=", $request->roleId);
}
})->get();


note: I'm using Laravel 5.7










share|improve this question



























    2















    Say I have a users, roles, and a pivot table. I have belongsToMany set up for both Role and User.



    User Model:



    <?php

    namespace App;

    ...
    use AppModelsRole;

    class User extends Authenticatable
    {
    ...
    public function roles()
    {
    return $this->belongsToMany(Role::class, "UserRoles", "userId", "roleId")
    ->withPivot("read", "write", "update", "delete")
    ->withTimestamps();
    }
    }


    Role Model:



    <?php

    namespace AppModels;

    ...
    use AppUser;

    class Role extends Model
    {
    ...
    public function users()
    {
    return $this->belongsToMany(User::class, "UserRoles", "roleId", "userId")
    ->withPivot("read", "write", "update", "delete")
    ->withTimestamps();
    }

    }


    Table "Users":



    userId username
    1 admin
    2 johndoe
    3 menghour


    Table "Roles":



    roleId roleName
    1 Admin
    2 HR
    3 Account


    Pivot Table "UserRoles":



    id     userId   roleId   read   write   update   delete
    1 1 1 1 1 1 1
    2 1 2 1 1 1 1
    3 1 3 1 1 1 1
    4 2 2 1 0 0 0
    5 3 3 1 1 1 0


    My question is how I can get filter in pivot table.



    For example: I want to filter only userId or roleId or both userId and roleId



    if I select user = admin in blade template, I want to get all roles that user have.
    expected result:



    username    roleName

    admin Admin
    admin HR
    admin Account


    if I select option role = HR in blade template,, I want to get all user that role have.
    expected result:



    username    roleName

    admin HR
    johndoe HR


    if I select option user = admin and role = HR, I want to get only specific user and role.
    expected result:



    username    roleName

    admin HR


    I've tired:



    User::whereHas("roles", function ($query) use ($request) {
    if ($request->userId) {
    $query->where("userRoles.userId", "=", $request->userId);
    }
    if ($request->roleId) {
    $query->where("userRoles.roleId", "=", $request->roleId);
    }
    })->get();


    note: I'm using Laravel 5.7










    share|improve this question

























      2












      2








      2


      0






      Say I have a users, roles, and a pivot table. I have belongsToMany set up for both Role and User.



      User Model:



      <?php

      namespace App;

      ...
      use AppModelsRole;

      class User extends Authenticatable
      {
      ...
      public function roles()
      {
      return $this->belongsToMany(Role::class, "UserRoles", "userId", "roleId")
      ->withPivot("read", "write", "update", "delete")
      ->withTimestamps();
      }
      }


      Role Model:



      <?php

      namespace AppModels;

      ...
      use AppUser;

      class Role extends Model
      {
      ...
      public function users()
      {
      return $this->belongsToMany(User::class, "UserRoles", "roleId", "userId")
      ->withPivot("read", "write", "update", "delete")
      ->withTimestamps();
      }

      }


      Table "Users":



      userId username
      1 admin
      2 johndoe
      3 menghour


      Table "Roles":



      roleId roleName
      1 Admin
      2 HR
      3 Account


      Pivot Table "UserRoles":



      id     userId   roleId   read   write   update   delete
      1 1 1 1 1 1 1
      2 1 2 1 1 1 1
      3 1 3 1 1 1 1
      4 2 2 1 0 0 0
      5 3 3 1 1 1 0


      My question is how I can get filter in pivot table.



      For example: I want to filter only userId or roleId or both userId and roleId



      if I select user = admin in blade template, I want to get all roles that user have.
      expected result:



      username    roleName

      admin Admin
      admin HR
      admin Account


      if I select option role = HR in blade template,, I want to get all user that role have.
      expected result:



      username    roleName

      admin HR
      johndoe HR


      if I select option user = admin and role = HR, I want to get only specific user and role.
      expected result:



      username    roleName

      admin HR


      I've tired:



      User::whereHas("roles", function ($query) use ($request) {
      if ($request->userId) {
      $query->where("userRoles.userId", "=", $request->userId);
      }
      if ($request->roleId) {
      $query->where("userRoles.roleId", "=", $request->roleId);
      }
      })->get();


      note: I'm using Laravel 5.7










      share|improve this question














      Say I have a users, roles, and a pivot table. I have belongsToMany set up for both Role and User.



      User Model:



      <?php

      namespace App;

      ...
      use AppModelsRole;

      class User extends Authenticatable
      {
      ...
      public function roles()
      {
      return $this->belongsToMany(Role::class, "UserRoles", "userId", "roleId")
      ->withPivot("read", "write", "update", "delete")
      ->withTimestamps();
      }
      }


      Role Model:



      <?php

      namespace AppModels;

      ...
      use AppUser;

      class Role extends Model
      {
      ...
      public function users()
      {
      return $this->belongsToMany(User::class, "UserRoles", "roleId", "userId")
      ->withPivot("read", "write", "update", "delete")
      ->withTimestamps();
      }

      }


      Table "Users":



      userId username
      1 admin
      2 johndoe
      3 menghour


      Table "Roles":



      roleId roleName
      1 Admin
      2 HR
      3 Account


      Pivot Table "UserRoles":



      id     userId   roleId   read   write   update   delete
      1 1 1 1 1 1 1
      2 1 2 1 1 1 1
      3 1 3 1 1 1 1
      4 2 2 1 0 0 0
      5 3 3 1 1 1 0


      My question is how I can get filter in pivot table.



      For example: I want to filter only userId or roleId or both userId and roleId



      if I select user = admin in blade template, I want to get all roles that user have.
      expected result:



      username    roleName

      admin Admin
      admin HR
      admin Account


      if I select option role = HR in blade template,, I want to get all user that role have.
      expected result:



      username    roleName

      admin HR
      johndoe HR


      if I select option user = admin and role = HR, I want to get only specific user and role.
      expected result:



      username    roleName

      admin HR


      I've tired:



      User::whereHas("roles", function ($query) use ($request) {
      if ($request->userId) {
      $query->where("userRoles.userId", "=", $request->userId);
      }
      if ($request->roleId) {
      $query->where("userRoles.roleId", "=", $request->roleId);
      }
      })->get();


      note: I'm using Laravel 5.7







      php laravel eloquent many-to-many relationship






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 17 '18 at 16:31









      Menghour SayMenghour Say

      133




      133
























          2 Answers
          2






          active

          oldest

          votes


















          3














          You could try this query to filter data as per your example.



          The function whereHas is used to retrieve all the users with a given role. To get the roles of user you have to use with function.



          $user = User::whereHas('roles', function ($query) use ($request) {
          if ($request->roleId) {
          $query->where("userRoles.roleId", "=", $request->roleId);
          }
          })
          // It will give you the user's role data.
          ->with('roles');

          // It will check in users table if you have applied the filter by user.
          if ($request->userId) {
          $user = $user->where("users.id", "=", $request->userId);
          }

          $user = $user->get();


          You could also try this query to filter data as per your example. This query will give you the user with particular that role which has you given in roles object.
          For Example: if you select option role = HR in blade template.



          $user = User::whereHas('roles', function ($query) use ($request) {
          if ($request->roleId) {
          $query->where("userRoles.roleId", "=", $request->roleId);
          }
          })
          // It will give you the user data with a particular given role which you has passed in the request.
          ->with(['roles' => function($query) use($request) {
          if ($request->roleId) {
          $query->where("roles.id", "=", $request->roleId);
          }
          }]);

          // It will check in users table if you have applied the filter by user.
          if ($request->userId) {
          $user = $user->where("users.id", "=", $request->userId);
          }

          $user = $user->get();





          share|improve this answer
























          • You saved my life. THANKS very much.

            – Menghour Say
            Nov 19 '18 at 16:14



















          0














          You could try the wherePivot() or wherePivotIn() methods:



          User::roles()->wherePivot('roleId',$request->roleId)
          ->wherePivot('userId',$request->userId)
          ->get()


          https://laravel.com/docs/5.7/eloquent-relationships#many-to-many






          share|improve this answer























            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53353182%2flaravel-5-7-eloquent-many-to-many-with-query-on-both-sides%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            3














            You could try this query to filter data as per your example.



            The function whereHas is used to retrieve all the users with a given role. To get the roles of user you have to use with function.



            $user = User::whereHas('roles', function ($query) use ($request) {
            if ($request->roleId) {
            $query->where("userRoles.roleId", "=", $request->roleId);
            }
            })
            // It will give you the user's role data.
            ->with('roles');

            // It will check in users table if you have applied the filter by user.
            if ($request->userId) {
            $user = $user->where("users.id", "=", $request->userId);
            }

            $user = $user->get();


            You could also try this query to filter data as per your example. This query will give you the user with particular that role which has you given in roles object.
            For Example: if you select option role = HR in blade template.



            $user = User::whereHas('roles', function ($query) use ($request) {
            if ($request->roleId) {
            $query->where("userRoles.roleId", "=", $request->roleId);
            }
            })
            // It will give you the user data with a particular given role which you has passed in the request.
            ->with(['roles' => function($query) use($request) {
            if ($request->roleId) {
            $query->where("roles.id", "=", $request->roleId);
            }
            }]);

            // It will check in users table if you have applied the filter by user.
            if ($request->userId) {
            $user = $user->where("users.id", "=", $request->userId);
            }

            $user = $user->get();





            share|improve this answer
























            • You saved my life. THANKS very much.

              – Menghour Say
              Nov 19 '18 at 16:14
















            3














            You could try this query to filter data as per your example.



            The function whereHas is used to retrieve all the users with a given role. To get the roles of user you have to use with function.



            $user = User::whereHas('roles', function ($query) use ($request) {
            if ($request->roleId) {
            $query->where("userRoles.roleId", "=", $request->roleId);
            }
            })
            // It will give you the user's role data.
            ->with('roles');

            // It will check in users table if you have applied the filter by user.
            if ($request->userId) {
            $user = $user->where("users.id", "=", $request->userId);
            }

            $user = $user->get();


            You could also try this query to filter data as per your example. This query will give you the user with particular that role which has you given in roles object.
            For Example: if you select option role = HR in blade template.



            $user = User::whereHas('roles', function ($query) use ($request) {
            if ($request->roleId) {
            $query->where("userRoles.roleId", "=", $request->roleId);
            }
            })
            // It will give you the user data with a particular given role which you has passed in the request.
            ->with(['roles' => function($query) use($request) {
            if ($request->roleId) {
            $query->where("roles.id", "=", $request->roleId);
            }
            }]);

            // It will check in users table if you have applied the filter by user.
            if ($request->userId) {
            $user = $user->where("users.id", "=", $request->userId);
            }

            $user = $user->get();





            share|improve this answer
























            • You saved my life. THANKS very much.

              – Menghour Say
              Nov 19 '18 at 16:14














            3












            3








            3







            You could try this query to filter data as per your example.



            The function whereHas is used to retrieve all the users with a given role. To get the roles of user you have to use with function.



            $user = User::whereHas('roles', function ($query) use ($request) {
            if ($request->roleId) {
            $query->where("userRoles.roleId", "=", $request->roleId);
            }
            })
            // It will give you the user's role data.
            ->with('roles');

            // It will check in users table if you have applied the filter by user.
            if ($request->userId) {
            $user = $user->where("users.id", "=", $request->userId);
            }

            $user = $user->get();


            You could also try this query to filter data as per your example. This query will give you the user with particular that role which has you given in roles object.
            For Example: if you select option role = HR in blade template.



            $user = User::whereHas('roles', function ($query) use ($request) {
            if ($request->roleId) {
            $query->where("userRoles.roleId", "=", $request->roleId);
            }
            })
            // It will give you the user data with a particular given role which you has passed in the request.
            ->with(['roles' => function($query) use($request) {
            if ($request->roleId) {
            $query->where("roles.id", "=", $request->roleId);
            }
            }]);

            // It will check in users table if you have applied the filter by user.
            if ($request->userId) {
            $user = $user->where("users.id", "=", $request->userId);
            }

            $user = $user->get();





            share|improve this answer













            You could try this query to filter data as per your example.



            The function whereHas is used to retrieve all the users with a given role. To get the roles of user you have to use with function.



            $user = User::whereHas('roles', function ($query) use ($request) {
            if ($request->roleId) {
            $query->where("userRoles.roleId", "=", $request->roleId);
            }
            })
            // It will give you the user's role data.
            ->with('roles');

            // It will check in users table if you have applied the filter by user.
            if ($request->userId) {
            $user = $user->where("users.id", "=", $request->userId);
            }

            $user = $user->get();


            You could also try this query to filter data as per your example. This query will give you the user with particular that role which has you given in roles object.
            For Example: if you select option role = HR in blade template.



            $user = User::whereHas('roles', function ($query) use ($request) {
            if ($request->roleId) {
            $query->where("userRoles.roleId", "=", $request->roleId);
            }
            })
            // It will give you the user data with a particular given role which you has passed in the request.
            ->with(['roles' => function($query) use($request) {
            if ($request->roleId) {
            $query->where("roles.id", "=", $request->roleId);
            }
            }]);

            // It will check in users table if you have applied the filter by user.
            if ($request->userId) {
            $user = $user->where("users.id", "=", $request->userId);
            }

            $user = $user->get();






            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 18 '18 at 7:40









            Vandit P. KotadiyaVandit P. Kotadiya

            862




            862













            • You saved my life. THANKS very much.

              – Menghour Say
              Nov 19 '18 at 16:14



















            • You saved my life. THANKS very much.

              – Menghour Say
              Nov 19 '18 at 16:14

















            You saved my life. THANKS very much.

            – Menghour Say
            Nov 19 '18 at 16:14





            You saved my life. THANKS very much.

            – Menghour Say
            Nov 19 '18 at 16:14













            0














            You could try the wherePivot() or wherePivotIn() methods:



            User::roles()->wherePivot('roleId',$request->roleId)
            ->wherePivot('userId',$request->userId)
            ->get()


            https://laravel.com/docs/5.7/eloquent-relationships#many-to-many






            share|improve this answer




























              0














              You could try the wherePivot() or wherePivotIn() methods:



              User::roles()->wherePivot('roleId',$request->roleId)
              ->wherePivot('userId',$request->userId)
              ->get()


              https://laravel.com/docs/5.7/eloquent-relationships#many-to-many






              share|improve this answer


























                0












                0








                0







                You could try the wherePivot() or wherePivotIn() methods:



                User::roles()->wherePivot('roleId',$request->roleId)
                ->wherePivot('userId',$request->userId)
                ->get()


                https://laravel.com/docs/5.7/eloquent-relationships#many-to-many






                share|improve this answer













                You could try the wherePivot() or wherePivotIn() methods:



                User::roles()->wherePivot('roleId',$request->roleId)
                ->wherePivot('userId',$request->userId)
                ->get()


                https://laravel.com/docs/5.7/eloquent-relationships#many-to-many







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 17 '18 at 19:35









                PeterPeter

                8641213




                8641213






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53353182%2flaravel-5-7-eloquent-many-to-many-with-query-on-both-sides%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    這個網誌中的熱門文章

                    Xamarin.form Move up view when keyboard appear

                    Post-Redirect-Get with Spring WebFlux and Thymeleaf

                    Anylogic : not able to use stopDelay()