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.

@RBarryYoung, base on SO bounty system, if no accepted answer when bounty ends, person with highest vote will recieve the bounty. — Trav L, Jan 23 '10 at 06:33

score 1 · Answer 1 · answered Jan 18 '10 at 20:23

1

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

answered Jan 18 '10 at 20:23

Tom H

46,766
14
87
128

Yes, CodingHorror (or InlineQuery) will work but that also remove the purpose of using ActionRecord. – Trav L Jan 20 '10 at 01:09

score 1 · Answer 2 · answered Jan 21 '10 at 00:03

1

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.

answered Jan 21 '10 at 00:03

RBarryYoung

55,398
14
96
137

View is a valid option that I will consider. However I'm just pickled that SubSonic cannot simply allow alias to referenced table name? – Trav L Jan 21 '10 at 00:25
Got me, SQL is my area of expertise, not SubSonic. – RBarryYoung Jan 21 '10 at 22:22

score 0 · Answer 3 · answered Jan 19 '10 at 17:35

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

-1 OP clearly says that upgrading is not an option. So this doesn't answer the question... — David Oneill, Jan 21 '10 at 00:07

Table Alias in SubSonic

3 Answers3