Query relational tables when using sqlite and rails, a pathway to INNER JOINS outside of sqlite.

querying-relational-tables-in-sqlite-through-rails

Let's say you have a database containing two tables, doctors and patients.
This means you have two models, Doctor and Patient.
We want to find all doctors who have female patients.

Our relationship (one to many) is based on the doctor_id being a foreign key in the patients table.

To query through Rails we use:

Doctor.joins(:patients).where('patients.gender = ?', 'female')

--Breaking It Down--

Doctor      =>  We join all doctors through
.joins()    =>  An awesome ActiveRecord helper method that creates INNER JOINS
:patients   =>  The table we're joining to doctors. (remember that syntax immediately prefixed with : is usually a symbol)
.where()    =>  Another ActiveRecord helper that calls the sqlite method WHERE

Inside the where method, we provide two things.

'patients.gender = ?'   =>  Here, we're doing two things: passing the field we're interested in searching on, and using '?'                              to pass a gateway to feed our search value.
'female'                =>  This is the exact string value we're search for (as in, it will only match 'female' and not                                  'Female' or 'f'.