Table Alias in SubSonic

Question

How can I assign alias to tables with SubSonic 2.1?

I am trying to reproduce the following query:

SELECT *
FROM posts P
RIGHT OUTER JOIN post_meta X ON P.post_id = X.post_id
RIGHT OUTER JOIN post_meta Y ON P.post_id = Y.post_id
WHERE X.meta_key = "category"
    AND X.meta_value = "technology"
    AND Y.meta_key = "keyword"
    AND Y.meta_value = "cloud"

I'm am using SubSonic 2.1 and upgrading to 2.2 isn't an option (yet). Thanks.

Answer 1

Does the CodingHorror functionality exist in 2.1? It states on the docs that it's in the 2.x category.

Answer 2

I think if you just use in subsonic 2.2 will do the work. You should seriously consider upgrading. Version 2.2 has a lot more goodies to make your life better.

subsonic.select().from("posts p")
        .RightOuterJoin("post_meta","post_id","posts","post_id")
        .RightOuterJoin("post_meta","post_id","posts","post_id")
        .Where("post_meta.meta_key").IsEqualTo("category")
        .And("post_meta.meta_value").IsEqualTo("technology")
        .And("post_meta.meta_key").IsEqualTo("keyword")
        .And("post_meta.meta_value").IsEqualTo("cloud")

Answer 3

Use a view:

CREATE VIEW post_meta2 AS SELECT * FROM post_meta

Then your query becomes:

SELECT *
FROM posts
RIGHT OUTER JOIN post_meta ON posts.post_id = post_meta.post_id
RIGHT OUTER JOIN post_meta2 ON posts.post_id = post_meta2.post_id
WHERE post_meta.meta_key = "category"
    AND post_meta.meta_value = "technology"
    AND post_meta2.meta_key = "keyword"
    AND post_meta2.meta_value = "cloud"

Yes, I know, it's crude. But effective nonetheless. If you want elegance then upgrade as has already been suggested, otherwise this should be sufficient for a temporary workaround.