I have one table that stores data related to math problems, and

another table that stores users’ answers to those problems. To make

this very simple, we’ll say that the math table has two numbers that

get subtracted, and the answers table has a field that can either be

“pos” or “neg”

math

2 5

8 3

Okay so if we do the math, we know that the correct answer to record 1

(2 -5) is “neg”, and the correct answer to record 2 (8-3) is “pos”.

What I’d like to do is get the user’s answers to these questions, and

determine what % they get correct. I really want to do this in as few

queries as possible though.

Right now I think the easiest way is to just change the table a bit so

it keeps the correct answer. Then I can do two queries:

SELECT COUNT(*) FROM answers WHERE user_id=5;

SELECT COUNT(SELECT answer FROM answers, problems WHERE

answers.answer=problems.answer AND answers.user_id=5));

The first one gets the number of total questions answered, the second

one gets the number of ones correctly answered. The first query - the

second query gives me incorrect answers.

Also I’m not entirely sure that the syntax for #2 is valid, I think it

is though.

First of all, is there a fast way that I can do this without having to

create another field representing the answer? I’m kind of thinking

not…I’d be performing these queries a lot, makes sense just to keep

the answer in the db so I can index it, rather than calculating it

every time. Secondly, can I do this with AR? Don’t know if it can

handle subqueries.

Pat

On 6/12/06, Glenn C. [email protected] wrote:

8 3

SELECT COUNT(*) FROM answers WHERE user_id=5;

(neg) field. Could you explain a little more?

Okay let’s say my app shows (4 - 2) on the page. The users of the app

don’t have to figure out the answer, but simply tell me if the result

is positive or negative.

Pat

Pat M. wrote:

I have one table that stores data related to math problems, and

another table that stores users’ answers to those problems. To make

this very simple, we’ll say that the math table has two numbers that

get subtracted, and the answers table has a field that can either be

“pos” or “neg”

math

2 5

8 3

Okay so if we do the math, we know that the correct answer to record 1

(2 -5) is “neg”, and the correct answer to record 2 (8-3) is “pos”.

What I’d like to do is get the user’s answers to these questions, and

determine what % they get correct. I really want to do this in as few

queries as possible though.

Right now I think the easiest way is to just change the table a bit so

it keeps the correct answer. Then I can do two queries:

SELECT COUNT(*) FROM answers WHERE user_id=5;

SELECT COUNT(SELECT answer FROM answers, problems WHERE

answers.answer=problems.answer AND answers.user_id=5));

The first one gets the number of total questions answered, the second

one gets the number of ones correctly answered. The first query - the

second query gives me incorrect answers.

Also I’m not entirely sure that the syntax for #2 is valid, I think it

is though.

First of all, is there a fast way that I can do this without having to

create another field representing the answer? I’m kind of thinking

not…I’d be performing these queries a lot, makes sense just to keep

the answer in the db so I can index it, rather than calculating it

every time. Secondly, can I do this with AR? Don’t know if it can

handle subqueries.

Pat

Sorry this is a little confusing particularly about having a (pos) or

(neg) field. Could you explain a little more?

If we keep the answer in the problem table then I would try something

like this.

correctly_answered = Answers.find_by_sql ( “select count(a.*) as*

total_answers,

count(p.) as correctly_answered " +

" from answers as a left outer join problems as p on * a.problem_id =

p.id )” +

" where a.answer = p.answer " +

" and a.user_id = ’ + user_id )

if a computed answer is needed then

correctly_answered = Answers.find_by_sql ( “select count(a.*) as*

total_answers,

count(p.) as correctly_answered " +

" from answers as a left outer join problems as p on * a.problem_id =

p.id )” +

" where (a.number_to_subtract_from -

a.number_for_the_amount_to_subtract) = p.answer " +

" and a.user_id = ’ + user_id )

correctly_answered = Answers.find_by_sql ( “select count(a.*) as*

total_answers,

count(p.) as correctly_answered " +

" from answers as a left outer join problems as p on * a.problem_id =

p.id )” +

" where sign(a.number_to_subtract_from -

a.number_for_the_amount_to_subtract) = sign(p.answer) " +

" and a.user_id = ’ + user_id )

Pat M. wrote:

On 6/12/06, Glenn C. [email protected] wrote:

8 3

SELECT COUNT(*) FROM answers WHERE user_id=5;

(neg) field. Could you explain a little more?

Okay let’s say my app shows (4 - 2) on the page. The users of the app

don’t have to figure out the answer, but simply tell me if the result

is positive or negative.

Pat

Assuming you are talking about kids students then in postgresql (i have

no idea about mysql) then you could try the “sign” function

sign(question.from_number - question.subtract_by) =

sign(answer.answer_value)

Where answer.answer_value is 1,-1 (is zero possible?)

check_box(“answer”, “answer_value”, { }, “-1”, “1” )