Tutorial :many-to-one attributes in Storm



Question:

My schema looks something like this:

CREATE TABLE plans (      id SERIAL PRIMARY KEY,      description text  );    CREATE TABLE projects (      id SERIAL PRIMARY KEY,      project_id character varying(240) UNIQUE,      plan_id integer REFERENCES plans(id) ON DELETE CASCADE  );  

And I want to do Storm queries along the lines of

plan = store.find(Plan, Plan.project_id == "alpha")  # should translate to something like  # SELECT p.* from plans p LEFT JOIN projects proj ON p.id = proj.plan_id  #     WHERE proj.project_id = 'alpha';  

(Note that projects.plan_id is not unique.)

How do I set this up?


Solution:1

For the given SQL, there isn't much reason to use a left join, since your where clause won't match any rows where there isn't a corresponding project. You could get the results with:

result = store.find(Plan, Plan.id == Project.plan_id, Project.project_id == "alpha")  

This will give you a ResultSet object. Given your schema, it looks like you're expecting a single row, so you can access that with:

plan = result.one()  

Or tie them both together with:

plan = store.find(Plan, Plan.id == Project.plan_id, Project.project_id == "alpha").one()  

If you really need to do a left join, the syntax for that would be something like this:

result = store.using(LeftJoin(Plan, Project, Plan.id == Project.plan_id)).find(      Plan, Project.project_id == "alpha")  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »