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