SQL Inner JOIN with query

Question

I am trying to do something like this but I am not sure if you can do this with a inner join:

SELECT "scores".* 
FROM "scores" 
    INNER JOIN "games" ON "games"."id" = "scores"."games_id" 
WHERE 
    "games"."date" >= '2013-02-11 19:30:11.799227' 
AND "scores".value > 350;

Thanks for the help. Also, if you know how to write a ActiveRecord / arel statement the would give the query that would be helpful as well.

My models look like this:

class Score < ActiveRecord::Base
  belongs_to :game
  delegates :date, to: game
end

class Game < ActiveRecord::Base
  has_many :scores, dependent: :destroy
end

The SQL syntax is just fine, and should work correctly. Did you test it? — Michael Berkowski, Mar 11 '13 at 19:42
@DavidW, he is talking about about relational algebra queries commonly used with ruby on rails — rs., Mar 11 '13 at 19:59
Note that it's usually best to put as many relevant relations as possible in a `JOIN` clause - depending on your database, this may be automatically performed in the case of `INNER JOIN`s (sort-of). Among other things, it makes `LEFT JOIN`s clear. — Clockwork-Muse, Mar 11 '13 at 20:28
@MichaelBerkowski When I run the above statement it says `ERROR: relation 'games' does not exist` — trev9065, Mar 11 '13 at 20:31

score 0 · Answer 1 · answered Mar 11 '13 at 20:19

0

An ActiveRecord query would look like this:

some_date = DateTime.parse('2013-02-11 19:30:11.799227')
score = 350
Score.joins(:game).
      where("games.date >= ? AND scores.value > ?", some_date, score)

This assumes that you have belongs_to :game in your Score model.

answered Mar 11 '13 at 20:19

PinnyM

35,165
3
73
81

This does not seem to work, I get the error `PG::Error Error: missing FROM-clause entry for table 'games' – trev9065 Mar 11 '13 at 20:30
@trev9065: Do you have a `Game` model? And what is `Workout`? – PinnyM Mar 11 '13 at 20:36
Sorry that was a typo, it is a Game model. – trev9065 Mar 11 '13 at 20:43
@trev9065: Can you add `.to_sql` to the end of the ActiveRecord query and post the output? And did you run your migration that creates your 'games' table? – PinnyM Mar 11 '13 at 20:52
Yes I am sure I have the table because I already have other relations built on the table and it is only this one method that is giving me errors. Here is the output: `"SELECT \"scores\".* FROM \"scores\" INNER JOIN \"games\" ON \"games\".\"id\" = \"scores\".\"game_id\" WHERE (games.date >= '2013-02-11 21:16:13.824959' AND scores.value > 350)"` – trev9065 Mar 11 '13 at 21:17
So the query generated is the one you were looking for. The error message `relation 'games' does not exist` seems to indicate that it can't find this table - can you double check using PGAdmin? – PinnyM Mar 11 '13 at 21:21
Double checked \d gives me scores and games tables, the scores table has the correct columns (game_id and value) and the games table has a date column. – trev9065 Mar 11 '13 at 21:25
Can you check that this database is the same as your console (or server environment)? That is, you aren't looking at your development db, while running the code against your test db... Besides that the errors are inconsistent (`missing FROM-clause` vs `relations 'games' does not exist`), the second error seems to indicate that the database can't find the table. – PinnyM Mar 13 '13 at 14:19

SQL Inner JOIN with query

1 Answers1