This post covers some common and basic ways to retrieve data from the database using Active Record in Ruby on Rails.
Imagine that we have a database table called movies that look something like below,
where the ID column is an auto incremental primary key, the name
column is a simple string and the translations column is of type JSONB.
| id | name | translations |
|---|---|---|
| 1 | “The Shawshank Redemption” | { “es”: “Cadena perpetua”, “se”: “Nyckeln till frihet” } |
| 2 | “The Godfather” | { “es”: “El padrino”, “se”: “Gudfadern” } |
| 3 | “The Good, the Bad and the Ugly” | { “es”: “Il buono, il brutto, il cattivo”, “se”: “Den gode, den onde, den fule” } |
| 4 | “City of God” | { “en”: “Ciudad de Dios”, “se”: “Guds stad” } |
Our Active Record Model for this would then likely be Movie. So let’s see some basic queries
we can do with the methods provided by Active Records.
Find record by ID:
irb> Movie.find(1)
=> #<Movie id: 1, name: "The Shawshank Redemption", translations: { "es": "Cadena perpetua", "se": "Nyckeln till frihet" }>Find record by DB table field
irb> Movie.find_by(name: "The Godfather")
=> #<Movie id: 2, name: "The Godfather", translations: { "es": "El padrino", "se": "Gudfadern" }>
# Or with several properties in the query:
irb> Movie.find_by(name: "The Godfather", id: 2)
=> #<Movie id: 2, name: "The Godfather", translations: { "es": "El padrino", "se": "Gudfadern" }>
Find records by LIKE on DB table field
irb> Movie.where("name LIKE ?", "City%")
=> #<ActiveRecord::Relation [#<Movie id: 4, name: "City of God", translations: { "en": "Ciudad de Dios", "se": "Guds stad" }>]>Find records by querying on a JSON field
irb> Movie.where("translations ->> 'se' = ?", "Guds stad")
=> #<ActiveRecord::Relation [#<Movie id: 4, name: "City of God", translations: { "en": "Ciudad de Dios", "se": "Guds stad" }>]>
Find records by LIKE on a JSON field
irb> Movie.where("translations ->> 'se' LIKE ?", "Guds%")
=> #<ActiveRecord::Relation [#<Movie id: 4, name: "City of God", translations: { "en": "Ciudad de Dios", "se": "Guds stad" }>]>