Is there a way to be explicit on .select() columns when using .includes() in Rails?












3















I am trying to optimize my query for RAM usage and a table that is in the .includes(:ocr) has too many columns on it (and also a large-sized column). Is there a way that I can still utilize Rails' .includes() but pick out explicit .select() columns from the includes() tables?



ie.



User.select(:email).includes(:ocr => select(:small_value_only))









share|improve this question























  • What is the current RAM usage by selecting all columns?

    – Lenin Raj Rajasekaran
    Nov 19 '18 at 22:25











  • 350MB, the column i'm trying to remove is a text column that is sometimes 2-3MB large.

    – Kamilski81
    Dec 3 '18 at 21:52











  • You could batch the records using one of the batch AR methods.

    – Lenin Raj Rajasekaran
    Dec 3 '18 at 22:07











  • Which version of Rails are you using?

    – Old Pro
    Feb 2 at 21:53
















3















I am trying to optimize my query for RAM usage and a table that is in the .includes(:ocr) has too many columns on it (and also a large-sized column). Is there a way that I can still utilize Rails' .includes() but pick out explicit .select() columns from the includes() tables?



ie.



User.select(:email).includes(:ocr => select(:small_value_only))









share|improve this question























  • What is the current RAM usage by selecting all columns?

    – Lenin Raj Rajasekaran
    Nov 19 '18 at 22:25











  • 350MB, the column i'm trying to remove is a text column that is sometimes 2-3MB large.

    – Kamilski81
    Dec 3 '18 at 21:52











  • You could batch the records using one of the batch AR methods.

    – Lenin Raj Rajasekaran
    Dec 3 '18 at 22:07











  • Which version of Rails are you using?

    – Old Pro
    Feb 2 at 21:53














3












3








3








I am trying to optimize my query for RAM usage and a table that is in the .includes(:ocr) has too many columns on it (and also a large-sized column). Is there a way that I can still utilize Rails' .includes() but pick out explicit .select() columns from the includes() tables?



ie.



User.select(:email).includes(:ocr => select(:small_value_only))









share|improve this question














I am trying to optimize my query for RAM usage and a table that is in the .includes(:ocr) has too many columns on it (and also a large-sized column). Is there a way that I can still utilize Rails' .includes() but pick out explicit .select() columns from the includes() tables?



ie.



User.select(:email).includes(:ocr => select(:small_value_only))






ruby-on-rails






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 19 '18 at 19:00









Kamilski81Kamilski81

5,6352080127




5,6352080127













  • What is the current RAM usage by selecting all columns?

    – Lenin Raj Rajasekaran
    Nov 19 '18 at 22:25











  • 350MB, the column i'm trying to remove is a text column that is sometimes 2-3MB large.

    – Kamilski81
    Dec 3 '18 at 21:52











  • You could batch the records using one of the batch AR methods.

    – Lenin Raj Rajasekaran
    Dec 3 '18 at 22:07











  • Which version of Rails are you using?

    – Old Pro
    Feb 2 at 21:53



















  • What is the current RAM usage by selecting all columns?

    – Lenin Raj Rajasekaran
    Nov 19 '18 at 22:25











  • 350MB, the column i'm trying to remove is a text column that is sometimes 2-3MB large.

    – Kamilski81
    Dec 3 '18 at 21:52











  • You could batch the records using one of the batch AR methods.

    – Lenin Raj Rajasekaran
    Dec 3 '18 at 22:07











  • Which version of Rails are you using?

    – Old Pro
    Feb 2 at 21:53

















What is the current RAM usage by selecting all columns?

– Lenin Raj Rajasekaran
Nov 19 '18 at 22:25





What is the current RAM usage by selecting all columns?

– Lenin Raj Rajasekaran
Nov 19 '18 at 22:25













350MB, the column i'm trying to remove is a text column that is sometimes 2-3MB large.

– Kamilski81
Dec 3 '18 at 21:52





350MB, the column i'm trying to remove is a text column that is sometimes 2-3MB large.

– Kamilski81
Dec 3 '18 at 21:52













You could batch the records using one of the batch AR methods.

– Lenin Raj Rajasekaran
Dec 3 '18 at 22:07





You could batch the records using one of the batch AR methods.

– Lenin Raj Rajasekaran
Dec 3 '18 at 22:07













Which version of Rails are you using?

– Old Pro
Feb 2 at 21:53





Which version of Rails are you using?

