Postgres Functions & Non-sargable Queries

Using postgres functions inside a where clause can make a query non-sargable.

Database Structure

tracks has_many artists

Non-Sargable Query

Using a LOWER function prevents DBMS engine from using indexes.

Track.joins(:artists).where('LOWER(tracks.display_name) LIKE ?', "eric clapton%").explain

Gather  (cost=1000.85..56714.32 width=4061)
   Workers Planned: 2
   ->  Nested Loop  (cost=0.85..55713.62 rows=3 width=4061)
         ->  Nested Loop  (cost=0.42..55708.37 rows=3 width=4069)
               ->  Parallel Seq Scan on tracks  (cost=0.00..55365.17 rows=41 width=4061)
                     Filter: (lower((display_name)::text) ~~* 'eric clapton%'::text)
               ->  Index Scan using index_artist_relations_on_artist_item_type_and_artist_item_id on artist_relations  (cost=0.42..8.36 rows=1 width=16)
                     Index Cond: (((artist_item_type)::text = 'Track'::text) AND (artist_item_id = tracks.id))
         ->  Index Only Scan using idx_35952_primary on artists  (cost=0.43..1.75 rows=1 width=8)
               Index Cond: (id = artist_relations.artist_id)


Sargable Query

Removing LOWER function allows DBMS engine to use indexes, resulting in faster execution.

Track.joins(:artists).where('tracks.display_name ILIKE ?', "eric clapton%").explain

Nested Loop  (cost=1497.60..2695.43 width=4061)
   ->  Nested Loop  (cost=1497.17..2684.94 rows=6 width=4069)
         ->  Bitmap Heap Scan on tracks  (cost=1496.75..1873.05 rows=97 width=4061)
               Recheck Cond: ((display_name)::text ~~* 'eric clapton%'::text)
               ->  Bitmap Index Scan on index_tracks_on_display_name  (cost=0.00..1496.73 rows=97 width=0)
                     Index Cond: ((display_name)::text ~~* 'eric clapton%'::text)
         ->  Index Scan using index_artist_relations_on_artist_item_type_and_artist_item_id on artist_relations  (cost=0.42..8.36 rows=1 width=16)
               Index Cond: (((artist_item_type)::text = 'Track'::text) AND (artist_item_id = tracks.id))
   ->  Index Only Scan using idx_35952_primary on artists  (cost=0.43..1.75 rows=1 width=8)
         Index Cond: (id = artist_relations.artist_id)


Abhi

Watch CI status on Github

When you have Github's hub installed, you can get the ci-status of your current branch like this:

$ hub ci-status
pending


If you install watch (e.g. brew install watch) you can continuously see the state:

$ watch hub ci-status


Const

Bash aliases and magic

I have a seperate file in my user folder named ~/.aliases. I use this to store all of my bash aliases. I use aliases a lot and add new ones regularly so I created a bash function to add new aliases on the fly.

addalias() {
  echo 'alias '$1'="'$2'"' >> ~/.aliases
  source ~/.aliases
}


Now you can add an alias from the terminal which will be available to the current session and all new sessions... shell $ addalias foo 'echo "bar"'

If I find myself using commands over and over I will create aliases shell alias g='git' alias b='bundle' alias be='bundle exec' alias bu='bundle update' alias r='bin/rails' alias rr='bin/rails routes' alias migration='bin/rails generate migration' alias migrate='bin/rails db:migrate; bin/rails db:migrate RAILS_ENV=test' alias rollback="rake db:rollback; RAILS_ENV=test rake db:rollback" alias push='git push origin HEAD' alias pr='git pull-request -c -m' alias ppr="git push origin HEAD && git pull-request -m" alias pulls='git browse -- pulls' alias prs='git pr list' pull() { git browse -- pull/'$1' } alias la='ls -a' alias ll='ls -al'

2
Joe

You can use FactoryBot for non ActiveRecord models

You can use FactoryBot's initialize_with method to initialize an object any way you like which allows you to factorize any of your objects at will

FactoryBot.define do
  factory :citizen_id_image, class: CitizenIdImagePresenter do
    image_url { 'https://placehold.it/200x200' }

    initialize_with { new(image_url: image_url) }
  end
end
build(:citizen_id_image)
#=> 


Joe

Force push previous commit to repo

If you push some code and want to force push a previous commit you can do so with shell git push origin +commit_sha^:branch_name

Where git interprets sha^ as the parent of sha and + as a forced non-fastforward push.

You can also use HEAD^ where ^ is the parent of HEAD so HEAD^^^ would push the commit 3 commits before the current commit. e.g. shell git push origin +HEAD^^^:feature/i_did_an_oopsie

1
Joe

Get current git branch name

git rev-parse --abbrev-ref HEAD


Ali

Back up a file with same name + prefix

$ cp todo.txt !#:1.bak
cp todo.txt todo.txt.bak


NOTE: Actually you can use !#:1 in every command 'cause that's last argument passed to a command.

UPDATED: Now it works with zsh

Ali

optional relation + RSpec w/shoulda-matchers

User 1 - 0..1 Laptop

  • Model ```ruby a/m/user.rb has_one :laptop

a/m/laptop.rb belongs_to :user, optional: true ```

  • spec ruby s/m/laptop_spec.rb it { should belong_to(:user).optional }
Alyson

How to find god objects in your project (Rails)

Go to your project root and cd into app/models (using CLI obviously) then run wc -lw * | sort -u

$ wc -lw * | sort -u
103     186 deal_broker.rb
116     313 physical_sim_report.rb
126     229 coupon_code.rb
126     260 subscription_mission.rb
129     284 payment.rb
145     290 deal_value.rb
147     255 single_use_coupon.rb
166     344 sim_card.rb
166     406 coupon_campaign.rb
225     451 order_summary.rb
260     534 deal.rb
308     783 package.rb
443    1031 user.rb
728    1744 subscription.rb
7262   15841 total


So now you can see Rocky is all about User ,Subscription and Package.

Note: First column number of lines and the second column number of words.

Ali

Remove ruby 2.6 bundled bundler

Generating a rails 6 app with ruby 2.6+ when you have bundler v2 installed creates unusable binstubs. If you are seeing this error You must use Bundler 2 or greater with this lockfile. you can remove the bundler that comes bundled with ruby 2.6 and rely on the manually installed v2 bundler.

If you are using rbenv you can use this command (adjust for your ruby version e.g. 2.6.{0, 1} etc), other installation systems will use different locations

rm -rf ~/.rbenv/versions/2.6.1/lib/ruby/2.6.0/bundler*


Joe