Hey all,
I feel like this is a dumb questions or a flaw in my design principles.
If that is the case, feel free to berate me.
I have a table of questions defined, using PostgreSQL, as:
CREATE TABLE questions(
id SERIAL,
parent_question_id INT4,
article_id INT4,
question_type_id INT4,
text TEXT NOT NULL,
created_at TIMESTAMP,
updated_at TIMESTAMP,
CONSTRAINT fk_article_id FOREIGN KEY(article_id) REFERENCES articles(id),
CONSTRAINT fk_question_type_id FOREIGN KEY(question_type_id)
REFERENCES question_types(id),
CONSTRAINT fk_parent_question_id FOREIGN KEY(parent_question_id)
REFERENCES questions(id),
CONSTRAINT pk_questions_id PRIMARY KEY(id)
);
The Question model is defined as:
class Question < ActiveRecord::Base
validates_presence_of :text
validates_uniqueness_of :text, :scope => "article_id"
belongs_to :article
belongs_to :question_type
belongs_to :parent_question,
:class_name => "Question",
:foreign_key => "parent_question_id"
has_many :child_questions,
:class_name => "Question",
:foreign_key => "parent_question_id"
end
I want to be able to find all parent questions; ie to find all rows
where parent_question_id is null.
I first started with:
parent_questions = @article.questions.find( :all, :conditions =>
"parent_question_id = null")
This, however, fails because any SQL evaluation involving null is null.
1) Is there a way to do the find to get the parent questions as the
table structure is now?
2) If I were going to restructure the table/data, what would you
reccomend? I considered putting parents in one table and children in a
second table, but that would necessarily limit the relationship depth to
two (one parent -> child) where I think the current method would allow
for multilayered relationship (parent -> child -> child...)
3) If you see anything else wrong with my approach here, please let me know!
Thanks for your help,
Jeff Casimir
> I first started with: > parent_questions = @article.questions.find( :all, :conditions => > "parent_question_id = null")Try this: parent_questions = @article.questions.find(:all, :conditions => ''parent_question_id is null'') -- rick techno-weenie.net
On 7/15/05, Jeff Casimir <jeff-+RlNNtFrnNmT15sufhRIGw@public.gmane.org> wrote:> parent_questions = @article.questions.find( :all, :conditions => > "parent_question_id = null") > > This, however, fails because any SQL evaluation involving null is null."parent_question_id is null" ''is null'' and ''is not null'' are two operations that work nicely with null. and they are about the only two... Jason
Thanks Jason and Rick...running like a charm. -Jeff Jason Foreman wrote:>On 7/15/05, Jeff Casimir <jeff-+RlNNtFrnNmT15sufhRIGw@public.gmane.org> wrote: > > >> parent_questions = @article.questions.find( :all, :conditions => >>"parent_question_id = null") >> >>This, however, fails because any SQL evaluation involving null is null. >> >> > > >"parent_question_id is null" > >''is null'' and ''is not null'' are two operations that work nicely with >null. and they are about the only two... > > >Jason >_______________________________________________ >Rails mailing list >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >lists.rubyonrails.org/mailman/listinfo/rails > >
Jeff Casimir wrote: [snip]> I first started with: > parent_questions = @article.questions.find( :all, :conditions => > "parent_question_id = null") > > This, however, fails because any SQL evaluation involving null is null.Try "parent_question_id is null" instead of "parent_question_id = null". Jim -- Jim Menard, jimm-Xhj3G7Rj6JI@public.gmane.org, io.com/~jimm Dash dash space newline Four-line witty quotation Perfect message end -- Donald Welsh in rec.humor.oracle.d
While the IS NULL syntax is correct, PostgreSQL has an option for dealing with legacy queries that are written as = NULL. Basically you set ''transform_null_equals'' to true and all those queries will work. See postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-COMPATIBLE for details :) Cheers, Dan On 7/16/05, Jim Menard <jimm-Xhj3G7Rj6JI@public.gmane.org> wrote:> Jeff Casimir wrote: > [snip] > > I first started with: > > parent_questions = @article.questions.find( :all, :conditions => > > "parent_question_id = null") > > > > This, however, fails because any SQL evaluation involving null is null. > > Try "parent_question_id is null" instead of "parent_question_id = null". > > Jim > -- > Jim Menard, jimm-Xhj3G7Rj6JI@public.gmane.org, io.com/~jimm > Dash dash space newline > Four-line witty quotation > Perfect message end > -- Donald Welsh in rec.humor.oracle.d > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > lists.rubyonrails.org/mailman/listinfo/rails >
Once again reminding me of the quote "PostgreSQL: All the features you need tomorrow, yesterday." As opposed to...well...I won''t stoke those coals at the moment :) Thanks for the tip, Jeff Dan Sketcher wrote:>While the IS NULL syntax is correct, PostgreSQL has an option for >dealing with legacy queries that are written as = NULL. Basically you >set ''transform_null_equals'' to true and all those queries will work. >See postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-COMPATIBLE >for details :) > >Cheers, >Dan > >On 7/16/05, Jim Menard <jimm-Xhj3G7Rj6JI@public.gmane.org> wrote: > > >>Jeff Casimir wrote: >>[snip] >> >> >>>I first started with: >>> parent_questions = @article.questions.find( :all, :conditions => >>>"parent_question_id = null") >>> >>>This, however, fails because any SQL evaluation involving null is null. >>> >>> >>Try "parent_question_id is null" instead of "parent_question_id = null". >> >>Jim >>-- >>Jim Menard, jimm-Xhj3G7Rj6JI@public.gmane.org, io.com/~jimm >>Dash dash space newline >>Four-line witty quotation >>Perfect message end >> -- Donald Welsh in rec.humor.oracle.d >>_______________________________________________ >>Rails mailing list >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>lists.rubyonrails.org/mailman/listinfo/rails >> >> >> >_______________________________________________ >Rails mailing list >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >lists.rubyonrails.org/mailman/listinfo/rails > >
On Jul 15, 2005, at 4:02 PM, Dan Sketcher wrote:> While the IS NULL syntax is correct, PostgreSQL has an option for > dealing with legacy queries that are written as = NULL. Basically you > set ''transform_null_equals'' to true and all those queries will work. > See postgresql.org/docs/8.0/static/runtime- > config.html#RUNTIME-CONFIG-COMPATIBLE > for details :) > > Cheers, > Dan > > On 7/16/05, Jim Menard <jimm-Xhj3G7Rj6JI@public.gmane.org> wrote: > >> Jeff Casimir wrote: >> [snip] >> >>> I first started with: >>> parent_questions = @article.questions.find( :all, :conditions => >>> "parent_question_id = null") >>> >>> This, however, fails because any SQL evaluation involving null is >>> null. >>> >> >> Try "parent_question_id is null" instead of "parent_question_id = >> null".Active Record has a private method to help determine whether to use IS, IN, or = depending on the argument type. You can use a similar approach to define your conditions: def attribute_condition(name, value) name = connection.quote_column_name(name) if value.nil? "#{name} IS ?" elsif value.respond_to?(:each) "#{name} IN (?)" else "#{name} = ?" end end parents = @article.questions.find(:all, :conditions => attribute_condition(''parent_question_id'', nil)) Associations should have dynamic finder methods to relieve this tedium: parents = @article.questions.find_by_parent_question_id(nil) Regardless, this operation is better encapsulated in your Article model: class Article < ActiveRecord::Base has_many :questions has_many :parent_questions, :class_name => ''Question'', :conditions => ''parent_question_id IS NULL'' end parents = @article.parent_questions Best, jeremy