
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
EmoticonEmoticon