#422 invalid
Troy K

Conditions are not in key order

Reported by Troy K | June 27th, 2008 @ 07:42 PM

Most RDBMS require the where clause to match index order to utilize the index. I ran into the following problem, which is more apparent for CPK's, but exists for any key used with other conditions as well. 

class Relation
  include DataMapper::Resource
  property :id, Integer, :key => true
  property :type, Class, :key => true
  property :to_id, Integer, :key => true
  property :to_type, Class, :key => true
end

>> Relation.get(1,Person,5,Note)

generates the following sql: 

SELECT `id`, `type`, `to_id`, `to_type` FROM `relations` WHERE `to_type` = 'Note' AND `id` = 1 AND `type` = 'Person' AND `to_id` = 5 ORDER BY `id`, `type`, `to_id`, `to_type` LIMIT 1

I've uploaded a patch that results in the following sql: 

SELECT `id`, `type`, `to_id`, `to_type` FROM `relations` WHERE `id` = 1 AND `type` = 'Person' AND `to_id` = 5 AND `to_type` = 'Note' ORDER BY `id`, `type`, `to_id`, `to_type` LIMIT 1

Comments and changes to this ticket

  • Sam Smoot

    Sam Smoot July 1st, 2008 @ 10:16 AM

    • State changed from “new” to “invalid”

    Troy:

    We're actually going to be changing up the syntax a bit, which should give you a bit more control over conditions order as we'll use Arrays instead.

    So for now, consider this rejected. In a friendly way. :-)

    I absolutely agree that order is important of course though. If you feel it's really urgent to integrate this, or you'd like to get in touch to discuss the syntax change I mentioned, feel free to drop me a note.

  • Troy K

    Troy K July 2nd, 2008 @ 09:13 PM

    I have limited access to the net this week, but I'd love to discuss this some more when I get back next week. I do think that it is in dm's best interest to make sure that if a key is used explicitly or implicitly that it is ordered before the other conditions. I do see where you are going with the new syntax, as it should not be dm's job to determine proper order based on index definitions, however I think it would be proper and intelligent design to give special consideration the primary keys.

    To give you some examples of where this is currently broken, and food for thought as you consider the new syntax, is CPK's and STI. Watching the sql that dm produces for STI, the discriminator is placed before the key when using resource.get. And from looking at the code it would seem this could also be the case for associations with conditions, although it looks good in my sql log, I think this is just coincidence. For example: 

    class Person < Contact
  has n, :relations, :child_key => [:id], :type=>self
end

    So bottom line, I think a syntax change allowing arrays in conditions to control order is cool, but how does the key get priority when I do: 

    >> Person.get(1) 
and 
>> Person.get(1).relations

    where I'm not really specifying conditions, but multiple conditions exist because I'm using STI and/or conditions on my associations.

    I'm really looking forward to discussing this in more detail when I get back, is IRC the best channel to meet up or should I drop you a message on the side, or just use this ticket?

    Thanks, and I'm really excited about dm and it's potential.

    -- troy

    For

    It also breaks (I mean does not take advantage of the index that is assumed to exist with a PK) when conditions are used with associations

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 »

People watching this ticket

Attachments

Tags

Pages