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?

Answer 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")