SQLObject

On Tue, May 15, 2007 at 01:07:14AM +0200, TiNo wrote:
> Hi,
> 
> I have a db model that looks like this:
> class Photo(SQLObject):
>    filename = StringCol(length=30,alternateID=True)
>    keywords = RelatedJoin("Keyword")
>    shot_date = DateTimeCol(default=None)
>    place = StringCol(default=None)
> 
> class Keyword(SQLObject):
>    keyword = StringCol(alternateID=True)
>    photos = RelatedJoin("Photo")
> 
> Two questions:
> 
> 1) How do I select all photos that have keyword1 AND keyword2?

photos = []
for photo on Photo.select():
   found = 0
   for keyword in photo.keywords:
      if keyword.keyword in ("test1", "TeSt2"):
         found += 1
   if found == 2:
      photos.append(photo)

> 2) What is the easiest way to build up a long select?

conditions = []
if date:
   conditions.append(Photo.q.shotdate == date)
if place:
   conditions.append(Photo.q.place == place)
Photo.select(AND(*conditions))

   Don't forget * in *conditions!

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

2007/5/15, Oleg Broytmann <phd@...>:
>
>
> > 1) How do I select all photos that have keyword1 AND keyword2?
>
> photos = []
> for photo on Photo.select():
>    found = 0
>    for keyword in photo.keywords:
>       if keyword.keyword in ("test1", "TeSt2"):
>          found += 1
>    if found == 2:
>       photos.append(photo)



Cool, this works. The only thing is, now it makes a query for every photo in
the DB. Right now that would only be a couple thousand (3 I think), but that
could be more than 10 thousand in a couple of years. Would this slow things
really down with a SQLite db? And if so, is there another way to do this?

Another question bubbles up. Is there a way to create a dict out of a
SelectResult? Or a JSONifiable object?

On Tue, May 15, 2007 at 01:17:40PM +0200, TiNo wrote:
> now it makes a query for every photo in
> the DB. Right now that would only be a couple thousand (3 I think), but that
> could be more than 10 thousand in a couple of years. Would this slow things
> really down with a SQLite db?

   Yes, I think so.

> And if so, is there another way to do this?

   Do it SQL-wise. Recreate and declare the intermediate table, and do a
join.

> Another question bubbles up. Is there a way to create a dict out of a
> SelectResult?

   Starting with 0.8 SQLObject.sqlmeta has a method .asDict().

> Or a JSONifiable object?

   No, that's completely outside of the scope of SQLObject.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

2007/5/15, Oleg Broytmann <phd@...>:
>
> > And if so, is there another way to do this?
>
>    Do it SQL-wise. Recreate and declare the intermediate table, and do a
> join.


Ok, I figured out the join I need to do, and I declared the intermediate
table like this:
class Photo(SQLObject):
   [...]
    keywords = RelatedJoin("Keyword",otherColumn="keyword_id",
                                        joinColumn="photo_id",
                                        intermediateTable="keyword_photo")
class Keyword(SQLObject):
    keyword = StringCol(alternateID=True)
    photos = RelatedJoin("Photo", joinColumn="keyword_id",
                                           otherColumn="photo_id",

intermediateTable="keyword_photo")

Is that correct? If so, how do I access the intermediateTable? Because:

>>> Photo.select(LEFTJOINOn(None,keyword_photo,Photo.id ==
keyword_photo.photo_id))

gives me:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
NameError: name 'keyword_photo' is not defined

??

> Another question bubbles up. Is there a way to create a dict out of a
> > SelectResult?
>
>    Starting with 0.8 SQLObject.sqlmeta has a method .asDict().
>
> Or a JSONifiable object?
>
>    No, that's completely outside of the scope of SQLObject.


I understand. I meant a simple object that I could pass to a function that
converts it to JSON. But asDict() does the trick. I should be mentioned in
the docs I think. Quite a valueable method..

On Tue, May 15, 2007 at 05:56:01PM +0200, TiNo wrote:
> Ok, I figured out the join I need to do, and I declared the intermediate
> table like this:
> class Photo(SQLObject):
>   [...]
>    keywords = RelatedJoin("Keyword",otherColumn="keyword_id",
>                                        joinColumn="photo_id",
>                                        intermediateTable="keyword_photo")
> class Keyword(SQLObject):
>    keyword = StringCol(alternateID=True)
>    photos = RelatedJoin("Photo", joinColumn="keyword_id",
>                                           otherColumn="photo_id",
> 
> intermediateTable="keyword_photo")
> 
> Is that correct? If so, how do I access the intermediateTable?

   No, it is not enough. You have to make the intermediate table full
SQLObject table. Create and id column in it and declare it as

class KeywordPhoto(SQLObject):
   photo = ForeignKey("Photo")
   keyword = ForeignKey("Keyword")

   Then use it in the SQLObject expressions.

   Or construct low-level queries using sqlbuilder.Select().

> But asDict() does the trick. I should be mentioned in
> the docs

   Many valuable things should be mentioned there. You can speed things up
by creating a documentation patch.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.