sdepold/sequelize — pull request #121 by megshark (2012-01-20 01:38:14+00:00)
Title: Do a join instead of an 'IN' when selecting on many-to-many associations

Performance of 'IN' queries significantly degrades when the number of items in the joined list expands. Also, you shouldn't have to do two queries when querying on a many-to-many association. I changed the has-many-double-linked to perform a join using the specified join table. I use an object for the value as a sentinel that the value is a column reference and not a literal.

I had to change one of the tests to prevent decimals from being used in table names & column names, since I also have to add appropriate tick marks to the key columns in the query generator. Personally, I think this is a reasonable restriction. But, if you prefer always using objects (something like {table: name, column: name}) to specify an association join, I would be OK with changing that. Basically, I was trying to make a clean but non-invasive change since I don't know what your plan is for supporting more complex relational queries in sqlize.

I also made two more small changes:
* be able to specify join table name when defining the many-to-many association (I have an existing schema & did not want to rename my join tables)
* emit a 'sql' event with the generated sql that is about to be executed. This should probably take the place of the logging option, IMO since the user can then decide whether they want to console log for debugging or incorporate the 'sql' into their existing logging (which is what I needed to do)

I added a test for the specified join table name. Existing tests seemed to cover many-to-many associations pretty well since I broke them while I was developing. :) If there are any additional tests you would like to see, let me know & I am happy to add them.

Thanks!
Meg Sharkey