Laravel Eloquent join vs with












1















I see that join is (by default inner join) and its returning all columns but it takes almost the same time as with keyword for just 1000 data.



$user->join(‘profiles’, ‘users.id’, ‘=’, ‘profiles.user_id’) - generates the below query.



select * from `users` inner join `profiles` on `users`.`id` = `profiles`.`user_id` where `first_name` LIKE '%a%'`


User::with(‘profile’) - this eager loading outputs the below query



select * from `users` where exists (select * from `profiles` where `users`.`id` = `profiles`.`user_id` and `first_name` LIKE '%a%')


which is the best way to return a list of users with a pagination for a REST API ? eager loading seems promising but its with a sub query.



if do with eager loading, this is how i will be filtering. need to use whereHas



if($request->filled('first_name')){
$query->whereHas('profile',function($q) use ($request){
$q->where('first_name','like','%'.request('first_name').'%');
});
}


but if used Join, its less lines of code.



  if ($request->filled('first_name')) {
$users = $users->where('first_name', 'LIKE', "%$request->first_name%");
}


laravel version is 5.7










share|improve this question


















  • 1





    Hi Dev1234 usually with this I will go with what is faster and that usually comes down to how much I need to manipulate the data with PHP. Neither way is wrong but it is best to use the quickest more efficient way as once you get lots of data it can take to long to process

    – Josh
    Nov 20 '18 at 2:14






  • 1





    Ensure profiles.user_id is either the first part of an index or primary key.

    – danblack
    Nov 20 '18 at 2:34
















1















I see that join is (by default inner join) and its returning all columns but it takes almost the same time as with keyword for just 1000 data.



$user->join(‘profiles’, ‘users.id’, ‘=’, ‘profiles.user_id’) - generates the below query.



select * from `users` inner join `profiles` on `users`.`id` = `profiles`.`user_id` where `first_name` LIKE '%a%'`


User::with(‘profile’) - this eager loading outputs the below query



select * from `users` where exists (select * from `profiles` where `users`.`id` = `profiles`.`user_id` and `first_name` LIKE '%a%')


which is the best way to return a list of users with a pagination for a REST API ? eager loading seems promising but its with a sub query.



if do with eager loading, this is how i will be filtering. need to use whereHas



if($request->filled('first_name')){
$query->whereHas('profile',function($q) use ($request){
$q->where('first_name','like','%'.request('first_name').'%');
});
}


but if used Join, its less lines of code.



  if ($request->filled('first_name')) {
$users = $users->where('first_name', 'LIKE', "%$request->first_name%");
}


laravel version is 5.7










share|improve this question


















  • 1





    Hi Dev1234 usually with this I will go with what is faster and that usually comes down to how much I need to manipulate the data with PHP. Neither way is wrong but it is best to use the quickest more efficient way as once you get lots of data it can take to long to process

    – Josh
    Nov 20 '18 at 2:14






  • 1





    Ensure profiles.user_id is either the first part of an index or primary key.

    – danblack
    Nov 20 '18 at 2:34














1












1








1








I see that join is (by default inner join) and its returning all columns but it takes almost the same time as with keyword for just 1000 data.



$user->join(‘profiles’, ‘users.id’, ‘=’, ‘profiles.user_id’) - generates the below query.



select * from `users` inner join `profiles` on `users`.`id` = `profiles`.`user_id` where `first_name` LIKE '%a%'`


User::with(‘profile’) - this eager loading outputs the below query



select * from `users` where exists (select * from `profiles` where `users`.`id` = `profiles`.`user_id` and `first_name` LIKE '%a%')


which is the best way to return a list of users with a pagination for a REST API ? eager loading seems promising but its with a sub query.



if do with eager loading, this is how i will be filtering. need to use whereHas



if($request->filled('first_name')){
$query->whereHas('profile',function($q) use ($request){
$q->where('first_name','like','%'.request('first_name').'%');
});
}


but if used Join, its less lines of code.



  if ($request->filled('first_name')) {
$users = $users->where('first_name', 'LIKE', "%$request->first_name%");
}


laravel version is 5.7










share|improve this question














I see that join is (by default inner join) and its returning all columns but it takes almost the same time as with keyword for just 1000 data.



$user->join(‘profiles’, ‘users.id’, ‘=’, ‘profiles.user_id’) - generates the below query.



select * from `users` inner join `profiles` on `users`.`id` = `profiles`.`user_id` where `first_name` LIKE '%a%'`


