I have a real-world application with some complex queries that I want to
convert to Arel (as part of an upgrade to Rails 3.1). So that I can
understand what I’m doing before I flail around in my real app, I wrote
a little sample app (just the models) with some similar associations –
one table joined with itself and more tables that join to another table,
so there are some queries that require disambiguation of column names.
I’m having trouble finding good docs that cover these kind of use case.
Please feel free to point me to some if they exist.
My sample app has a person, which has many tweets. Every person speaks
a language and may tweet in their own language or a different language,
so there’s a language table associated with both people and tweets.
Also, people have followers and may be followed by other people (stored
in the same table, of course). I posted the app on github with some
seed data if anyone wants to try it in the console:
git clone git://github.com/ultrasaurus/twitter_like_example_app.git
sample_app
cd sample_app
bundle install
rake db:migrate
rake db:seed
rails c
I can create simple queries like this:
list all people’s names alphabetically
Person.order(:name).all.map(&:name)
how many people speak french?
Person.joins(:language).where(:languages => {:code => ‘fr’}).count
How many German people are in the data set?
Person.where(:language_id => 2).order(:name).count
Create a list of them alphabetized by name
Person.where(:language_id => 2).order(:name).all.map(&:name)
How many people have the first name that begins with “A”?
Person.where(“name like ‘A%’”).count
I’m not having as much success with more complicated ones like these:
all people grouped by language, then alphabetized by name
this sorts people by name not language:
Person.order(:name, {:language => :english_name}).all.map { |p| puts
“#{p.name} #{p.language.english_name}” }
how many people tweet in french?
Person.where(:tweets => {:language_id => ‘fr’}).count
(0.1ms) SELECT COUNT() FROM “people” WHERE “tweets”.“language_id” =
0
SQLite3::SQLException: no such column: tweets.language_id: SELECT
COUNT() FROM “people” WHERE “tweets”.“language_id” = 0
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column:
tweets.language_id: SELECT COUNT(*) FROM “people” WHERE
“tweets”.“language_id” = 0
How many people tweet in french who are english speakers?
all tweets sorted by language (with language alphabetized)
How many people with a first name beginning with A follow someone whose
first name begins with “S” ?
List all the tweets in in french that can be seen by french people (e.g.
where the person whose language is french follows someone who has a
tweet which is french)
Thanks in advance,
Sarah