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.
