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 -~----------~----~----~----~------~----~------~--~---
