Jared, Looks like a bug - but, can you lay out the the basic config for me, so I can see the object relationships, as I think there are a couple of m2m relationships in there.
Mark On Thu, Nov 27, 2008 at 7:11 AM, Jared Rypka-Hauer <[EMAIL PROTECTED]> wrote: > > I found a transfer bug yesterday, but at the time I wasn't sure what > was going on. > > Now I know, and I shall share, and gather comments, and if there's a > bug tracker I'll prolly file a defect. :D Never done that with > Transfer before so I dunno where the bugbase even is. I shall look > for it. > > In any case, given this block of TQL: > > SELECT > c.ID AS CompanyID, > a.ID AS AuctionID, > i.ID AS InvoiceID, > p.ID AS PaymentID, > pm.ID as PaymentMethodID, > * > FROM company AS c > JOIN auction AS a > JOIN invoice AS i > JOIN payment AS p > JOIN paymentMethod > WHERE > c.ID = :companyID > AND p.deleted != p.refunded > AND p.deleted = :deleted > > AND... here's the gotcha... given the fact that both company and > payment have a relationship with paymentMethod, when it generates SQL > it doesn't do a very good job of creating the join statements. Maybe > this is because Company>paymentMethod is a m2m and > payment>paymentMethod is a o2m... but it don't work no matter what. > It creates the first one it finds just fine (company > > xCompanyPaymentMethods > paymentMethod), but it only partially > generates the join statement for the second table > (payment.paymentMethodID = paymentMethod.ID). > > The SQL that it generates looks like this: > > FROM Company c > inner join Auction a ON c.ID = a.CompanyID > inner join Invoice i ON a.ID = i.auctionID > inner join xInvoicePayments on i.ID = > xInvoicePayments.invoiceID > inner join payment p ON > xInvoicePayments.paymentID = p.ID > inner join xCompanyPaymentMethods on > c.ID = > xCompanyPaymentMethods.CompanyID > inner join paymentMethod pm ON > xCompanyPaymentMethods.paymentMethodID = pm.ID > ON p.paymentMethodID = pm.ID > > If you look at the last 2 lines you'll see what I mean. It generates > the whole statement for company to xCompanyPaymentMethods to > PaymentMethod, butu it only generates the last part of the line for > payment>paymentMethod. Personally I think this is a bug because I may > WANT it to execute those 2 joins and if so it won't work... it needs > to generate the whole statement for both relationships. > > Currently I'm trying to work around this using an explicit ON > statement for the paymentMethod join, but I'm having issues because > Payment.paymentMethodID doesn't exist, there's a o2m there instead. I > can't quite nail down the syntax to get it to work. I'm not even > quite sure it can be made to work, to be honest. I'm going to try > adding the property back in as well as the relationship using the > ignore-update/ignore-insert technique and I'll pipe up if I get it > going. > > In any case, I think this is an ugly bug and I'm interested in > anyone's comments. > > Thanks, > J > > > > -- E: [EMAIL PROTECTED] W: www.compoundtheory.com --~--~---------~--~----~------------~-------~--~----~ Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer You received this message because you are subscribed to the Google Groups "transfer-dev" 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/transfer-dev?hl=en -~----------~----~----~----~------~----~------~--~---
