Replies at the bottom...
> > OK, sorry about the obfuscated names. Don't know what came over me. I
> > have a CMS site, with advertising.
> >
> > I have:
> >
> > class Picture(SQLObject):
> > producer = ForeignKey('Producer')
> > image_categories = SQLRelatedJoin('Image_category')
> >
> > class Ad(SQLObject):
> > producer_categories = SQLRelatedJoin('Producer_category')
> > image_categories = SQLRelatedJoin('Image_category')
> >
> > class Producer(SQLObject):
> > producer_category = ForeignKey('Producer_category')
> >
> > class Producer_category(SQLObject):
> > producers = SQLMultipleJoin('Producer')
> > ads = SQLRelatedJoin('Ad')
> >
> > class Image_category(SQLObject):
> > ads = SQLRelatedJoin('Ad')
> > pictures = SQLRelatedJoin('Picture')
> >
> > Say I want to display a picture, p = Picture() on a page. I need to
> > find an Ad to display with it. The rules for ad selection is that it
> > needs to have the producer_category which encompasses the picture in is
> > producer_categories field. And that its image_categories and the
> > picture's image_categories need to have at least one element in common.
> >
> > I know I could use SQLBuilder to select what I need, but something tells
> > me I'm not doing this in the most elegant manner possible.
> >
> > Suggestions, anyone?
> >
> > Thanks,
> >
> > Stuart
> >
> >
> > On Mon, 2006-11-06 at 09:45 +0000, Jorge Vargas wrote:
> > > I think either you have a very very complex project or your not using
> > > the object relational mapper right. SQLObject are not tables.
> > >
> > > can you post a "real" example maybe what you need is a simplier Model
> > > layer.
> > >
> > > but I can tell you with all those RelatedJoin it can very very slow.
> > >
> > > On 11/6/06, Stuart Clarke <[EMAIL PROTECTED]> wrote:
> > > >
> > > > Sorry, I mis-wrote the problem setup. It should be:
> > > >
> > > > class A(SQLObject):
> > > > foo = SQLRelatedJoin('E')
> > > > bar = ForeignKey('C')
> > > >
> > > > class B(SQLObject):
> > > > smell = SQLRelatedJoin('E')
> > > > mummy = SQLRelatedJoin('D')
> > > >
> > > > class C(SQLObject):
> > > > fine = ForeignKey('D')
> > > >
> > > > class D(SQLObject):
> > > > hair = SQLMultipleJoin('C')
> > > > bees = SQLRelatedJoin('B')
> > > >
> > > > class E(SQLObject):
> > > > moo = SQLRelatedJoin('A')
> > > > itch = SQLRelatedJoin('B')
> > > >
> > > > a = A()
> > > >
> > > >
> > > > On Mon, 2006-11-06 at 19:24 +1000, Stuart Clarke wrote:
> > > > > Thanks for the reply, but the solution doesn't work for me.
> > > > >
> > > > > The problem I posted was actually part of a wider one. Details:
> > > > >
> > > > > class A(SQLObject):
> > > > > foo = SQLRelatedJoin('B')
> > > > > bar = ForeignKey('C')
> > > > >
> > > > > class B(SQLObject):
> > > > > smell = SQLRelatedJoin('B')
> > > > > mummy = SQLRelatedJoin('D')
> > > > >
> > > > > class C(SQLObject):
> > > > > fine = ForeignKey('D')
> > > > >
> > > > > class D(SQLObject):
> > > > > hair = SQLMultipleJoin('C')
> > > > > bees = SQLRelatedJoin('B')
> > > > >
> > > > > a = A()
> > > > >
> > > > > OK, my problem is that I want to find all b = B(), such that
> > > > > a.bar.fine
> > > > > is in b.mummy AND the intersection of a.foo and b.smell is not an
> > > > > empty
> > > > > set.
> > > > >
> > > > > So, I can get b_global = a.bar.fine.bees which gives me a
> > > > > SelectResults
> > > > > of B instances that contains every one I could be interested in, plus
> > > > > some more.
> > > > >
> > > > > I need to select amongst those. Something along these lines:
> > > > >
> > > > > for my_foo in a.foo:
> > > > > b_subset = b_global.filter(my_foo is in B.smell)
> > > > >
> > > > > I don't mind having the separate b_subsets at this point, although if
> > > > > I
> > > > > could aggregate them and specify that each b in b_subsets_aggregate be
> > > > > unique, then that would be even better.
> > > > >
> > > > > So, who's got the chops to help me with this monster? :-)
> > > > >
> if I undestand correctly I think your model can be reduced to
>
> class Picture(SQLObject):
> producer = ForeignKey('Producer')
> image_categories = SQLMultipleJoin('Image_category')
>
> class Ad(SQLObject):
> producer_category = ForeignKey('Producer_category')
> image_categories = SQLMultipleJoin('Image_category')
>
> class Producer(SQLObject):
> producer_category = ForeignKey('Producer_category')
> ads = SQLRelatedJoin('Ad')
>
> class Producer_category(SQLObject):
> name = StringCol()
>
> class Image_category(SQLObject):
> name = StringCol()
*** Unfortunately, this is not the case. I navigate to lists of
pictures by way of their image category. So, the user needs to be able
to say "I want to see all pictures in image category X". And so I need
to retrieve Image_category X and delve into its 'Picture' RelatedJoin.
> on the other hand the query is not really possible because this has
> "application logic".
> So unless you can create a stored procedure to implement the "best
> match" betten the image_category lists
> I'm affaid the best that can be done is something like
>
> p = Picture.get(1)
> possibleAds = Ad.selectBy(producer=p.producer)
>
> for ad in possibleAds:
> determineBestMatch(ad.image_categories,p.image_categories)
*** Hmmm....
I still feel that I should be able to say:
ads_global = p.producer.producer_category.ads
for category in p.image_categories:
ads_global.filter(<category in Ad.image_categories>)
and then make a random selection from the results. I just don't know
how to do that part in the <>.
Problem is, I can't just get ads_global from the DB and then search
through them manually. There will simply be too many of them, and the
SELECT will really damage performance.
What I could do, assuming the above is impossible, however, is to get a
random 10-element subset of ads_global, and manually search through
that. If I don't find a suitable match, I get another one, and so on.
After N attempts, I can just give up. It's not optimal, but it might
have to do.
So nobody is able to recommend a way in which I could implement the
above pseudo-code?
Stuart
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" 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/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
