> For the complete documentation index, see [llms.txt](https://certil-remy.gitbook.io/learn/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://certil-remy.gitbook.io/learn/orm-and-active-record/untitled-7.md). # Preventing Record Duplication ## Learning Goals * Avoid creating duplicate records in a database that is mapped to a Ruby program * Build a `#find_or_create_by` method ## The Dreaded Duplication What happens when two Ruby objects get created using the same attributes? If we are trying to persist representations of such objects to a database, would we end up with essentially identical rows in our table? That would make for a very confusing database and our program would quickly become useless as a way to store and manage information. For example, lets say we have a `Song` class that produces individual song objects, each of which has a `name` and `album` attribute. Nothing stops us from creating two objects, each of which has the exact same name and album. ``` hello = Song.new("Hello", "25") hello_again = Song.new("Hello", "25") ``` What happens when we save these objects to our database? *For this example*, we'll assume our connection to the database is stored in `DB[:conn]`. ``` hello.save hello_again.save DB[:conn].execute("SELECT * FROM songs WHERE name = "Hello") # => [[1, "Hello", "25"], [2, "Hello", "25"]] ``` We have two records that contain the same information! How can we avoid this? When we try to save a new `Song` instance, we should first check to see whether the object we are trying to save already has an equivalent record in the database. If it does, we should simply update it. Otherwise, we can go ahead and save it. ## Saving vs. Updating Let's say we have a song, `hello`: ``` hello = Song.new("Hello", "25") ``` Before we call `#save` on our `hello` object, we need to check and see if a record containing this name and album already exists in the database. The SQL statement to accomplish that would look something like this: ``` SELECT * FROM songs WHERE name = "Hello", album = "25"; ``` If this statement returns a record, we don't need to create a new record, only update the existing one. Otherwise, we need to insert a new record into our database table. Let's build a method that will allow us to either *find an existing record* or *create and save* a new one. ## The `#find_or_create_by` Method Take a look at our `Song` class. ``` class Song attr_accessor :name, :album attr_reader :id def initialize(id=nil, name, album) @id = id @name = name @album = album end def save if self.id self.update else sql = <<-SQL INSERT INTO songs (name, album) VALUES (?, ?) SQL DB[:conn].execute(sql, self.name, self.album) @id = DB[:conn].execute("SELECT last_insert_rowid() FROM songs")[0][0] end end def self.create(name:, album:) song = Song.new(name, album) song.save song end def self.find_by_id(id) sql = "SELECT * FROM songs WHERE id = ?" result = DB[:conn].execute(sql, id)[0] Song.new(result[0], result[1], result[2]) end def update sql = "UPDATE songs SET name = ?, album = ? WHERE id = ?" DB[:conn].execute(sql, self.name, self.album, self.id) end end ``` Let's build our `#find_or_create_by` method: ``` def self.find_or_create_by(name:, album:) song = DB[:conn].execute("SELECT * FROM songs WHERE name = ? AND album = ?", name, album) if !song.empty? song_data = song[0] song = Song.new(song_data[0], song_data[1], song_data[2]) else song = self.create(name: name, album: album) end song end ``` Let's break this down: First, we query the database: does a record exist that has this name and album? ``` song = DB[:conn].execute("SELECT * FROM songs WHERE name = ? AND album = ?", name, album) ``` If such a record exists, the `song` variable will now point to an array that would look something like this: *song name and album name provided as a hypothetical example* If this is the case, then the statement: `!song.empty?` will return `true`. Therefore, we will use the returned values to make a new "Hello" object that Ruby can play around with, but we will *not* save it to the database. That re-instantiation of an existing Song object is accomplished with these lines: ``` song_data = song[0] song = Song.new(song_data[0], song_data[1], song_data[2]) ``` We grab the `song_data` from the `song` array of arrays, setting `song_data` equal to: Then, we use this array to create a new `Song` instance with the given id, name and album. However, if no record exists that matches the name and album passed in as arguments, then `!song.empty?` will return `false`, and we will instead create and save a new `Song` instance with the `#create` method. At the end of our `#find_or_create_by` method, we will return the song object whose database entry we either found or created. This method assumes that there isn't already a song object matching these attributes, but that there may already exist a database entry with the same name and album. Therefore, it instantiates a new instance of the `Song` class while preventing a duplicate database entry. ### Our Code in Action Now, we can use our `Song` class without worrying about creating duplicate records: ``` Song.find_or_create_by(name: "Hello", album: "25") Song.find_or_create_by(name: "Hello", album: "25") DB[:conn].execute("SELECT * FROM songs WHERE name = Hello, album = 25") # => [[1, "Hello", "25"]] ``` Although we called `#find_or_create_by` twice *with the same data* (gasp!), we only created *one record with that data*. ## Bonus Section- Video Reviews * [Video Review- ORMs and SQL Joins](https://www.youtube.com/watch?v=mZROu5oSWfI) * [Video Review- Building a Metaprogrammed Abstract ORM](https://www.youtube.com/watch?v=hts7TjpPw-8) * [Video Review- Code-along: Building a Room Scraper and ORM](https://www.youtube.com/watch?v=1eIgKGukBlg) View [Preventing Record Duplication](https://learn.co/lessons/orm-find-or-create-by) on Learn.co and start learning to code for free. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://certil-remy.gitbook.io/learn/orm-and-active-record/untitled-7.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.