gmane.comp.python.sqlobject

Fred C | 23 Apr 01:13

LEFTJOINOn question


I have that request

articles = model.Article.select(
	model.Comment.q.id != None,
	join = LEFTJOINOn(None, model.Comment,
		model.Comment.q.articleID == model.Article.q.id),
	orderBy='-created')

which is translated into

SELECT article.id, article.link, article.title, article.summary,  
article.updated, article.rss_id, article.user_id, article.category_id  
FROM article LEFT JOIN comment ON ((comment.article_id) =  
(article.id)) WHERE ((comment.id) IS NOT NULL) ORDER BY created DESC

Since I am doing a LEFT JOIN it seems natural for me to have the  
request returning the fields from both tables article and comment. How  
can I have sqlobject returning the fields from both tables.

-fred

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference 
Don't miss this year's exciting event. There's still time to save $100. 
Use priority code J8TL2D2. 
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone

headers

Oleg Broytmann | 23 Apr 01:21

Re: LEFTJOINOn question

On Tue, Apr 22, 2008 at 04:16:53PM -0700, Fred C wrote:
> How  
> can I have sqlobject returning the fields from both tables.

   SQLObject.select() cannot do that because it doesn't know the
description of the fields. Article.select(...) - SQLObject knows only
Article's fields.
   sqlbuilder.Select() returns whatever you want.

Oleg.
--

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

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference 
Don't miss this year's exciting event. There's still time to save $100. 
Use priority code J8TL2D2. 
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone

headers

Fred C | 23 Apr 01:37

Re: LEFTJOINOn question

On Apr 22, 2008, at 4:21 PM, Oleg Broytmann wrote:

> On Tue, Apr 22, 2008 at 04:16:53PM -0700, Fred C wrote:
>> How
>> can I have sqlobject returning the fields from both tables.
>
>   SQLObject.select() cannot do that because it doesn't know the
> description of the fields. Article.select(...) - SQLObject knows only
> Article's fields.
>   sqlbuilder.Select() returns whatever you want.

The problem with sqlbuilder is that it only know about the database  
raw fields and what is returned are just arrays of fields and I am  
loosing the mapping with the objects. In my class Articles and  
Comments I have methods  and these methods are not accessible by what  
is returned from sqlbuilder.Select

for exemple in the class Comment I have the method

     def _get_datetime(self):
         return self._SO_get_created().strftime('%b %d, %Y @ %H:%M')

This method cannot called from a request build with sqlbuilder.Select  
and can be called from a regular select

x = Comment.select()
x[0].datetime

-fred-

(Continue reading)

headers

Oleg Broytmann | 23 Apr 01:44

Re: LEFTJOINOn question

On Tue, Apr 22, 2008 at 04:37:34PM -0700, Fred C wrote:
> On Apr 22, 2008, at 4:21 PM, Oleg Broytmann wrote:
> > On Tue, Apr 22, 2008 at 04:16:53PM -0700, Fred C wrote:
> >> How
> >> can I have sqlobject returning the fields from both tables.
> >
> >   SQLObject.select() cannot do that because it doesn't know the
> > description of the fields. Article.select(...) - SQLObject knows only
> > Article's fields.
> >   sqlbuilder.Select() returns whatever you want.
> 
> The problem with sqlbuilder is that it only know about the database  
> raw fields and what is returned are just arrays of fields and I am  
> loosing the mapping with the objects.

   Table.select() operates with the Table and only with the Table. All
other objects are SQLExpressions, not tables, so SQLObject cannot guess if
they are tables and what SQLObjects these tables correspond to. SQLObject
converts these SQLExpressions to strings and construct SQL queries, but
that's all it can do.

http://sqlobject.org/FAQ.html#how-can-i-do-a-left-join

Oleg.
--

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

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference

(Continue reading)