Rails SQL commands got error

Question

I have some tables: users, posts, translations, vocabularies a user can create posts, make translations for words on posts and can mark translations of others as vocabularies for review. table vocabularies only has 2 columns (user_id,translation_id) In controller I declare variable:

@translations = @post.translations.where("translations.words=?", params[:words], params[:end]).joins("LEFT OUTER JOIN vocabularies ON vocabularies.translation_id = translations.id AND vocabularies.user_id = 6").select("translations.*")

And get errors

SQLite3::SQLException: near "*": syntax error: SELECT COUNT(translations.*) FROM "translations" LEFT OUTER JOIN vocabularies ON vocabularies.translation_id = translations.id AND vocabularies.user_id = 6 WHERE "translations"."post_id" = ? AND (translations.words='hello')

One more question, because the :translations and :vocabularies both have the same column user_id, so how can alias user_id of :vocabularies as learner_id? I tried to run this command but it still got error: SQLite3::SQLException: near "as":

@translations = @post.translations.where("translations.words=?", params[:words], params[:end]).joins("LEFT OUTER JOIN vocabularies ON vocabularies.translation_id = translations.id AND vocabularies.user_id = 6").select("translations.*, vocabularies.user_id as learner_id")

Please help me out. Thank you.

I think you can reduce the query to something like this @post.translations.joins(:vocabularies).where(words: params[:words] + params[:end], vocabularies: {user_id: 6}) and add the select at the end — Mohammad AbuShady
– Mohammad AbuShady, Commented Sep 7, 2014 at 14:13
I think I'm OK with joins command but I had trouble with select commands, it got error SQLException: near "*": syntax error — Tai Nguyen
– Tai Nguyen, Commented Sep 8, 2014 at 5:08

RAJ · Accepted Answer · 2014-09-08 14:50:56Z

1

You need to construct your where statement like:

where("translations.words = ? OR translations.words = ?", params[:words], params[:end])

So your query will become:

@translations = @post.translations.where("translations.words = ? OR translations.words = ?", params[:words], params[:end]).joins("LEFT OUTER JOIN vocabularies ON vocabularies.translation_id = translations.id AND vocabularies.user_id = 6").select("translations.*, vocabularies.user_id learner_id")

edited Sep 8, 2014 at 14:50

answered Sep 7, 2014 at 13:55

RAJ

9,7471 gold badge36 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tai Nguyen Over a year ago

I changed but it get errors SQLite3::SQLException: near "*": syntax error

@post.translations.joins(:vocabularies).where("translations.start=? AND translations.end=?", params[:start], params[:end]).select("translations.*","vocabularies.user_id AS learner_id")

Tai Nguyen Over a year ago

Dear RAJ, I tried both, even with select("translations.*") and it still got error SQLException: near "*": syntax error: SELECT COUNT(translations.*, vocabularies.user_id learner_id)

Tai Nguyen Over a year ago

everything was fine until I add select command, it works with .select("translations.id") but fails with `.select("translations.*")

Tai Nguyen Over a year ago

Well, I think the problem is when i add an option .select(), it refers to COUNT() command in SQL

Tai Nguyen Over a year ago

I'm sorry, it seems to be an error because after that, I add a command @translations.any? and if I remove this, everything works perfect, thanks for helping me

Collectives™ on Stack Overflow

Rails SQL commands got error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related