#5 ✓resolved
Georg Ledermann

Conditions for date/time columns

Reported by Georg Ledermann | September 30th, 2008 @ 07:48 AM

Something like this would be very helpful:

:conditions => { :created_in_year => "2008" }
:conditions => { :created_in_month => "current" }
:conditions => { :created_in_month => "last" }
:conditions => { :created_in_month => "next" }
:conditions => { :created_in_month => "2008-09" }
:conditions => { :created_in_week => "next" }
:conditions => { :created_in_week => "last" }
:conditions => { :created_in_week => "40" }
:conditions => { :created_on_day => "yesterday" }
:conditions => { :created_on_day => "monday" }

I know, this is already on the todo list (http://github.com/binarylogic/se..., but I just want to note that it would be a very nice feature.

Comments and changes to this ticket

  • Ben Johnson

    Ben Johnson September 30th, 2008 @ 12:57 PM

    Absolutely, those are a little more tricky because not every database retrieves those values the same. Also, I am going to have to change how conditions work a little bit. Nothing too complicated though. I want to treat created_hour just like an integer column. So you can do created_hour_gt, created_hour_before, etc.

    This is where searchgasm gets really cool. Because now we can set up conditions like:

    created_during_evening = true
    created_during_day = true
    created_during_night = true
    created_during_weekend = true

    The list goes on. What is also really cool about this is that every thing is lazy. So we can add a million conditions and it wont change a thing, the conditions only get instantiated when they are needed. So this could become a really useful tool for doing more complicated searches. I just want to make sure I keep this simple, lean, and not bloat it with conditions.

    Anyways, I think I'll have this done by the end of the week. Thanks for letting me know you think this would be helpful. I wasn't sure if it was worth doing all of this.

  • Georg Ledermann

    Georg Ledermann October 1st, 2008 @ 06:41 AM

    This sounds exciting, I can't wait to try this new feature when it's available :)

  • Ben Johnson

    Ben Johnson October 1st, 2008 @ 06:52 PM

    • State changed from “new” to “open”

    I have this pretty much done but came up with an even cooler idea. Here's what I got:

    search.conditions.hour_of_created_at_gt = 10
    search.conditions.minute_of_created_at = 10

    But the reason I switched it up is for other "modifiers" and so you can chain them.

    So we can do

    search.conditions.tan_of_cos_of_sin_of_id_less_than = 10
    search.conditions.floor_of_tan_of_total_gt = 50

    It acts similar to User.find_by_email_and_name(email, name)

    I should have this done tonight or tomorrow. I'll update this ticket when its done. But if you see anything wrong with what I'm doing please let me know so I don't waste my time. Or maybe you have a better syntax?


  • Georg Ledermann

    Georg Ledermann October 2nd, 2008 @ 04:41 AM

    Chaining of modifiers is pretty cool, of course. But what about date modifiers? Perhaps you think about this:

    search.conditions.month_of_created_at = 8
    search.conditions.year_of_created_at = 2008

    But beware: For SQL performance this should NOT result in:

    ... WHERE MONTH(created_at)=8 AND YEAR(created_at)=2008

    This query doesn't use any index (in MySQL, but I think other databases behave the same). Instead, it should result in:

    ... WHERE created_at BETWEEN "2008-08-01 00:00:00" AND "2008-08-31 23:59:59"

    Because of this, I think it would be better to have a combined modifier which is a more accurate representation of the "between range":

      search.conditions.created_at_in_month = "2008-08"
      search.conditions.created_at_in_year = 2008
      search.conditions.created_at_in_week = 40

    (or similar syntax)

  • Ben Johnson

    Ben Johnson October 2nd, 2008 @ 11:08 AM

    You always have the hard problems for me, ha ha. I agree with what you said. I would prefer to treat them all the same, and then have exceptions as the one you listed above. You could ultimately create the between statement by making a good exception.

    The ONLY way this exception would take effect is if the year or a range of years was provided.

    I think for now, I'm going to release it without that, and work on that for the next update. It should be simple though, before I sanitize the conditions down to sql I could loop through the conditions, do a quick check, and modify them if necessary, to create the more efficient statement.

  • Ben Johnson

    Ben Johnson October 2nd, 2008 @ 02:14 PM

    • State changed from “open” to “resolved”

    I released v 1.3.0, so this should be all set. As for your last example, that is on the todo list and plan on working on that in the near future.

  • Georg Ledermann

    Georg Ledermann October 3rd, 2008 @ 04:10 PM

    Nice work! Creating "BETWEEN" as last step optimization seems a good way.

    I found some bugs in 1.3.0, additional ticket is coming...

  • Ben Johnson

    Ben Johnson October 3rd, 2008 @ 04:13 PM

    Awesome, I found some bugs myself. I'm using this in a project that I'm developing right now. Update off the repo...I bet a lot of your bugs are fixed. Thanks!

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.

People watching this ticket