Eager loading, like column names, and conditions

Hi all,

The setup:
I've got a Candidate model, and it has two associations like:
  has_many :next_tasks, :class_name => 'Task', :foreign_key =>
'candidate_id', :conditions => ['start_time > now() and not deleted =
0'], :order => 'start_time'
  has_many :previous_tasks, :class_name => 'Task', :foreign_key =>
'candidate_id', :conditions => ['start_time < now() and not deleted =
0'], :order => 'start_time desc'

I have a controller which is using will_paginate, which at some point is
using ActiveRecord to get a count, and it's eager loading both of these
associations:
:include => [:status, :position, :user, { :previous_tasks => :user },
{:next_tasks => :user}, :hiring_manager]

The problem:

I'm getting an error like:
Column 'start_time' in on clause is ambiguous ...
The two relevant areas of the clause are:
LEFT OUTER JOIN `tasks` ON tasks.candidate_id = candidates.id AND
start_time < now() and not deleted = 0
and
LEFT OUTER JOIN `tasks` next_tasks_candidates ON
next_tasks_candidates.candidate_id = candidates.id AND start_time >
now() and not deleted = 0

So, AR is smart enough to join 'tasks' as next_tasks_candidates, but not
to append that join name to the conditions.

This seems (notice, just seems... I don't pretend to be a Rails expert,
demanding that this is a problem with Rails and not my code) to be a
problem with AR and doing a count on a query that is eager loading two
associations which have conditions with the same variable names in them.

Anyone have any thoughts on what I can do to fix this?

Thanks,
Luke

Hi all,

The setup:
I've got a Candidate model, and it has two associations like:
has_many :next_tasks, :class_name => 'Task', :foreign_key =>
'candidate_id', :conditions => ['start_time > now() and not deleted =
0'], :order => 'start_time'
has_many :previous_tasks, :class_name => 'Task', :foreign_key =>
'candidate_id', :conditions => ['start_time < now() and not deleted =
0'], :order => 'start_time desc'

I have a controller which is using will_paginate, which at some
point is
using ActiveRecord to get a count, and it's eager loading both of
these
associations:
:include => [:status, :position, :user, { :previous_tasks => :user },
{:next_tasks => :user}, :hiring_manager]

The problem:

I'm getting an error like:
Column 'start_time' in on clause is ambiguous ...
The two relevant areas of the clause are:
LEFT OUTER JOIN `tasks` ON tasks.candidate_id = candidates.id AND
start_time < now() and not deleted = 0
and
LEFT OUTER JOIN `tasks` next_tasks_candidates ON
next_tasks_candidates.candidate_id = candidates.id AND start_time >
now() and not deleted = 0

So, AR is smart enough to join 'tasks' as next_tasks_candidates, but
not
to append that join name to the conditions.

This seems (notice, just seems... I don't pretend to be a Rails
expert,
demanding that this is a problem with Rails and not my code) to be a
problem with AR and doing a count on a query that is eager loading two
associations which have conditions with the same variable names in
them.

Anyone have any thoughts on what I can do to fix this?

AR is not smart enough to parse your sql fragments and qualify table
names. You'd probably have to basically parse the sql exactly as the
database does to do that.

What you can do is have an interpolated condition. Off the top of my
head,

:conditions => '...#{aliased_table_name}.start_time...', ...

The single quotes are important.

Fred

Frederick Cheung wrote:

0'], :order => 'start_time desc'

Anyone have any thoughts on what I can do to fix this?

AR is not smart enough to parse your sql fragments and qualify table
names. You'd probably have to basically parse the sql exactly as the
database does to do that.

What you can do is have an interpolated condition. Off the top of my
head,

:conditions => '...#{aliased_table_name}.start_time...', ...

The single quotes are important.

Fred

Actually:

I JUST found this out right before I read your reply:

If you, in your conditions, specify the table name, so in my case
something like:

has_many :next_tasks, :class_name => 'Task', :foreign_key =>
'candidate_id', :conditions => ['tasks.start_time > now() and not
tasks.deleted = 0'], :order => 'start_time'

When it does the join, it WILL automatically replace the tasks. part
with the correct entry from the SQL query that it generates.

Thanks for the help, though, it's good to know that the alternative
option exists as well.

:slight_smile:

-Luke