Erlang/OTP Forums
Author Message

<  Erlang  ~  outer joins in qlc
cerrina
Posted: Wed Apr 29, 2009 10:24 pm Reply with quote
Joined: 29 Apr 2009 Posts: 1
Does anyone know how to implement outer joins in qlc?

Say I have 2 tables, A and B:

A has records of type user {id,name}
B has records of type place {id,address}

where id is the common field between the records. The tables have the following data:

A: {1,"John"},{2,"Amy"},{3,"Bob"}
B: {1,"Abc St"},{3,"Def St"}

Ideally, I'd love to say:
qlc:q([{X#user.name,Y#place.address} || X<-A,Y<-B, X#user.id=:=Y#place.id])

and have it return:
{"John","Abc St"}
{"Amy",[]} or {"Amy,null}
{"Bob","Def St"}

Can someone point me in the right direction? Thanks!
Back to top View user's profile Send private message
Hans Bolinder
Posted: Fri May 08, 2009 2:07 pm Reply with quote
User Joined: 05 Nov 2008 Posts: 24 Location: Stockholm
Hi,

There is no support for outer join per se. I can think of two
alternatives:

Avoid the problem by assigning everybody a list of addresses. Like this:
{1,["Abc St"]},{2,[]},{3,["Def St"]}.

Or use qlc:keysort and qlc:append to "fill in" the missing users.
Here is a sketch using the Erlang shell:

Eshell V5.7.2 (abort with ^G)
1> rd(user, {id, name}).
user
2> rd(place, {id, address}).
place
3> A = [#user{id=1,name="John"},#user{id=2,name="Amy"},#user{id=3,name="Bob"}].
[#user{id = 1,name = "John"},
#user{id = 2,name = "Amy"},
#user{id = 3,name = "Bob"}]
4> B = [#place{id=1,address="Abc St"},#place{id=3,address="Def St"}].
[#place{id = 1,address = "Abc St"},
#place{id = 3,address = "Def St"}]
5> Q1 = qlc:q([{X#user.name,Y#place.address} ||
X <- A,
Y <- B,
X#user.id=:=Y#place.id]),
Q2 = qlc:append(Q1,
qlc:q([{X#user.name,""} || X <- A])),
Q = qlc:keysort(1, Q2, [unique]),
qlc:e(Q).
[{"Amy",[]},{"Bob","Def St"},{"John","Abc St"}]
6>

Note that keysort chooses the first occurrence with a certain key when
given the 'unique' option.

Best regards,

Hans Bolinder, Erlang/OTP team, Ericsson
Back to top View user's profile Send private message
uwiger
Posted: Sun May 10, 2009 8:58 am Reply with quote
User Joined: 03 Jul 2006 Posts: 604 Location: Sweden
Hans Bolinder wrote:
Hi,

There is no support for outer join per se. I can think of two
alternatives:

Avoid the problem by assigning everybody a list of addresses. Like this:
{1,["Abc St"]},{2,[]},{3,["Def St"]}.

Or use qlc:keysort and qlc:append to "fill in" the missing users.


Interesting. Then what is wrong with doing it this way?

Code:

1> rd(user,{id,name}).
user
2> rd(place,{id,address}).
place
3> T1 = ets:new(user,[ordered_set,{keypos,2}]).                           
122901                             
4> T2 = ets:new(place,[ordered_set,{keypos,2}]).                         
126996                             
5> ets:insert(T1,[{user,1,"John"},{user,2,"Amy"},{user,3,"Bob"}]).             
true                               
6> ets:insert(T2,[{place,1,"Abc St"},{place,3,"Def St"}]).                     
true                               
7> qlc:eval(qlc:q([{X#user.name,Y#place.address} || X <- ets:table(T1),Y <- ets:table(T2), X#user.id =:= Y#place.id])).
[{"John","Abc St"},{"Bob","Def St"}]


BR,
Ulf W
Back to top View user's profile Send private message Visit poster's website

Display posts from previous:  

All times are GMT
View next topic
View previous topic
Page 1 of 1
Erlang Forum - Trap Exit Forum Index  ~  Erlang
This forum is locked: you cannot post, reply to, or edit topics.

Jump to:  

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum
Hosted by Erlang Solutions    |    Powered by phpBB and NoseBleed 1.14