The following Sequel code produces a query which seems reasonable and
produces the data I expect, if I execute it directly as a mysql
command. However the dataset result gives me some columns with the
column names inserted where the data should be. These columns happen
to be those which were disambiguated in a select call.
qn = Question.table_name
# get all the questions in reverse chronological order
quests_ds =
Question.dataset.select("#{qn}.id".as(:qid),:question,:background,"#{qn}.created_at").order("#{qn}.created_at").reverse
# join the questions with the user's answers to those questions
# keeping all unanswered questions (left outer join)
quest_ans_ds = quests_ds.join_table(:left_outer, Answer,
[[:question_id, :id], [:user_id, @user.id]])
# get all current (not retired answers) from the database
@questions_with_answered =
quest_ans_ds.filter(:retired_at=>nil).all
This gives:
SELECT 'questions.id' AS `qid`, `question`, `background`,
'questions.created_at' FROM `questions` LEFT OUTER JOIN `answers` ON
((`answers`.`question_id` = `questions`.`id`) AND (`answers`.`user_id`
= 1)) WHERE (`retired_at` IS NULL) ORDER BY 'questions.created_at'
DESC
questions_with_answered: [#<Question
@values={:"questions.created_at"=>"questions.created_at", :qid=>"questions.id",
:background=>"There
is an old saying: 'It's the economy, stupid!'", :question=>"Should the
President stay focused on the Economy during an election year?"}
>, ......
Whereas a direct query gives:
mysql> select questions.id as qid, question, background,
questions.created_at from questions left outer join answers on
((answers.question_id = questions.id) and (answers.user_id = 1)) where
(retired_at is null) order by questions.created_at DESC;
+-----
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+---------------------+
| qid |
question
|
background
| created_at |
+-----
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+---------------------+
| 1 | Should the President stay focused on the Economy during an
election
year?
| There is an old saying: 'It's the economy,
stupid!'
| 2011-06-28 19:12:11 |
You can see qid=>1 here and :qid=>"question.id" in the dataset result.
Any clues how to fix this?
Mike
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
