...You are submitting queries to fetch data in a given structure (like SELECT in SQL) or mutations to alter the database (similar to POST/PUT/DELETE in REST). You can control how...
...string argument as the value for ‘column’. This string is passed directly to the SQL statement, making it possible to use all sorts of fun things to lock down the...
So regarding to the cited site Rails 5+ allows me to use an SQL statement in my index: t.index 'shop_id, lower(street), lower(house_number), lower(zip_code...
...many thousand keys). This means every association that you pivot around will trigger one SQL query...
...example can also be achieved with Rails internals: Article.group(:brand).count. This translates to SQL, so it executes fast. However, grouping is restricted to columns (attributes). Using #count_by gives...
When you want to filter records in a model where a string column roughly matches a given term, you can...
...column delegates to #pluck, which can be used for the same effect. Relation#to_sql # Rails 2 scope Post.scoped(:conditions => { :id => [1, 2] }).to_sql # => "SELECT `posts`.* FROM `posts` WHERE...
...posts.id` IN (1, 2)" ^ # Rails 3 relation Post.where(:id => [1, 2]).to_sql # => "SELECT `posts`.* FROM `posts` WHERE `posts.id` IN (1, 2)" Implementation note: Rails 3+ implements #to_sql.
...okay, but next automatic id will still be 101 Record.create! # will cause an SQL error 'duplicate key value violates unique constraint "records_pkey"' Workaround In tests, one workaround would be...
The change_column method for rails migrations support casting with a custom SQL statement. This allows us to change a column type and keep the former content as the new...
...are consuming the most disk space. You can easily check this using the following SQL statement from the PostgreSQL wiki. SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total...
...fallback to ActiveRecord::Base as connection base in the strategies that do not use SQL. That's why in the code example above, we could use DatabaseCleaner[:active_record].clean...
...you might load an outdated database structure. Since the schema.rb does not hold any SQL specific information, like charsets or collations, your test database may differ from your development database...
...without further information). To avoid this problem, your environment.rb needs to say: config.active_record.schema_format = :sql This will make a db/development_structure.sql appear which is then also read and loaded when you...
Using Scenic, you can bring the power of SQL views to your Rails application without having to switch your schema format to SQL. Scenic provides a convention for versioning views...
...that keeps your migration history consistent and reversible and avoids having to duplicate SQL strings across migrations. As an added bonus, you define the structure of your view in a...
...read more about window functions here, but for our purposes, know that the following SQL does what we need: SELECT posts.*, COUNT(*) OVER() AS full_count FROM (/* some complicated subquery...
This may be awkward to set up, but will work once you're done. Fun facts:
There are times when you need to send SQL to the database, like this: def self.some_count(field) field = connection.quote_column_name(field) scoped(:select => "COUNT(DISTINCT #{field}) AS count...
...not helping against injection: Klass.some_count("id`); DELETE FROM users; -- ") # Will result in this SQL which is valid but definitely undesirable: SELECT COUNT(DISTINCT `id`); DELETE FROM users; -- `) AS count...
Here is a way to create a duplicate of one database, with all its tables and their data, under a...
...my understanding of database indexes. As a small bonus, it includes a few helpful SQL oneliners like these two: Detecting unused indexes Detecting duplicate indexes Warning Do not run random...
...radius of a location is a bit trickier: def close_destinations(latitude, longitude) distance_sql = Graticule::Distance::Spherical.to_sql(:latitude => latitude, :longitude => longitude, :units => :kilometers) Destination.all(:conditions => [ "#{distance_sql...
...find every Destination that is close to the given latitude and longitude: Using to_sql will give you an SQL snippet to compute the distance from the given geo point...
...will throw an error to one of the offending threads. This error states the SQL statement that this thread was currently waiting for, and that tried to acquire one of...
Solutions: make your queries comply with it, if possible change the sql mode in the config/database.yml (and that of all your colleagues, staging and production servers!):
...adapter: mysql2 ... variables: sql_mode: TRADITIONAL (Be aware, that this solution disables the only_full_group_by setting globally and MySQL will select those values arbitrarily again)
Site.joins(:user).where(:users => { :name => 'Bruce' }) This scope will expand to a SQL query like this: SELECT * FROM sites INNER JOIN users WHERE sites.user_id = sites.id AND users.name...
...where(:users => { :name => 'Bruce' }).to_id_query #to_id_query will immediately run an SQL query where it collects all the IDs that match your scope: SELECT sites.id FROM sites...
...something else -- your IDE may even be aware of it, for example RubyMine understands <<-SQL, <<-HTML, <<-XML, <<-JSON, etc and highlights correctly. Please note: Your string will end with a...
...scope of Contact records: # We start by building a scope of all contacts. # No SQL query is made. all_contacts = Contact.all # => ActiveRecord::Relation # Now we filter the scope to only...
...contain contacts with "gmail" in either :name or :email column. # Again, no SQL query is made. gmail_contacts = ContactFilter.new.filter(all_contacts, 'gmail') # => ActiveRecord::Relation # Inspect the filtered scope. gmail_contacts.to_sql...
...order does not work with complex sorting constraints and may even silently create malformed SQL for rails < 5. Take a look at this query which orders by the maximum of...
Page.order('GREATEST(pages.published_from_de, pages.published_from_en) DESC').to_sql # => SELECT "pages".* FROM "pages" ORDER BY GREATEST(pages.published_from_de, pages.published_from_en) DESC Rails 4