Just one more thing, I advanced a bit.
I tried to run:
Protein.query.join('names').join('mutations').filter_by(Protein.id=3).all()
the emphasis is on Protein.id, with id=3 it runs perfectly, but in
some cases you need to filter on columns from different tables...
My interpreter is v2.6.2
What am I doing wrong?thanks david and I got: SyntaxError: keyword can't be an expression Even though the same is done in the tutorial with the Movie entity On Feb 28, 1:27 pm, David Borsodi <[email protected]> wrote: > Hello Everyone, could you help me how to turn this sql query into an > Elixir(preferably) or sqlalchemy one? > SELECT COUNT(p.id), pn.name > FROM md_proteinname pn, md_protein p, md_mutation m, > md_proteinname_proteins__md_protein_names pnp > WHERE pn.mainname = 1 AND m.protein_id = p.id > AND p.id = pnp.md_protein_id AND pn.id = pnp.md_proteinname_id > GROUP BY pn.name ORDER BY pn.name ASC > > A have the tables mapped with elixir: > class ProteinName(Entity): > using_options(tablename='md_proteinname') > > id = Field(Integer, primary_key=True, autoincrement=True) > name = Field(String(128), nullable=False) > mainname = Field(Boolean, nullable=False) > proteins = ManyToMany('Protein') > > class Protein(Entity): > using_options(tablename='md_protein') > > id = Field(Integer, primary_key=True, autoincrement=True) > sequence = Field(Text, nullable=False) > organism = Field(String(128), nullable=False) > regions = OneToMany('Region') > names = ManyToMany('ProteinName') > mutations = OneToMany('Mutation') > > class Mutation(Entity): > using_options(tablename='md_mutation') > > id = Field(Integer, primary_key=True, autoincrement=True) > position = Field(String(64), nullable = True) > mutation = Field(String(64), nullable=False) > protein = ManyToOne('Protein') > comments = OneToMany('Comment') > publications = ManyToMany('Publication') > > So far, I'm here with sqlalchemy (0.6.6): > session.query(ProteinName.name, func.count(Mutation.id)). > filter(and_(ProteinName.mainname=True)). > group_by(ProteinName.name).order_by(ProteinName.name).all() > > I can't do the joins since I have elixir relations defined > With elixir I'm almost completely lost... > > thanks in advance > david -- You received this message because you are subscribed to the Google Groups "SQLElixir" 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/sqlelixir?hl=en.
