Home>

< ≪ My skill level and development environment are listed at the bottom > ≫

[What I want to do]

Summary

Calculate using data of the association destination table with Rails, and sort the calculated value

Specifically ...

There are four tables.
Songs table
CHORDS table
PRACTICES Table
Users table


※ This time, the unrelated column name is omitted

SONGS Sorting records.
Arrange in order of DESC based on the calculated value.

How to determine the calculated value is as follows

Calculated the number of Practice records that hit the grandson of the SONG record.
However, the same USER_ID is not duplicated.

As shown in the figure below, the total of Practice records is 5, but USER_ID: 2 is duplicated, and the ideal calculation result is 4.


Calculation itself is successful in the following description.

result= song.chords.Joins (: Practices) .distinct.count ('Practices.user_id')

Calculated value is assigned to virtual attribute.

# Song model excerpt
Class Song <
 ApplicationRecord
  Attribute: Song_practices: Integer, Default: 0
  Attr_Accessor: Song_Practices
END

Calculation and result assignment

SONG [: SONG_PRACTICES]= SONG.CHORDS.JOINS (: Practices) .Distinct.count ('Practices.user_id')

[Problem]

@songs= @ songs.order ("Song_Practices Desc")

I try to sort using the ORDER method, but an error.

Error statement
"MySQL2 :: Error: Unknown Column 'Song_Practices' in 'Order Clause': SELECTSONGS. * FromSONGSORDER by Song_Practices DESC

recognized as follows
→ There is no such column in the table Error

Error Verification

Conducted an operation test with the following controller.
Binding.PRY paused with "CHECK_POINT !!" and checked the state of the variable

# Songs Controller Excerpt
# Search is an action for the search feature.
DEF Search
    # This time there is no relationship (description for search function)
    Params [: Keyword] .Strip!
    keywords= params [: keyword] .Split (/\ S + /)
    # SONGS Record Call
    @Songs= Song.All.includes (: Chords)
   # Conditions This time, the value of PARAMS is sent so that the IF condition is TRUE.
    IF PARAMS [: SORT]== "PRACTICE"
      # Calculate the number of Practice linked as a grandchild for each SONG record
      @ Songs.each Do | Song |
        # Calculation, assignment to virtual attributes at the same time
        SONG [: SONG_PRACTICES]= SONG.CHORDS.JOINS (: Practices) .distinct.count ('Practices.user_id')
      END
      # Check_point !! ← here Binding.PRY
      Sort by # order.
      @Songs= @ Songs.Order ("SONG_PRACTICES DESC")
    else
      # This time the not related description (function to sort in Title order)
      @Songs= @ Songs.Order ("Title ASC")
    END
    # Below, this time not related description (description for search function)
    @Songs= @ Songs.where (JAM: PARAMS [: JAM]) if (params [: jam]== "True")
    @Songs= @ Songs.Where (Standard: params [: standard]) if (params [: standard]== "true")
    @Songs= @ Songs.Where (Beginner: Params [: Beginner]) if (params [: beginner]== "true")
    @songs= @ songs.where (Vocal: params [: Vocal]) if (params [: Vocal]== "True")
    @Songs= @ Songs.Where (Instrumental: params [: instrumental]) if (params [: instrumental]== "true")
    Keywords.each do | Keyword | UNLESS (Params [: Keyword] .NIL?)
      @Songs= @ Songs.Where ("TITLE LIKE?", "% # {Keyword}%")
    END
    Respond_to do | Format |
      Format.html
      Format.json
    END
  END

Check_POINT !! The state of the variable at the time point is as follows

@Songs

