Jeff Ward
2006-Jun-19 19:39 UTC
[Rails] Using set_primary_key breaks acts_as_tree with non-integer column
I just switched from using the standard "id" column into using my own primary key and generating my own unique id for each record. But, this breaks acts_as_tree. Because my new primary key is not an integer, it breaks the SQL query as follows: StatementInvalid in PagesController#create -------------------------------------------------------------------------------------- Mysql::Error: #42S22Unknown column ''1_3'' in ''where clause'': SELECT * FROM pages WHERE (parent_id = 1_3 AND account_id = 1) ORDER BY position DESC LIMIT 1 Because parent_id is also now a :string column, the 1_3 should be put into single quotes like so: SELECT * FROM pages WHERE (parent_id = ''1_3'' AND account_id = 1) ORDER BY position DESC LIMIT 1 ... but acts_as_tree assumes that the default parent_id column should be an integer. Any ideas on how I can get Rails to treat that parent_id as a string instead of an integer? Jeff
Jeff Ward
2006-Jun-19 19:47 UTC
[Rails] Re: Using set_primary_key breaks acts_as_tree with non-integer column
Okay, a quick update..
I narrowed it down to being where I set my Page''s parent_id value....
This Is What I Have:
page.parent_id = params[:id]
Then, I tried this to force those single quotes:
page.parent_id = "''#{params[:id]}''"
and it inserts the record but includes the single quotes in the pages
table so in the parent_id column, the value is: ''1_3'' .... but
I don''t
want the single quotes saved!
So, I tried:
page.parent_id = "#{params[:id]}"
...and that got me back to the original problem.
I also tried:
page.parent_id = "#{params[:id].to_s}"
and the same thing....
On 6/19/06, Jeff Ward <animikii@gmail.com> wrote:> I just switched from using the standard "id" column into using my
own
> primary key and generating my own unique id for each record. But, this
> breaks acts_as_tree.
>
> Because my new primary key is not an integer, it breaks the SQL query
> as follows:
>
>
>
> StatementInvalid in PagesController#create
>
--------------------------------------------------------------------------------------
> Mysql::Error: #42S22Unknown column ''1_3'' in
''where clause'': SELECT *
> FROM pages WHERE (parent_id = 1_3 AND account_id = 1) ORDER BY
> position DESC LIMIT 1
>
>
>
>
> Because parent_id is also now a :string column, the 1_3 should be put
> into single quotes like so:
>
> SELECT * FROM pages WHERE (parent_id = ''1_3'' AND
account_id = 1)
> ORDER BY position DESC LIMIT 1
>
> ... but acts_as_tree assumes that the default parent_id column should
> be an integer.
>
> Any ideas on how I can get Rails to treat that parent_id as a string
> instead of an integer?
>
> Jeff
>
Jeff Ward
2006-Jun-19 21:39 UTC
[Rails] Re: Using set_primary_key breaks acts_as_tree with non-integer column
Okay, it was all my fault. Not ActiveRecord''s!
I also had an acts_as_list with a scope defined. That scope thought
that parent_id was an integer.
acts_as_list :scope => ''parent_id = #{self.parent_id} AND account_id
#{self.account_id}''
But, when I changed it to this:
acts_as_list :scope => "parent_id =
''#{self.single_quoted_parent_id}''
AND account_id = #{self.account_id}"
... changing the single quotes to double quotes around the entire
:scope value it still broke. It seems that the scope will only take
the sql statement in single quotes. SO, a hack-around it, I created a
method on the Model to add the single quotes:
acts_as_list :scope => ''parent_id = #{self.single_quoted_parent_id}
AND account_id = #{self.account_id}''
def single_quoted_parent_id
"''#{self.parent_id}''"
end
... it works, but seems messy. What am I missing in my ruby syntax to
get the scope parameters to work without single quotes?
On 6/19/06, Jeff Ward <animikii@gmail.com> wrote:> Okay, a quick update..
>
> I narrowed it down to being where I set my Page''s parent_id
value....
>
> This Is What I Have:
> page.parent_id = params[:id]
>
> Then, I tried this to force those single quotes:
> page.parent_id = "''#{params[:id]}''"
>
> and it inserts the record but includes the single quotes in the pages
> table so in the parent_id column, the value is: ''1_3''
.... but I don''t
> want the single quotes saved!
>
> So, I tried:
> page.parent_id = "#{params[:id]}"
>
> ...and that got me back to the original problem.
>
> I also tried:
> page.parent_id = "#{params[:id].to_s}"
>
> and the same thing....
>
>
> On 6/19/06, Jeff Ward <animikii@gmail.com> wrote:
> > I just switched from using the standard "id" column into
using my own
> > primary key and generating my own unique id for each record. But, this
> > breaks acts_as_tree.
> >
> > Because my new primary key is not an integer, it breaks the SQL query
> > as follows:
> >
> >
> >
> > StatementInvalid in PagesController#create
> >
--------------------------------------------------------------------------------------
> > Mysql::Error: #42S22Unknown column ''1_3'' in
''where clause'': SELECT *
> > FROM pages WHERE (parent_id = 1_3 AND account_id = 1) ORDER BY
> > position DESC LIMIT 1
> >
> >
> >
> >
> > Because parent_id is also now a :string column, the 1_3 should be put
> > into single quotes like so:
> >
> > SELECT * FROM pages WHERE (parent_id = ''1_3'' AND
account_id = 1)
> > ORDER BY position DESC LIMIT 1
> >
> > ... but acts_as_tree assumes that the default parent_id column should
> > be an integer.
> >
> > Any ideas on how I can get Rails to treat that parent_id as a string
> > instead of an integer?
> >
> > Jeff
> >
>
Jeff Ward
2006-Jun-19 21:41 UTC
[Rails] Re: Using set_primary_key breaks acts_as_tree with non-integer column
Um, that should read like this:
But, when I changed it to this:
acts_as_list :scope => "parent_id =
''#{self.parent_id}''
AND account_id = #{self.account_id}"
.... changing the single quotes to double quotes around the entire
:scope value it still broke.
On 6/19/06, Jeff Ward <animikii@gmail.com> wrote:> Okay, it was all my fault. Not ActiveRecord''s!
>
> I also had an acts_as_list with a scope defined. That scope thought
> that parent_id was an integer.
>
> acts_as_list :scope => ''parent_id = #{self.parent_id} AND
account_id > #{self.account_id}''
>
> But, when I changed it to this:
> acts_as_list :scope => "parent_id =
''#{self.single_quoted_parent_id}''
> AND account_id = #{self.account_id}"
>
> ... changing the single quotes to double quotes around the entire
> :scope value it still broke. It seems that the scope will only take
> the sql statement in single quotes. SO, a hack-around it, I created a
> method on the Model to add the single quotes:
>
> acts_as_list :scope => ''parent_id =
#{self.single_quoted_parent_id}
> AND account_id = #{self.account_id}''
>
> def single_quoted_parent_id
> "''#{self.parent_id}''"
> end
>
> ... it works, but seems messy. What am I missing in my ruby syntax to
> get the scope parameters to work without single quotes?
>
>
> On 6/19/06, Jeff Ward <animikii@gmail.com> wrote:
> > Okay, a quick update..
> >
> > I narrowed it down to being where I set my Page''s parent_id
value....
> >
> > This Is What I Have:
> > page.parent_id = params[:id]
> >
> > Then, I tried this to force those single quotes:
> > page.parent_id = "''#{params[:id]}''"
> >
> > and it inserts the record but includes the single quotes in the pages
> > table so in the parent_id column, the value is:
''1_3'' .... but I don''t
> > want the single quotes saved!
> >
> > So, I tried:
> > page.parent_id = "#{params[:id]}"
> >
> > ...and that got me back to the original problem.
> >
> > I also tried:
> > page.parent_id = "#{params[:id].to_s}"
> >
> > and the same thing....
> >
> >
> > On 6/19/06, Jeff Ward <animikii@gmail.com> wrote:
> > > I just switched from using the standard "id" column
into using my own
> > > primary key and generating my own unique id for each record. But,
this
> > > breaks acts_as_tree.
> > >
> > > Because my new primary key is not an integer, it breaks the SQL
query
> > > as follows:
> > >
> > >
> > >
> > > StatementInvalid in PagesController#create
> > >
--------------------------------------------------------------------------------------
> > > Mysql::Error: #42S22Unknown column ''1_3'' in
''where clause'': SELECT *
> > > FROM pages WHERE (parent_id = 1_3 AND account_id = 1) ORDER BY
> > > position DESC LIMIT 1
> > >
> > >
> > >
> > >
> > > Because parent_id is also now a :string column, the 1_3 should be
put
> > > into single quotes like so:
> > >
> > > SELECT * FROM pages WHERE (parent_id = ''1_3''
AND account_id = 1)
> > > ORDER BY position DESC LIMIT 1
> > >
> > > ... but acts_as_tree assumes that the default parent_id column
should
> > > be an integer.
> > >
> > > Any ideas on how I can get Rails to treat that parent_id as a
string
> > > instead of an integer?
> > >
> > > Jeff
> > >
> >
>