postgresql - How to make Rails/ActiveRecord return unique objects using join table's boolean column -

i have rails 4 app using activerecord , postgresql 2 tables: stores , open_hours. store has many open_hours:

stores:

       column       |  --------------------+  id                 |   name               |  

open_hours:

    column       | -----------------+  id              |  open_time       |  close_time      |  store_id        | 

the open_time , close_time columns represent number of seconds since midnight of sunday (i.e. beginning of week).

i list of store objects ordered whether store open or not, stores open ranked ahead of stores closed. query in rails:

store.joins(:open_hours).order("#{current_time} > open_time , #{current_time} < close_time desc") 

notes current_time in number of seconds since midnight on previous sunday.

this gives me list of stores open stores ranked ahead of closed ones. however, i'm getting lot of duplicates in result.

i tried using distinct, uniq , group methods, none of them work:

store.joins(:open_hours).group("stores.id").group("open_hours.open_time").group("open_hours.close_time").order("#{current_time} > open_time , #{current_time} < close_time desc") 

i've read lot of questions/answers on stackoverflow of them don't address order method. this question seems relevant 1 max aggregate function not work on booleans.

would appreciate help! thanks.

here did solve issue:

in rails:

is_open = "bool_or(#{current_time} > open_time , #{current_time} < close_time)"  store.select("stores.*, case when #{is_open} 1 when #{is_open} null 2 else 3 end open").group("stores.id").joins("left join open_hours on open_hours.store_id = stores.id").uniq.order("open asc") 

explanation:

• the is_open variable there shorten select statement.
• the bool_or aggregate function needed here group open_hours records. otherwise there 2 results each store (one open , 1 closed), why using uniq method alone doesn't eliminate duplicate issues
• left join used instead of inner join can include stores don't have open_hours objects
• the store can open (i.e. true), closed (i.e. false) or not determined (i.e. nil), case when statement needed here: if store open, it's 1, 2 if not determined , 3 if closed
• ordering results asc show open stores first, not determined ones, closed stores.

this solution works doesn't feel elegant. please post answer if have better solution. lot!