User::with(‘profile’) - this eager loading outputs the below query



select * from `users` where exists (select * from `profiles` where `users`.`id` = `profiles`.`user_id` and `first_name` LIKE '%a%')


which is the best way to return a list of users with a pagination for a REST API ? eager loading seems promising but its with a sub query.



if do with eager loading, this is how i will be filtering. need to use whereHas



if($request->filled('first_name')){
$query->whereHas('profile',function($q) use ($request){
$q->where('first_name','like','%'.request('first_name').'%');
});
}


but if used Join, its less lines of code.



  if ($request->filled('first_name')) {
$users = $users->where('first_name', 'LIKE', "%$request->first_name%");
}


laravel version is 5.7







php mysql laravel laravel-5 eloquent






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 20 '18 at 2:09









dev1234dev1234

2,42783686




2,42783686








  • 1





    Hi Dev1234 usually with this I will go with what is faster and that usually comes down to how much I need to manipulate the data with PHP. Neither way is wrong but it is best to use the quickest more efficient way as once you get lots of data it can take to long to process

    – Josh
    Nov 20 '18 at 2:14






  • 1





    Ensure profiles.user_id is either the first part of an index or primary key.

    – danblack
    Nov 20 '18 at 2:34














  • 1





    Hi Dev1234 usually with this I will go with what is faster and that usually comes down to how much I need to manipulate the data with PHP. Neither way is wrong but it is best to use the quickest more efficient way as once you get lots of data it can take to long to process

    – Josh
    Nov 20 '18 at 2:14






  • 1





    Ensure profiles.user_id is either the first part of an index or primary key.

    – danblack
    Nov 20 '18 at 2:34








1




1





Hi Dev1234 usually with this I will go with what is faster and that usually comes down to how much I need to manipulate the data with PHP. Neither way is wrong but it is best to use the quickest more efficient way as once you get lots of data it can take to long to process

– Josh
Nov 20 '18 at 2:14





Hi Dev1234 usually with this I will go with what is faster and that usually comes down to how much I need to manipulate the data with PHP. Neither way is wrong but it is best to use the quickest more efficient way as once you get lots of data it can take to long to process

– Josh
Nov 20 '18 at 2:14




1




1





Ensure profiles.user_id is either the first part of an index or primary key.

– danblack
Nov 20 '18 at 2:34





Ensure profiles.user_id is either the first part of an index or primary key.

– danblack
Nov 20 '18 at 2:34












1 Answer
1






active

oldest

votes


















2














Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses. It is a good solution to use when you process a single entity in a CRUD manner - that is, read from database or create a new entity and then save it or delete. You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alert or update statistics counter when someone has created a new account), traits (timestamps, soft deletes, custom traits) eager/lazy loading etc.



But, as you already know, it comes with some performance price. When you process a single or just a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain DB is better approach.



For our application we are doing exactly that - using Laravel's Eloquent in web forms to process a single record and using DB (with SQL views) to retrieve data for grids, export etc.



When it comes to performance and the application grows, for the sake of comparison, take a loot at the following tables:



Comparison of select operation average response time between Eloquent ORM and Raw SQL



Eloquent ORM average response time



Joins | Average (ms) 

1 | 162,2
3 | 1002,7
4 | 1540,0


Result of select operation average response time for Eloquent ORM



Raw SQL average response time



Joins | Average (ms)
1 | 116,4
3 | 130,6
4 | 155,2


