More efficient exists? method

[writercoder]

The exists? method loads the whole row from the files table, including the file blob. This is very slow for us as the files data is encrypted. It would help to have exists? - or another method - using a more efficient query.

[jarl-dk]

I am very open for pull requests if you have any proposals.

[danbt79]

This does need looking at I think. Any gut feelings on if this is going to be very hard to patch @jarl-dk?

[jarl-dk]

What version of Rails (AR) are you considering?

[danbt79]

We've not moved to 4 just yet, but we are in the process. I take it there are significant differences between the two?

[jarl-dk]

My first guess would be that AR 4 contains an efficient exists? method out of the box, I will investigate...

[danbt79]

Do let us know how you get on - we're setup here to be able to 'trigger' this bug, and do want to help resolve it!

If moving to Rails 4 fixes it, that would be awesome. If it looks like it might, I'll see how much work would be involved in getting us up on Rails 4 enough to at least spin up 'rails s' locally and see if we can replicate the issue.

[jarl-dk]

Would you please mind trying out this branch:
https://github.com/softace/paperclip_database/tree/improve_exists

As far as I can see the #exists? was just sloppy implemented and has nothing to do with Rails 3 VS Rails 4.

[writercoder]

I've tried out the branch. The query generated doesn't seem to be correct. We have a Document model with a an attachment called file. Trying the exists? method in the console results in the following:

1.9.3p194 :004 > doc.file.exists?
  DocumentFilePaperclipFile Exists (0.5ms)  SELECT 1 AS one FROM `document_files` WHERE `document_files`.`document_id` = 3240 AND `document_files`.`id` = 0 LIMIT 1
 => false

The problem seems to be the document_files.id = 0 subclause.

[jarl-dk]

Thanks a lot for spending some time trying it out. (I should really write a test for this feature).

Can you verify you have a model like (please specify details about your :styles option.)

class Document < ActiveRecord::Base
  has_attached_file :file,
                    :storage => :database,
                    :database_table => :document_files
end

Then somewher in your code:

doc = Document.find(3240)
doc.files.exists?

right?

If so you should be aware of the line

doc.files.exists?

is a shortcut for

doc.files.exists?(:normal)

which means that it check existence for this particular style.

If you want to check if doc has a file attachment, then it is a paperclip thing and should be possible with doc.files.nil?

If you still believe that the SQL is wrong, please explain.

Jarl

[writercoder]

Hi Jarl,

The model definition is like this

class Document < ActiveRecord::Base
  has_attached_file(
    :file,
    :styles => {:original => {:strength => :full}},
    :storage => :database,
    :database_table => 'document_files',
    # This is turned off as it does not seem neccessary and seems
    # to prevent documents from being destroyed.
    :cascade_deletion => false,
    :processors => [:file_encrypt]
  )
end

In the console we have

doc = Document.find(3240)
doc.file.exists?
[paperclip] Database Storage Initalized.
  DocumentFilePaperclipFile Exists (0.4ms)  SELECT 1 AS one FROM `document_files` WHERE `document_files`.`document_id` = 3240 AND `document_files`.`id` = 0 LIMIT 1
 => false
doc.file.exists? :original # Should be true
DocumentFilePaperclipFile Exists (0.3ms)  SELECT 1 AS one FROM `document_files` WHERE `document_files`.`document_id` = 3240 AND `document_files`.`id` = 0 LIMIT 1
 => false

I don't see why there is a sub-clause in both queries that says

 AND `document_files`.`id` = 0

I can't see how this would ever be true and I don't understand why it is generated.

Hope this helps,

Richard

[jarl-dk]

Thanks for feedback. I'll dig into the details...

[jarl-dk]

Try out the latest branch head, this should work now, the test also covers the case now....

Thanks a lot for the report.

[jarl-dk]

Just released version 2.2.1 with the fix for this issue included.