Joining three tables - Selecting column from two different tables

[Steve]

Hi all,

Newbie here.

I just want to execute the following sql using SqlAlchemy . But
getting various errors.

select ssf.factor,ssf.displayname,pmw.weight
from probability_models_weights pmw
inner join probability_models pm on pm.id = pmw.model_id
inner join success_factors ssf on ssf.id = pmw.factor_id
where pm.id = 6

I want to execute this using session.

I am using declarative base with the following auto loaded classes.

class SucessFactors(WBase):
__tablename__ = "success_factors"
__table_args__ = {'autoload':True}

class ProbabilityModels(WBase):
__tablename__ = "probability_models"
__table_args__ = {'autoload':True}

class ProbabilityModelsWeights(WBase):
__tablename__ = "probability_models_weights"
__table_args__ = {'autoload':True}

I tried the following but it didn't work.

session.query(SucessFactors.factor,SucessFactors.displayname,ProbabilityModelsWeights.weight).
\
join(ProbabilityModelsWeights,ProbabilityModels,
ProbabilityModelsWeights.model_id == ProbabilityModels.id).\
join(ProbabilityModelsWeights,SucessFactors,
ProbabilityModelsWeights.factor_id == SucessFactors.id).\
filter(ProbabilityModels.id == model_id).\
all()

Thanks in advance.

Steve.

[Michael Bayer]

query.join() is a one-argument form (it will accept two arguments in 0.7, but thats not released yet), so here you want to be saying

query(...).select_from(ProbabiliyModelsWeights).join((ProbabiltityModels, ProbabiltiyModelsWeights.model_id==ProbabilityModels.id)).

the select_from() accepting a mapped class is a helper that was introudced in 0.6.5. Also note the tuple form inside of join(), i.e. join((target, onclause)) (you won't need that in 0.7). Documented at http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins .

>
> Thanks in advance.
>
> Steve.
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Steve]

Hi,

Thanks. Worked like a charm.

Also thanks for SqlAlchemy. A refreshing change for someone from java
background.

I am using this with Jython. Thanks for the Jython support also.

Steve