...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...
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...
...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...
...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...
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...
...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...
...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
...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...
...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...
...conditions as a hash (as seen above). To be on the safe side, use SQL fragments which are never overridden: >> Article.available.scoped(:conditions => [ 'articles.state = ?', 'deleted' ]) # SELECT * FROM `articles` WHERE ((articles.state = 'deleted...
...pgAdmin as a GUI. While you can do most things just like on an SQL console, you can use it to display EXPLAIN results in a more human-readable way...
...pgAdmin, connect to your server Pick a database from the left pane Click the "SQL" icon in the toolbar, or press Ctrl+E to open the query tool.
...from and to InnoDB tables while completely bypassing the parsing and planning overhead of SQL. This could potentially be very awesome because we would then have a distributed key/value store...
...that stores data in a way it can also be queried with complex SQL queries...
...dump in the custom format you can can view the text format dump (plain SQL statements). PostgreSQL 12 and newer Write it as a textdump to a file:
...error when using Graticule? Numerical argument out of domain - acos Similarly to the to_sql problem for some edge cases, Graticule::Distance::Spherical.distance (and possibly those of Graticule's other...
...the same coordinates (applies only for some). How to fix So, similar to the SQL fix, we just force the value we compute the arc cosine of into...
...db:migrate and stash you changes in the migration files. 2. Temporally change the sql mode mysql> SELECT @@global.sql_mode; +--------------------------------------------+ | @@global.sql_mode | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+
The SQL code generated by Graticule's spherical distance computation is insufficient and can lead to NULL distances in edge cases. The reason for this is that MySQL is performing...
...it. Put the following into an initializer like config/initializers/graticule_spherical_to_sql_without_nulls.rb which patches Graticule's to_sql method: Graticule::Distance::Spherical.class_eval do def self.to_sql(options) options = { :units => :miles, :latitude_column...
It's generally not trivial to change a datetime's seconds, minutes, etc in SQL. Here is how it works when speaking PostgreSQL. Consider you have a timestamp column whose...
...be the one who messed up permissions on a shared database cluster. The following SQL code will find all table names and execute a DROP TABLE statement for each.
...this case means that all those commands are processed step by step, causing 4 SQL statements to change the table. In turn, your database needs to modify the table structure...
PostgreSQL supports the SQL OVERLAPS operator. You can use it to test if two date ranges overlap: => SELECT ('2001-02-16'::date, '2001-12-21'::date) OVERLAPS...