0

I am trying to filter by multiple fields. The fields are taken from an ENUM list and put in checkbox fields, then saved into an array. The GET request for what I'm trying to filter looks like this: {"statuses" => ["active", "past_due"]}. I have a scope with the query, which should filter by the values selected. I am assuming the problem is in the model.

The model:

scope :subscription_status, -> (statuses) {where :subscription_status => statuses}

The controller:

@users = User.where(nil)
@users = @users.subscription_status(params[:statuses]) if params[:statuses].present?

The view (html.haml):

= form_tag root_path, :method => 'get' do
    - @subscription_statuses.each do |status|
        = check_box_tag 'statuses[]', status
        = status
    = submit_tag "Filter", :name => nil

@subscription_statuses contains the ENUM list.

My other filters work on the same principle, but this one doesn't seem to work. The page reloads, the GET params are sent, but there is no filtering.

Thank you in advance. :)

EDIT:

@subscription_statuses = User.subscription_statuses.keys

This is an array of strings, and the values in the database are saved as strings.

Ioana Surdu Bob
  • 139
  • 1
  • 9
  • can you pls output the SQL query generated by you code by sufixing call with `.to_sql` ...e.g. : `User.where(id: [1,2,3]).to_sql` – equivalent8 Jul 08 '16 at 10:40
  • I don't know if I understood your question right, but this is what i generated: User.where(:subscription_status => ["trialing", "past_due"]).to_sql "SELECT `users`.* FROM `users` WHERE `users`.`subscription_status` IN (0, 0)". You are right, there is a problem here, but I don't know why it translates like this. If I try to put [1,2] instead of "statuses" in the query, it shows nothing, as the values are saved as strings... – Ioana Surdu Bob Jul 08 '16 at 10:58

2 Answers2

0

I think you would better try this code in rails console. As it turns out there shouldn't be any problems in your implementation but, taking a look at the generated SQL would be the ideal thing to do. You can use a console debugger like Byebug to help you debug the request and stepping through it.

Pragash
  • 713
  • 7
  • 9
0

A couple of things come to mind. I had this type of problem before, so I can only assume here that the statues you generating here

= check_box_tag 'statuses[]', status

is string.

So when you pass them into the params params[:statuses], it's an array of string, but when we query on enum type, if it's just a single one, you can pass in either sym or string, it will work fine. However that's not the case when you have multiple ones.

where :subscription_status => statuses will generate the sql something like WHERE users.subscription_status IN (0, 0, 0), but all the subscription_statuses are stored as enum type which are integers.

So in your case, you have to find their corresponding integer value first like this

where(subscription_status: User.subscription_status.slice(*statuses).values)

Also you may find this answer helpful as well

UPDATE

Since you already have subscription_status stored as string in the database, declaring the column as enum type in the User model would potentially create a conflict which resulted in not generating desired query. Simple removal of the enum type declaration would possibly resolve the issue.

UPDATE 2

So I messed around with the enum type in rails console. Regarding the problem of query showing up like IN (0, 0, 0) when you pass in an array of strings. It actually shows IN (NULL, NULL, NULL) when you pass in an array of symbols.

My guess is that, since you have declared it as enum type, it wants each status as integer, so it probably did statuses.map(&:to_i) when you try where(subscription_status: statuses) before it generate the query. As a new finding (yay me!) "hello".to_i => 0, while :hello.to_i => undefined method therefore (0, 0, 0) and (NULL, NULL, NULL). (I hope you understand my messy explanation here)

Possibility of mapping enum values to string type instead of integer

Community
  • 1
  • 1
lusketeer
  • 1,890
  • 1
  • 12
  • 29
  • This is not the problem... subscription_status is saved in the database as string, as the database was first created with php/MySQL. Also, I forgot to show the variable: @subscription_statuses = User.subscription_statuses.keys, which contains an array of the strings. – Ioana Surdu Bob Jul 08 '16 at 10:37
  • but you mentioned that `@subscription_statuses contains the ENUM list`, maybe let's see how you define `subscription_status` in the model? – lusketeer Jul 08 '16 at 10:39
  • Model: enum subscription_status: [:active, :trialing, :past_due, :deleted, :canceled] – Ioana Surdu Bob Jul 08 '16 at 10:39
  • in this case, I don't think you need to declare it as enum type if you have already stored them as string. as far as I know, rails doesn't allow enum type column stored as something other than integer ([see this post](http://stackoverflow.com/a/24105977/1301840)), I think if you remove the enum line, it should work with what you already have – lusketeer Jul 08 '16 at 10:44
  • I tried to put the values in the controller instead of enum, same result. – Ioana Surdu Bob Jul 08 '16 at 10:55
  • I think you could create a constant in the model, so you can use it in other places as well, something like `SUB_STATUSES = %w{ active trialing past_due deleted canceled }`, when you need it, just do `User::SUB_STATUSES` – lusketeer Jul 08 '16 at 10:55
  • This assumption: WHERE users.subscription_status IN (0, 0, 0) was right. The problem is that I don't work on a database created with Rails, so inside the tables will be strings, but rails will change the strings into numbers automatically. – Ioana Surdu Bob Jul 08 '16 at 11:01
  • regarding that part, if you try to pass in an array of symbols, it actually will show up as `NULL` instead of `0`, my guess is that since you have declared the column as enum, it's attempting to find it's corresponding integer value, but resulted in wrongfully generating the query, if you really want to use enum type, check out the link I included, the answer recommended some gems that offer feature to allow you to use string instead of integer for enum type – lusketeer Jul 08 '16 at 11:05
  • Thank you for helping. In the meantime, I realised that even the values are printed as strings, they are only 0's in the database. I assume it was a problem with the migration. My boss is in vacation, so I can't ask, but i'll ask how it works and if I can keep the integers later. For now, I just sent the corresponding integers as query params. => -User.subscription_statuses.each do |key,value| = check_box_tag 'statuses[]', value = key – Ioana Surdu Bob Jul 08 '16 at 11:51
  • you're welcome. I thought about suggesting to use value for check_box, key for displaying, but that way, it will appear in your url, don't know if you would like that. good luck! – lusketeer Jul 08 '16 at 12:16