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.
Does the CodingHorror functionality exist in 2.1? It states on the docs that it's in the 2.x category.
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.
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")
精彩评论