Result of select operation average response time for Raw SQL






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%2f53385230%2flaravel-eloquent-join-vs-with%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses. It is a good solution to use when you process a single entity in a CRUD manner - that is, read from database or create a new entity and then save it or delete. You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alert or update statistics counter when someone has created a new account), traits (timestamps, soft deletes, custom traits) eager/lazy loading etc.



    But, as you already know, it comes with some performance price. When you process a single or just a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain DB is better approach.



    For our application we are doing exactly that - using Laravel's Eloquent in web forms to process a single record and using DB (with SQL views) to retrieve data for grids, export etc.



    When it comes to performance and the application grows, for the sake of comparison, take a loot at the following tables:



    Comparison of select operation average response time between Eloquent ORM and Raw SQL



    Eloquent ORM average response time



    Joins | Average (ms) 

    1 | 162,2
    3 | 1002,7
    4 | 1540,0


    Result of select operation average response time for Eloquent ORM



    Raw SQL average response time



    Joins | Average (ms)
    1 | 116,4
    3 | 130,6
    4 | 155,2


    Result of select operation average response time for Raw SQL






    share|improve this answer




























      2














      Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses. It is a good solution to use when you process a single entity in a CRUD manner - that is, read from database or create a new entity and then save it or delete. You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alert or update statistics counter when someone has created a new account), traits (timestamps, soft deletes, custom traits) eager/lazy loading etc.



      But, as you already know, it comes with some performance price. When you process a single or just a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain DB is better approach.



      For our application we are doing exactly that - using Laravel's Eloquent in web forms to process a single record and using DB (with SQL views) to retrieve data for grids, export etc.



      When it comes to performance and the application grows, for the sake of comparison, take a loot at the following tables:



      Comparison of select operation average response time between Eloquent ORM and Raw SQL



      Eloquent ORM average response time



      Joins | Average (ms) 

      1 | 162,2
      3 | 1002,7
      4 | 1540,0


      Result of select operation average response time for Eloquent ORM



      Raw SQL average response time



      Joins | Average (ms)
      1 | 116,4
      3 | 130,6
      4 | 155,2


      Result of select operation average response time for Raw SQL






      share|improve this answer


























        2












        2








        2







        Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses. It is a good solution to use when you process a single entity in a CRUD manner - that is, read from database or create a new entity and then save it or delete. You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alert or update statistics counter when someone has created a new account), traits (timestamps, soft deletes, custom traits) eager/lazy loading etc.



        But, as you already know, it comes with some performance price. When you process a single or just a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain DB is better approach.



        For our application we are doing exactly that - using Laravel's Eloquent in web forms to process a single record and using DB (with SQL views) to retrieve data for grids, export etc.



        When it comes to performance and the application grows, for the sake of comparison, take a loot at the following tables:



        Comparison of select operation average response time between Eloquent ORM and Raw SQL



        Eloquent ORM average response time



        Joins | Average (ms) 

        1 | 162,2
        3 | 1002,7
        4 | 1540,0


        Result of select operation average response time for Eloquent ORM



        Raw SQL average response time



        Joins | Average (ms)
        1 | 116,4
        3 | 130,6
        4 | 155,2


        Result of select operation average response time for Raw SQL






        share|improve this answer













        Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses. It is a good solution to use when you process a single entity in a CRUD manner - that is, read from database or create a new entity and then save it or delete. You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alert or update statistics counter when someone has created a new account), traits (timestamps, soft deletes, custom traits) eager/lazy loading etc.



        But, as you already know, it comes with some performance price. When you process a single or just a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain DB is better approach.



        For our application we are doing exactly that - using Laravel's Eloquent in web forms to process a single record and using DB (with SQL views) to retrieve data for grids, export etc.



        When it comes to performance and the application grows, for the sake of comparison, take a loot at the following tables:



        Comparison of select operation average response time between Eloquent ORM and Raw SQL



        Eloquent ORM average response time



        Joins | Average (ms) 

        1 | 162,2
        3 | 1002,7
        4 | 1540,0


        Result of select operation average response time for Eloquent ORM



        Raw SQL average response time



        Joins | Average (ms)
        1 | 116,4
        3 | 130,6
        4 | 155,2


        Result of select operation average response time for Raw SQL







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 20 '18 at 7:53









        Web ArtisanWeb Artisan

        1,28531223




        1,28531223
































            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%2f53385230%2flaravel-eloquent-join-vs-with%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







            Popular posts from this blog

            Guess what letter conforming each word

            Port of Spain

            Run scheduled task as local user group (not BUILTIN)