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