Dirkjan, Excellent news, your commit fixes my problem! I will switch over to using edge DM for my testing, at least until 0.10.1 is released.
Thanks again. ..tony.. On Mon, Sep 28, 2009 at 11:01 AM, Dirkjan Bussink <[email protected]>wrote: > > Hello Tony, > > > Could you try with the current code on github? I've fixed a similar > issue I've been experiencing myself: > > > http://github.com/datamapper/dm-core/commit/5961aa90f068acf79607da21c736c8b011b7f287 > > Hopefully that fixes your issue! > > -- > Regards, > > Dirkjan Bussink > > On 28 Sep 2009, at 18:18, Tony Mann wrote: > > > Ted, > > > > I could not access your gist for some reason, so I made this new one > > with my updated code to repro the issue: > > > > http://gist.github.com/195538 > > > > ..tony.. > > > > On Mon, Sep 28, 2009 at 7:02 AM, Tony Mann <[email protected]> > > wrote: > > Hi Ted. > > > > It turns out that my code was running a more complex query than the > > one I posted, as there was a conditional clause in the query > > generation code that I did not notice. This more complex query is > > the one that breaks DM. Next time I will create a script that repros > > the problem *before* I post :-( > > > > GitHub is down for maintenance at the moment, so I cannot fork your > > gist. I am posting the code below, but I will update the gist as > > soon as I can. > > > > Basically, the query that breaks DM is: > > > > CrawledPage.first('crawling_run.board_account.board_id' => 1, > > 'crawling_run.board_account.recruiter.team_id' => 1) > > > > This results in the errant SQL I posted earlier. > > > > -------------------------------------- > > > > require 'rubygems' > > gem 'dm-core' > > require 'dm-core' > > > > DataMapper::Logger.new(STDOUT, :debug) > > DataMapper.setup(:default, "mysql://localhost/YOUR_TEST_DB") > > > > class CrawledPage > > include DataMapper::Resource > > property :id, Serial > > belongs_to :crawling_run > > end > > > > class CrawlingRun > > include DataMapper::Resource > > property :id, Serial > > belongs_to :board_account > > has n, :crawled_pages > > end > > > > class Board > > include DataMapper::Resource > > property :id, Serial > > end > > > > class BoardAccount > > include DataMapper::Resource > > property :id, Serial > > belongs_to :board > > belongs_to :recruiter > > has n, :crawling_runs > > end > > > > class Recruiter > > include DataMapper::Resource > > property :id, Serial > > belongs_to :team > > has n, :board_accounts > > end > > > > class Team > > include DataMapper::Resource > > property :id, Serial > > end > > > > puts "******** Create tables ********" > > DataMapper.auto_migrate! > > puts "\n\n" > > > > puts "******** Create resources ********" > > board = Board.create > > team = Team.create > > recruiter = Recruiter.create(:team => team) > > board_account = BoardAccount.create(:board => board, :recruiter => > > recruiter) > > crawling_run = CrawlingRun.create(:board_account => board_account) > > crawled_page = CrawledPage.create(:crawling_run => crawling_run) > > puts "\n\n" > > > > puts "******** Query 1 works ********" > > CrawledPage.first('crawling_run.board_account.board_id' => 1) > > puts "\n\n" > > > > puts "******** Query 2 works ********" > > CrawledPage.first('crawling_run.board_account.board_id' => 1, > > 'crawling_run.board_account.recruiter_id' => 1) > > puts "\n\n" > > > > puts "******** Query 3 fails ********" > > CrawledPage.first('crawling_run.board_account.board_id' => 1, > > 'crawling_run.board_account.recruiter.team_id' => 1) > > > > > > > > On Sun, Sep 27, 2009 at 4:15 PM, Tony Mann <[email protected]> > > wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
