I had a chance to use pluck and ibs while learning rails, so I will summarize it.
pluck can be used to send a query to get columns (s) from the table used in one model. Given a list of column names as an argument, returns an array of values for the specified column with the corresponding data type.
my_room_ids = current_user.entries.pluck(:room_id)
If you check with pry, it will be like this
pry(#<RoomsController>)> my_room_ids
=> [7, 8, 9, 10]
The above is the code to assign the array of values of: room_id to my_room_ids from the model of entries associated with current_user. If you set the second argument, you can output it as well. It is said that pluck can be about four times faster than map if it only searches a standard database. *important point The pluck method triggers the query directly, so you can't chain other scopes after it.
If no argument is passed to the pluck method, the data of all columns will be put in an array and returned. The return value is a two-dimensional array.
If you specify the Owner model in the pluck method with no arguments, the values of the id, name, and age columns are stored in the array as shown below.
If you do not pass any arguments to the pluck method
Owner.pluck
SELECT `owners`.* FROM `owners`
=> [
[1, "Tanaka", 23], # id, name,The value of the age column is stored
[2, "Ito", 44],
[3, "Takahashi", 65],
[4, "Kato", 23]
]
The data of all columns is further stored in the array, so it is returned as a two-dimensional array.
The pluck method can be used with the distinct method to remove duplicates. If you specify the age column as the argument of the pluck method as shown below, the value of 23 will be duplicated and stored in the array.
The value of the age column is obtained in duplicate
Owner.pluck(:age)
SELECT `owners`.`age` FROM `owners`
=> [23, 44, 65, 23] #Return value
You can get a unique value by removing duplicate values by using the distinct method as shown below.
console|Get the value in the age column by removing duplicates
Owner.distinct.pluck(:age)
SELECT DISTINCT `owners`.`age` FROM `owners`
=> [23, 44, 65] #Return value
If you check the return value, you can see that each unique value is stored in the array.
Specify the condition The pluck method can be used together with the where method that extracts the condition.
Example of narrowing down the value of the age column with the where method
Get the value of the id column whose age column value is 60 or less
Owner.where('age <= 60').pluck(:id)
SELECT `owners`.`id` FROM `owners` WHERE (age <= 60)
=> [1, 2, 4] #Return value
Get the value of the id column as an array with .pluck (: id) from the extracted data.
The pluck method can also get the column value of the related table by using the join method that joins the related tables internally.
Let's take the owners table that manages owners and the cats table that manages cats as examples.
Examples of owners and cats tables
When you want to get the type of cat owned by the owner in this table as an array, you can join the table internally with the join method and get the value of the species column of the cats table of the join destination with the pluck method as shown below. can.
An example of inner joining the owners table and cats table
Get the value of the species column of the cats table to join
Owner.joins(:cats).pluck(:species)
SELECT `species` FROM `owners` INNER JOIN `cats` ON `cats`.`owner_id` = `owners`.`id`
=> ["mix", "Scottish fold", "American shorthair", "mix"] #Return value
In Owner.joins (: cats), only the data that matches the value of the pink part (owners.id = join condition of cats.owner_id) is joined. Then, from the joined data, use .pluck (: species) to get the value of the species column of the cats table to be joined as an array.
Since the acquired values have duplicate mixes, you can remove the duplicates with the distinct method as shown below.
Get the unique value of the species column of the cats table to join
Owner.joins(:cats).distinct.pluck(:species)
SELECT DISTINCT `species` FROM `owners` INNER JOIN `cats` ON `cats`.`owner_id` = `owners`.`id`
=> ["mix", "Scottish fold", "American shorthair"] #Return value
You can check the return value that each unique value is stored in the array and returned.
The method chain can be used for ActiveRecord :: Relation objects, so it cannot be used for the pluck method where the array is the return value.
When using a method chain If you use a method chain after the pluck method, you will get a NoMethodError as shown below.
If you use a method chain after the pluck method
Owner.pluck(:id).where('age <= 60')
NoMethodError (undefined method `where' for [1, 2, 3, 4]:Array)
The above tried to call the where method using the method chain (.) After the pluck method, but since the return value of the pluck method is an array (Array), I get an error that there is no where method in the array (Array) class. It is occurring.
When using query methods together You cannot call other query methods in the method chain after the pluck method, so if you want to use the query method together, put the pluck method at the end as shown below.
Owner.where('age <= 60').pluck(:id)
SELECT `owners`.`id` FROM `owners` WHERE (age <= 60)
=> [1, 2, 4]
When using with other query methods, be careful only in the order in which the pluck methods are used. In addition, there is a select method to get the data of a specific column unless it is necessary to return it as an array. Since the return value of this select method is an ActiveRecord :: Relation object, you can use the method chain.
Supplementary explanation A query method is a method that creates various conditions when searching a database, such as where method and order method. It returns the result of the condition as an ActiveRecord :: Relation object.
Both are the same in that they get the data for a particular column, but there are some differences.
pluck and map methods
Model name.pluck(:Column name)
Model name.all.map(&:Column name) #Same as above
Differences in issued SQL statements The pluck method and the map method have different SQL statements issued when the method is executed. For example, if you get the data of the age column of the owners table with each method, it will be as follows.
Get age column data with pluck and map methods
Owner.pluck(:age) #When getting with the pluck method
SELECT `owners`.`age` FROM `owners`
=> [23, 44, 65, 23]
Owner.all.map(&:age) #When getting with the map method
SELECT `owners`.* FROM `owners`
=> [23, 44, 65, 23]
When I check the return value, both methods return an array, but the issued SQL statement is different.
When executed by the pluck method, the following SQL will be issued.
SQL |When the pluck method is executed
SELECT `owners`.`age` FROM `owners`
Since the age column of the owners table is specified in the SELECT statement like SELECT owners.age, you can see that "data of the age column of the owners table" is acquired in this SQL statement.
On the other hand, when the map method is executed, the following SQL will be issued.
SQL |When the map method is executed
SELECT `owners`.* FROM `owners`
Since all columns of the owners table are specified in the SELECT statement like SELECT owners. *, You can see that "data of all columns of the owners table" is acquired in this SQL statement.
From this, you can see that Owner.all.map (&: age) once gets all the data from the owners table and then puts only the data in the age column back into the array. As you can see, the return values of the pluck method and the map method are the same, but the SQL statements issued are different.
SQL differences between pluck and map methods The pluck method narrows down the column data to be acquired from the SQL stage. The map method gets the data of a specific column from the data after getting all the data. Scenes that use pluck instead of map Use the pluck method when you only want to use data from a specific column. The reason is that you only need specific data, but the map method reads all the data, wasting memory and slowing performance.
Scenes that use map instead of pluck If you want to get data from an instantiated object, use the map method. The reason is that the pluck method executes SQL every time even for an instantiated object, leading to poor performance.
Method that returns the value of the column specified in the argument as an array If multiple arguments are specified, a two-dimensional array will be returned. You cannot use a method chain after the pluck method
Get primary key column data The pluck method can get specific column data as an array, but if you want to get the primary key column data as an array, it is convenient to use the ids method.
Get the data of the primary key of the owners table below with the ids method.
Get the data of id column of owners table
Owner.ids
SELECT `owners`.`id` FROM `owners`
=> [1, 2, 3, 4] #Return value
If you check the return value, the column data of the primary key is acquired as an array.
Recommended Posts