Hi Ted. Thanks for the response, and for trying to repro my problem. BoardAccount "belongs_to" Board, so there is indeed a "board_id" column in board_accounts. If you ran into an SQL error saying that there was no board_id, then you actually did repro my problem, at least in part.
I will fork your gist to get a better repro case for you to look at. Once I do that, we can diagnose more readily. ..tony.. On Sun, Sep 27, 2009 at 8:57 AM, Ted Han <[email protected]> wrote: > Tony, > I can't get DM 0.10 to replicate the bug that you're dealing with. > > In fact, knowing how the many-to-many mapping works, i am really mystified > how you could have possibly ended up with that query. DM's M2M (sigh for > overloaded acronyms) literally walks up the chain of relationships from the > origin class the query is built for, through the chain of relationships to > the destination point (this is something i know and care about, and have > harassed dkubb over the development and release of 0.10). > > Additionally, the query you've provided us with is invalid because there is > no `board_id` column on the `board_accounts` table based on the models you > gave us. Given the SQL that you've produced i can only presume that you > either wish to have a query like this: > > CrawledPage.count('crawling_run.board_account.recruiter.id' => 'board1') > > which produces the following sql (correctly) > > SELECT COUNT(*) FROM `crawled_pages` > INNER JOIN `crawling_runs` ON `crawled_pages`.`crawling_run_id` = > `crawling_runs`.`id` > INNER JOIN `board_accounts` ON `crawling_runs`.`board_account_id` = > `board_accounts`.`id` > INNER JOIN `recruiters` ON `board_accounts`.`recruiter_id` = > `recruiters`.`id` > WHERE `recruiters`.`id` = 'board1' > > OR > > CrawledPage.count('crawling_run.board_account.id' => 'board1') > > which correctly produces: > > SELECT COUNT(*) FROM `crawled_pages` > INNER JOIN `crawling_runs` ON `crawled_pages`.`crawling_run_id` = > `crawling_runs`.`id` > INNER JOIN `board_accounts` ON `crawling_runs`.`board_account_id` = > `board_accounts`.`id` > WHERE `board_accounts`.`id` = 'board1' > > Could you clarify what the problem is? (i've gisted what i'm using to > replicate: http://gist.github.com/194831 feel free to fork and email back) > > -T > > (P.S. the SQL join generation code is in > lib/dm-core/adapters/data_objects_adapter in #join_statement. You'll have > to spelunk the relationships code to ensure that the Path that is being > traversed has your relationships in the correct order. Since the queries i > wrote executed successfully, i don't think that's the issue though, which is > why i need clarification regarding your intent.) > > On Sun, Sep 27, 2009 at 10:18 AM, Tony Mann <[email protected]> wrote: > >> This bug is actually a showstopper for me. Until it is fixed, I cannot >> migrate our project to DM 0.10. Does anyone on this list have some >> suggestions on how this should be fixed? I am willing to dive in cold, but >> having a few pointers up front would be great. >> Thanks. >> >> ..tony.. >> >> >> On Fri, Sep 25, 2009 at 2:16 PM, Tony Mann <[email protected]> wrote: >> >>> First, the quick synopsis. Datamapper is generating this query: >>> SELECT COUNT(*) FROM `crawled_pages` >>> INNER JOIN `recruiters` ON `board_accounts`.`recruiter_id` = >>> `recruiters`.`id` >>> INNER JOIN `crawling_runs` ON `crawled_pages`.`crawling_run_id` = >>> `crawling_runs`.`id` >>> INNER JOIN `board_accounts` ON `crawling_runs`.`board_account_id` = >>> `board_accounts`.`id` >>> WHERE `board_accounts`.`board_id` = 'board1' >>> >>> MySQL is not happy with this, since the INNER JOINs are in the wrong >>> order. If I change the order of the JOINs around, the query works: >>> >>> SELECT COUNT(*) FROM `crawled_pages` >>> INNER JOIN `crawling_runs` ON `crawled_pages`.`crawling_run_id` = >>> `crawling_runs`.`id` >>> INNER JOIN `board_accounts` ON `crawling_runs`.`board_account_id` = >>> `board_accounts`.`id` >>> INNER JOIN `recruiters` ON `board_accounts`.`recruiter_id` = >>> `recruiters`.`id` >>> WHERE `board_accounts`.`board_id` = 'board1' >>> >>> This has nothing to do with dm-aggregate, as I can repro the problem >>> using Model.all. >>> >>> Here is the code that generates the errant query: >>> >>> CrawledPage.count('crawling_run.board_account.board_id' => 'board1') >>> >>> And here are the models (extraneous code removed): >>> >>> class CrawledPage >>> property :id, Serial >>> belongs_to :crawling_run >>> end >>> >>> class CrawlingRun >>> property :id, Serial >>> belongs_to :board_account >>> has n, :crawled_pages >>> end >>> >>> class BoardAccount >>> property :id, Serial >>> belongs_to :board >>> belongs_to :recruiter, :nullable => true >>> has n, :crawling_runs >>> end >>> >>> class Recruiter >>> property :id, Serial >>> has n, :board_accounts >>> end >>> >>> Anyone know if this has been fixed in 0.10.1 or filed as a ticket? If I >>> want to fix this, any suggestions where to dive in? >>> >>> Thanks. >>> >>> ..tony.. >>> >> >> >> >> > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "DataMapper" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/datamapper?hl=en -~----------~----~----~----~------~----~------~--~---
