How is it possible that SQL query and ActiveRecord.find_by_sql return different results?

Question

I have a conundrum. I am writing a scope on my Account class that finds a bunch of account objects that meet a lengthy condition. Here is my scope:

 scope :unverified_with_no_associations, -> {
  find_by_sql("SELECT COUNT(DISTINCT(accounts.id, accounts.email)) FROM accounts WHERE level = 0 AND id NOT IN
                (SELECT DISTINCT(account_id) FROM verifications) AND id NOT IN 
                (SELECT DISTINCT(account_id) FROM positions) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM edits) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM posts) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM reviews) AND id NOT IN
                (SELECT DISTINCT(sender_id) FROM kudos) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM stacks WHERE account_id IS NOT NULL)")
}

When I execute this scope by with Account.unverified_with_no_associations I receive this object back [#<Account:0x007f7fc94d79c0 id: nil>].

However when I connect to the database and execute the sql as is:

SELECT COUNT(DISTINCT(accounts.id, accounts.email)) FROM accounts WHERE level = 0 AND id NOT IN
                (SELECT DISTINCT(account_id) FROM verifications) AND id NOT IN 
                (SELECT DISTINCT(account_id) FROM positions) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM edits) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM posts) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM reviews) AND id NOT IN
                (SELECT DISTINCT(sender_id) FROM kudos) AND id NOT IN
                (SELECT DISTINCT(account_id) FROM stacks WHERE account_id IS NOT NULL);

I receive the number 221214. Why would it be that I get to differenct results? I've ruled out the possibility of connecting onto different databases. I've checked my .env files and have confirmed that I am in the same database as my application. Does anyone know why I would get such a difference in queries?

------------UPDATE--------

I discovered that find_by_sql does not like the inclusion of COUNT in its argument. When I remove the COUNT() from the sql and later execute a .count method I retrieve the matching number.

However, I still get different results when I use both methods.

What I really need are distinct accounts.id and accounts.email but the methods do not return the same output.

For example, when I execute the sql version I receive an output that looks like this:

row
---------
(1234,[email protected])

but when I use the activerecord version I get this:

[#<Account:0x007fdc9ec104d0 id: nil>]

but with no accompanying email.

----UPDATE #3------

Also on my sql output I get this unknown OID 2249: failed to recognize type of 'row'. It will be treated as String. What does this mean?

rootatdarkstar · Accepted Answer · 2016-06-14 16:01:38Z

2

You have right result in both examples.

If you use only count in select you always got a number as result of query. So your result from database is expectable.

In rails case you trying to get some set of records by scope with count in select statement. It's expectable to got empty set if you have count in your query.

Try count_by_sql method http://apidock.com/rails/ActiveRecord/Base/count_by_sql/class to get number of records instead of empty set.

And use it without scope, but with class method:

def self.unverified_with_no_associations()
  self.count_by_sql("SELECT COUNT(DISTINCT(accounts.id, accounts.email)) FROM accounts WHERE level = 0 AND id NOT IN
            (SELECT DISTINCT(account_id) FROM verifications) AND id NOT IN 
            (SELECT DISTINCT(account_id) FROM positions) AND id NOT IN
            (SELECT DISTINCT(account_id) FROM edits) AND id NOT IN
            (SELECT DISTINCT(account_id) FROM posts) AND id NOT IN
            (SELECT DISTINCT(account_id) FROM reviews) AND id NOT IN
            (SELECT DISTINCT(sender_id) FROM kudos) AND id NOT IN
            (SELECT DISTINCT(account_id) FROM stacks WHERE account_id IS NOT NULL)")
end

edited Jun 14, 2016 at 16:01

answered Jun 14, 2016 at 15:52

rootatdarkstar

1,5363 gold badges15 silver badges27 bronze badges

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

4 Comments

Dan Rubio Over a year ago

rootatdarkstar, thanks for your reply. I didn't know there was a count_by_sql method. Would you know about the problem which I specified on my update. I still don't get matching records.

Dan Rubio Over a year ago

I'll mark your answer as correct and open a new question. Thanks for your help with the count_by_sql.

Dan Rubio Over a year ago

Thanks rootatdarkstar. I'll try your recommendation. I posted another question found here stackoverflow.com/questions/37817143/… if you feel like getting more points today.

lmat - Reinstate Monica Over a year ago

You have count_by_sql...WHERE level = 0.... How would you use prepared parameters for this?

Collectives™ on Stack Overflow

How is it possible that SQL query and ActiveRecord.find_by_sql return different results?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related