[# <
SONG: 0x00007FB29BB44C90
  ID: 204533774,
  Title: "Little Cabin Home on the Hill",Jam: false, Standard: true,
  Beginner: False,
  USER_ID: 1,
  CREATED_AT: Mon, 25 May 2020 13:37:46 JST +09: 00,
  Updated_at: Mon, 22 jun 2020 20:31:35 JST +09: 00,
  Vocal: False,
  Instrumental: false >
,
 # <
SONG: 0x00007FB29BB449E8
  ID: 307520937,
  Title: "Foggy Moutain Breakdown",
  JAM: TRUE,
  Standard: True,
  Beginner: True,
  USER_ID: 1,
  CREATED_AT: Mon, 25 May 2020 13:37:46 JST +09: 00,
  Updated_at: FRI, 26 Jun 2020 23:22:29 JST +09: 00,
  Vocal: False,
  Instrumental: TRUE >
,
//Subsequent

※ Virtual attribute: does not include Song_Practice.

@Songs [0] [: Song_Practices]

@songs [0] [: Song_practices]= ["Ideal calculation result ※"]

error did not occur
(If you delete the virtual attribute description from the model, CAN 'WRITE UNKNOWN ATTRIBUTESong_Practices= "Such attribute does not exist error" occurs)

* "Data indicating the validity of the calculation result" is not listed for the time being to avoid readability. Please say if necessary.

[Tested Other]

hypothesis

Because the behavior when the ORDER method is called is as follows, "Such attribute does not exist in DB." →
1 Find a specified column out of the model table
2 Sort using the data of the found column

so ...
→ Prerequisit that a column exists in the table.

Workaround

1 Define sort algorithm as instance method
2 Conduct sorting using the defined method

Method Implementation for countermeasures Before implementation ...
→ Define or use instance methods to test

Problem

As with the previous step, the instance method trial is tested at CHECK_POINT !!.

Definition of instance method for test

# Song model excerpt
Def test_song
    PUTS "TEST # {Title}"
END

trial result

1
@Songs [0] .TEST_SONG

TEST->
 Little Cabin Home on the Hill
= ≫
 nil

→ Operation without problems

2
@ Songs.test_song

NomethodError: undefined method `test_song 'for # <
SONG :: ActiveRecord_Relation: 0x00007FB2A5B1D230 >
from /users/imotoshouta/.rbenv/versions/2.5.1/lib/ruby/gems/2.5.0/gems/activeRecord-5.2.4.2/lib/active_record/relation/delegation.rb:125: In `Method_Missing '

→ error.

※ The method itself is not called, or the description of the inside is conducted to see if it is a problem

3
PUTS "TEST-> # {@ Thongs.Title} "

NomethodError: undefined method `title 'for # <
SONG :: ActiveRecord_Relation: 0x00007FB2A5B1D230 >
from /users/imotoshouta/.rbenv/versions/2.5.1/lib/ruby/gems/2.5.0/gems/activeRecord-5.2.4.2/lib/active_record/relation/delegation.rb:125: In `Method_Missing '

→ of course an error.
If the method itself is called, it is estimated that the same error occurs even in 2.
However, the contents of the error are different.
Therefore, in 2, the method itself is not called

Inference

@Songs and @Songs [0]
@ Songs → Array of Associative Array /Instance Variables
@Songs [0] → Just hash /instance variable alone

→ Isn't it necessary to describe another description or another place to define an instance method for an associative array?

measures

Speaking of methods that apply to associative arrays each.
Look at Each's definition statement and define well by imitating it?
→ Identifying the definition statement without going out.

Contents that you want to teach

1 plan of implementation method to realize "What I want to do"
2 How to sort with ORDER based on calculated value
3 How to define an instance method for an associative array
It is fortunate if you can tell me if there is an idea in any of the 1-3.

However ...

1 Small information is reduced to avoid reading. Please call if there is any other information required
2 SONG Table Providing Calculation columns OR Calculated Value Creating Table Creation, more glad to 3 I want to know if there is a definition sentence of the method used for the associative array, such as the EACH method.

[premise information]

My programming level feeling

Learning History
Learning time 550H
Microphone Progate
April TECH CAMP Curriculum
May-Self-made app creation

What can be handled somewhat
HTML /CSS, JavaScript (either JQuery), Ruby, Rails, MySQL, Github, AWS

[Reference] WEB page under preparation
Please use to understand the skill level

IP: 3.113.194.185

< ≪ Test user > ≫
Email Address: S @ S
Password: 111111

※ Since not started operation, you can freely register and delete it.
※ Please note that HTTPS is not supported.

Development Environment

OS MacOS Catalina (10.15.4)
VSCODE (1.45)

Version of language, GEM, etc.

Ruby 2.5.1P57
RAILS (5.2.4.2)
HAML (5.1.2)
HAML-RAILS (2.0.1)
MySQL2 (0.5.3)
SASS (3.7.4)
SASS-RAILS (5.1.0)
jQuery-Rails (4.4.0)

or more.
Thank you.

To those who answered teraatail.com/questions/273924

htb2021-05-10 17:59:00