Rails - Jul 2011 - Arel quiz: complex queries with associations

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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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:
> 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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Jul 25, 2011, at 8:30 AM, Sarah Allen wrote:
> 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
> 
FYI - the query says ''Language_id'' = 0 and your code says
''fr'' is this intentional?


-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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_

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.