-1

I can't seem to get this query right. Sequel implementation of:

SELECT count(id) FROM users WHERE username = "alpha" AND status = "new"

Here's what I have so far:

db = Sequel.connect('postgres://me:pw@0.0.0.0:5432/dbname)
u = db[:users]
puts "Total users: #{u.count}"  # this is correct
puts db.where(:username => "alpha", :status => "new").count

I've tried various direct SQL and that doesn't seem to work either. It smells like this is remedial, but the connectivity is fine, and I can replicate the exact SQL which doesn't come back the same.

Phrogz
  • 296,393
  • 112
  • 651
  • 745
Rich_F
  • 1,830
  • 3
  • 24
  • 45

1 Answers1

0

You forgot to select the table. You want this:

db[:users].where(username:'alpha', status:'new').count

For SQLite3, this produces the query:

SELECT count(*) AS 'count'
FROM `users`
WHERE ((`username` = 'alpha')
  AND  (`status` = 'new'))
LIMIT 1

The error message you should have been getting was:

NoMethodError: undefined method `where' for #<Sequel::SQLite::Database: ...>

If you saw this error and read it, it should have let you know that calling db.where was not right.

In addition to making this work with the native Sequel DSL, you can also always run raw sql commands in Sequel.

Phrogz
  • 296,393
  • 112
  • 651
  • 745
  • Second line is the table. I did indeed leave that out of the last line. – Rich_F Oct 19 '16 at 00:12
  • @Rich_F That's frustrating. Please copy paste exact code, don't type on the fly. Please edit your question with your actual code, and the actual error you get. – Phrogz Oct 19 '16 at 00:15
  • That is the pasted text. I tried many variations and none of them worked. I don't retype my code in here. – Rich_F Oct 19 '16 at 00:19
  • 1) That can't be the actual code. The first line has a syntax error. 2) You say that your tried it with the table, but your code doesn't show that. 3) You still haven't showed the error you are getting, or what you expected and what you got instead. Saying it "doesn't work" is not enough information for us to be able to do more than guess at the problem. – Phrogz Oct 19 '16 at 00:22
  • Also, what "various direct SQL" did you try, via what code, and what was the error or incorrect result that came out the other side? – Phrogz Oct 19 '16 at 00:27
  • 1. errors are why I posted. After several trials, I couldn't get it working. My question was about "here's what I'm left with, can this be done?". 2. My code indeed has issues. It's why I posted. 3. I know. I found it irrelevant, as I was asking if it could be accomplished using Sequel. 4. Postico SQL client. In the end, I moved to the pg gem and everything is wonderful again. – Rich_F Oct 19 '16 at 07:56
  • Glad you found a solution you are happy with. Know that this is easily done with Sequel. For future questions, please remember to include a minimal working test case showing the problem, a description of what you expected, and what actually happened instead. If you are not interested in solving this question, please delete it. If you are still curious, please edit the question. – Phrogz Oct 19 '16 at 13:00
  • Oh, and note that you can run arbitrary SQL using Sequel. See this URL, which I've added to my answer: http://sequel.jeremyevans.net/rdoc/files/doc/sql_rdoc.html – Phrogz Oct 19 '16 at 13:03
  • I'm going to keep it as it does provide some leadership. Thanks for the answers. – Rich_F Oct 19 '16 at 15:16