– Old Pro
Feb 2 at 21:53












3 Answers
3






active

oldest

votes


















4





+25









You don't give much information on how your tables are set, so I'll assume that User belongs_to :ocr, User has an ocr_id column, and the ocr table's name is ocr.



You should use joins instead of includes, that's how you generate an INNER JOIN query and be able to retrieve the joined table's column(s). Finally you can alias the column name with AS, so it's easier to retrieve in your model:



users = User.joins(:ocr).select(:email, :ocr_id, '`ocr`.`small_value_only` AS `ocr_sma`')

users.each do |user|
user.email # Users.email for this record
user.ocr_sma # Joined Ocr.small_value_only for this record
end


(Obviously I aliased it ocr_sma, but you can give it the name you want)






share|improve this answer

































    1














    If you are really only trying to get 2 columns of data, you should use pluck instead of select. Speeding up these kinds of big queries is exactly what pluck was created for. Instead of all of the overhead that goes with creating all the ActiveRecord objects for all your data, pluck just returns an array of data:



    values = User.includes(:ocr).pluck(:email, "ocr.small_value_only")
    # [["email_1", 3], ["email_2", 7]] # 3 and 7 are the small values





    share|improve this answer































      1














      I do not want to say that the following options are necessarily better than what Benj and Old Pro suggest, but rather want to provide some more alternatives.



      The first one makes use of the includes method as that is what OP asked for. But as includes will by default select all columns of the primary model (User), one first has to remove that default selection via the except method:



      User.includes(:ocr).except(:select).select(:id, 'ocr.small_value_only')


      Of course, using join in the first place would be easier.



      The following options stem from my dislike for having strings in AR queries as it tends to rely on knowledge about the called model which AR should actually abstract for you, i.e. the name of the database table (ocr).



      A naive implementation for removing that knowledge is to get the table name from the model:



      User.joins(:ocr).select(:email, "`#{Ocr.table_name}`.`small_value_only`")


      The next alternative relies on merge to get rid of the string for selecting the column:



       User.joins(:ocr).merge(Ocr.select(:project_id)).select(:id) 


      But as the resulting sql does not reference the Ocr table for small_value_only, it would be equivalent to writing:



       User.joins(:ocr).select(:email, :small_value_only) 


      and will only work as long as small_value_only does not also exist on the users table.



      The last alternative does not have that shortcoming but is way more verbose:



      User.joins(:ocr).select(Ocr.select(:small_value_only).arel.projections).select(:id)





      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%2f53381034%2fis-there-a-way-to-be-explicit-on-select-columns-when-using-includes-in-rai%23new-answer', 'question_page');
        }
        );

        Post as a guest















        Required, but never shown

























        3 Answers
        3






        active

        oldest

        votes








        3 Answers
        3






        active

        oldest

        votes









        active

        oldest

        votes






        active

        oldest

        votes









        4





        +25









        You don't give much information on how your tables are set, so I'll assume that User belongs_to :ocr, User has an ocr_id column, and the ocr table's name is ocr.



        You should use joins instead of includes, that's how you generate an INNER JOIN query and be able to retrieve the joined table's column(s). Finally you can alias the column name with AS, so it's easier to retrieve in your model:



        users = User.joins(:ocr).select(:email, :ocr_id, '`ocr`.`small_value_only` AS `ocr_sma`')

        users.each do |user|
        user.email # Users.email for this record
        user.ocr_sma # Joined Ocr.small_value_only for this record
        end


        (Obviously I aliased it ocr_sma, but you can give it the name you want)






        share|improve this answer






























          4





          +25









          You don't give much information on how your tables are set, so I'll assume that User belongs_to :ocr, User has an ocr_id column, and the ocr table's name is ocr.



          You should use joins instead of includes, that's how you generate an INNER JOIN query and be able to retrieve the joined table's column(s). Finally you can alias the column name with AS, so it's easier to retrieve in your model:



          users = User.joins(:ocr).select(:email, :ocr_id, '`ocr`.`small_value_only` AS `ocr_sma`')

          users.each do |user|
          user.email # Users.email for this record
          user.ocr_sma # Joined Ocr.small_value_only for this record
          end


          (Obviously I aliased it ocr_sma, but you can give it the name you want)






          share|improve this answer




























            4





            +25







            4





            +25



            4




            +25





            You don't give much information on how your tables are set, so I'll assume that User belongs_to :ocr, User has an ocr_id column, and the ocr table's name is ocr.



            You should use joins instead of includes, that's how you generate an INNER JOIN query and be able to retrieve the joined table's column(s). Finally you can alias the column name with AS, so it's easier to retrieve in your model:



            users = User.joins(:ocr).select(:email, :ocr_id, '`ocr`.`small_value_only` AS `ocr_sma`')

            users.each do |user|
            user.email # Users.email for this record
            user.ocr_sma # Joined Ocr.small_value_only for this record
            end


            (Obviously I aliased it ocr_sma, but you can give it the name you want)






            share|improve this answer















            You don't give much information on how your tables are set, so I'll assume that User belongs_to :ocr, User has an ocr_id column, and the ocr table's name is ocr.



            You should use joins instead of includes, that's how you generate an INNER JOIN query and be able to retrieve the joined table's column(s). Finally you can alias the column name with AS, so it's easier to retrieve in your model:



            users = User.joins(:ocr).select(:email, :ocr_id, '`ocr`.`small_value_only` AS `ocr_sma`')

            users.each do |user|
            user.email # Users.email for this record
            user.ocr_sma # Joined Ocr.small_value_only for this record
            end


            (Obviously I aliased it ocr_sma, but you can give it the name you want)







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Feb 2 at 10:03

























            answered Feb 2 at 8:48









            BenjBenj

            10.8k12458




            10.8k12458

























                1














                If you are really only trying to get 2 columns of data, you should use pluck instead of select. Speeding up these kinds of big queries is exactly what pluck was created for. Instead of all of the overhead that goes with creating all the ActiveRecord objects for all your data, pluck just returns an array of data:



                values = User.includes(:ocr).pluck(:email, "ocr.small_value_only")
                # [["email_1", 3], ["email_2", 7]] # 3 and 7 are the small values





                share|improve this answer




























                  1














                  If you are really only trying to get 2 columns of data, you should use pluck instead of select. Speeding up these kinds of big queries is exactly what pluck was created for. Instead of all of the overhead that goes with creating all the ActiveRecord objects for all your data, pluck just returns an array of data:



                  values = User.includes(:ocr).pluck(:email, "ocr.small_value_only")
                  # [["email_1", 3], ["email_2", 7]] # 3 and 7 are the small values





                  share|improve this answer


























                    1












                    1








                    1







                    If you are really only trying to get 2 columns of data, you should use pluck instead of select. Speeding up these kinds of big queries is exactly what pluck was created for. Instead of all of the overhead that goes with creating all the ActiveRecord objects for all your data, pluck just returns an array of data:



                    values = User.includes(:ocr).pluck(:email, "ocr.small_value_only")
                    # [["email_1", 3], ["email_2", 7]] # 3 and 7 are the small values





                    share|improve this answer













                    If you are really only trying to get 2 columns of data, you should use pluck instead of select. Speeding up these kinds of big queries is exactly what pluck was created for. Instead of all of the overhead that goes with creating all the ActiveRecord objects for all your data, pluck just returns an array of data:



                    values = User.includes(:ocr).pluck(:email, "ocr.small_value_only")
                    # [["email_1", 3], ["email_2", 7]] # 3 and 7 are the small values






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Feb 2 at 23:11









                    Old ProOld Pro

                    14.7k23867




                    14.7k23867























                        1














                        I do not want to say that the following options are necessarily better than what Benj and Old Pro suggest, but rather want to provide some more alternatives.



                        The first one makes use of the includes method as that is what OP asked for. But as includes will by default select all columns of the primary model (User), one first has to remove that default selection via the except method:



                        User.includes(:ocr).except(:select).select(:id, 'ocr.small_value_only')


                        Of course, using join in the first place would be easier.



                        The following options stem from my dislike for having strings in AR queries as it tends to rely on knowledge about the called model which AR should actually abstract for you, i.e. the name of the database table (ocr).



                        A naive implementation for removing that knowledge is to get the table name from the model:



                        User.joins(:ocr).select(:email, "`#{Ocr.table_name}`.`small_value_only`")


                        The next alternative relies on merge to get rid of the string for selecting the column:



                         User.joins(:ocr).merge(Ocr.select(:project_id)).select(:id) 


                        But as the resulting sql does not reference the Ocr table for small_value_only, it would be equivalent to writing:



                         User.joins(:ocr).select(:email, :small_value_only) 


                        and will only work as long as small_value_only does not also exist on the users table.



                        The last alternative does not have that shortcoming but is way more verbose:



                        User.joins(:ocr).select(Ocr.select(:small_value_only).arel.projections).select(:id)





                        share|improve this answer




























                          1














                          I do not want to say that the following options are necessarily better than what Benj and Old Pro suggest, but rather want to provide some more alternatives.



                          The first one makes use of the includes method as that is what OP asked for. But as includes will by default select all columns of the primary model (User), one first has to remove that default selection via the except method:



                          User.includes(:ocr).except(:select).select(:id, 'ocr.small_value_only')


                          Of course, using join in the first place would be easier.



                          The following options stem from my dislike for having strings in AR queries as it tends to rely on knowledge about the called model which AR should actually abstract for you, i.e. the name of the database table (ocr).



                          A naive implementation for removing that knowledge is to get the table name from the model:



                          User.joins(:ocr).select(:email, "`#{Ocr.table_name}`.`small_value_only`")


                          The next alternative relies on merge to get rid of the string for selecting the column:



                           User.joins(:ocr).merge(Ocr.select(:project_id)).select(:id) 


                          But as the resulting sql does not reference the Ocr table for small_value_only, it would be equivalent to writing:



                           User.joins(:ocr).select(:email, :small_value_only) 


                          and will only work as long as small_value_only does not also exist on the users table.



                          The last alternative does not have that shortcoming but is way more verbose:



                          User.joins(:ocr).select(Ocr.select(:small_value_only).arel.projections).select(:id)





                          share|improve this answer


























                            1












                            1








                            1







                            I do not want to say that the following options are necessarily better than what Benj and Old Pro suggest, but rather want to provide some more alternatives.



                            The first one makes use of the includes method as that is what OP asked for. But as includes will by default select all columns of the primary model (User), one first has to remove that default selection via the except method:



                            User.includes(:ocr).except(:select).select(:id, 'ocr.small_value_only')


                            Of course, using join in the first place would be easier.



                            The following options stem from my dislike for having strings in AR queries as it tends to rely on knowledge about the called model which AR should actually abstract for you, i.e. the name of the database table (ocr).



                            A naive implementation for removing that knowledge is to get the table name from the model:



                            User.joins(:ocr).select(:email, "`#{Ocr.table_name}`.`small_value_only`")


                            The next alternative relies on merge to get rid of the string for selecting the column:



                             User.joins(:ocr).merge(Ocr.select(:project_id)).select(:id) 


                            But as the resulting sql does not reference the Ocr table for small_value_only, it would be equivalent to writing:



                             User.joins(:ocr).select(:email, :small_value_only) 


                            and will only work as long as small_value_only does not also exist on the users table.



                            The last alternative does not have that shortcoming but is way more verbose:



                            User.joins(:ocr).select(Ocr.select(:small_value_only).arel.projections).select(:id)





                            share|improve this answer













                            I do not want to say that the following options are necessarily better than what Benj and Old Pro suggest, but rather want to provide some more alternatives.



                            The first one makes use of the includes method as that is what OP asked for. But as includes will by default select all columns of the primary model (User), one first has to remove that default selection via the except method:



                            User.includes(:ocr).except(:select).select(:id, 'ocr.small_value_only')


                            Of course, using join in the first place would be easier.



                            The following options stem from my dislike for having strings in AR queries as it tends to rely on knowledge about the called model which AR should actually abstract for you, i.e. the name of the database table (ocr).



                            A naive implementation for removing that knowledge is to get the table name from the model:



                            User.joins(:ocr).select(:email, "`#{Ocr.table_name}`.`small_value_only`")


                            The next alternative relies on merge to get rid of the string for selecting the column:



                             User.joins(:ocr).merge(Ocr.select(:project_id)).select(:id) 


                            But as the resulting sql does not reference the Ocr table for small_value_only, it would be equivalent to writing:



                             User.joins(:ocr).select(:email, :small_value_only) 


                            and will only work as long as small_value_only does not also exist on the users table.



                            The last alternative does not have that shortcoming but is way more verbose:



                            User.joins(:ocr).select(Ocr.select(:small_value_only).arel.projections).select(:id)






                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Feb 7 at 23:05









                            ulfertsulferts

                            1,614717




                            1,614717






























                                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%2f53381034%2fis-there-a-way-to-be-explicit-on-select-columns-when-using-includes-in-rai%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)