Arel quiz: complex queries with associations

rubyonrails-talk
1

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

2

Hi Sarah,

I think you can find a lot of information here : http://rdoc.info/github/rails/arel/master/Arel/Table

What you are using here is ActiveRecord, ActiveRecord is using Arel itself to generate it's queries. You can use Arel directly this way :

scope :with_or, lambda { b = Person.arel_table     where(       b[:id].in([1,2,8])       .or(       b[:id].in([5,7,9]))     ) }

This scope is mostly useless, but the main idea is here.

Sarah Allen wrote in post #1012801:

3

Ok, I figured out "how many people tweet in french?" ... I was missing a join:

Person.joins(:tweets).where(:tweets => {:language_id => 'fr'}).count

   (0.1ms) SELECT COUNT(*) FROM "people" INNER JOIN "tweets" ON "tweets"."person_id" = "people"."id" WHERE "tweets"."language_id" = 0

Sarah

p.s. Thomas -- thanks I will read that too.

4

FYI - the query says 'Language_id' = 0 and your code says 'fr' is this intentional?

5

I must have posted that before I had enough coffee this morning. The correct answer to "how many people tweet in french" is:

Person.select('DISTINCT people.id').joins(:tweets).merge(Tweet.where(:language_id => 3)).count

via @ffu_

6

How many people tweet in French who speak English?

Person.select('DISTINCT

people.id').joins(:tweets).merge(Tweet.where(:language_id => 3)).where(:language_id => 1).count    (0.8ms) SELECT COUNT(DISTINCT people.id) FROM "people" INNER JOIN "tweets" ON "tweets"."person_id" = "people"."id" WHERE "tweets"."language_id" = 3 AND "people"."language_id" = 1 => 6