CODEWITHSUNDEEP
mysqlscalaslickslick-3.0
SergiGP
SergiGP

Reputation: 691

Two joins with same table with Slick 3.0

I'm trying to the the following sql query with Slick 3.0

select cm_buyer.conversation_id
from conversations c
inner join conversation_members cm_buyer on cm_buyer.conversation_id = c.id
inner join conversation_members cm_seller on cm_buyer.conversation_id = cm_seller.conversation_id
where (cm_buyer.talker_id = "7LUhaK"
and cm_buyer.is_buyer = 1)
and c.`product_id` = "2"
and (cm_seller.`talker_id` = "BBBBBB" and cm_seller.is_buyer = 0);

I'm new to Slick and i've never been a super crack in mysql so I need some help here. As you can see I'm doing two joins from conversation table to the same table conversation_members.

I'm doing something like:

val sellerId = TalkerId("7LUhak")
val buyerId = TalkerId("BBBBBB")
val conversationMembers = TableQuery[ConversationMemberTable]
val conversations = TableQuery[ConversationTable]

val query = for {
  a <- conversations join conversationMembers on (_.id === _.conversationId)
  // SOME MAGIC HERE
} yield (something)

PersistenceUtils.run(query.result)

I've done some basic queries with these two table queries (add, simple selects...) so the mappings are correctly done. I've tried to do so many things in the magic section without luck :(

Can you help me with this?

Thank you!

Sergi

Upvotes: 0

Views: 669

Answers (2)

SergiGP
SergiGP

Reputation: 691

I have finally did this and it works :D

val conversationMembers = TableQuery[ConversationMemberTable]

val query = for {
  c <- conversations if c.productId === productId
  cmBuyer <- conversationMembers if c.id === cmBuyer.conversationId && cmBuyer.talkerId === buyerId && cmBuyer.isBuyer === true
  cmSeller <- conversationMembers if c.id === cmSeller.conversationId && cmSeller.talkerId === sellerId && cmSeller.isBuyer === false
} yield (c)



PersistenceUtils.run(query.result)

Upvotes: 1

Roman
Roman

Reputation: 5699

In slick it is all about mixing and combining queries (untested example):

val conversationMembers = TableQuery[ConversationMemberTable]
val conversations = TableQuery[ConversationTable]

val sellerQuery = conversationMembers.filter(b => b.talkerId === "7LUhaK" && b.isBuyer === 1)
val buyerQuery = conversationMembers.filter(s => s.talkerId === "BBBBBB" && s.isBuyer === 0)

val query = for {
  c      <- conversations.filter(_.productId === 2)
  buyer  <- buyerQuery if c.id === buyer.conversationId
  seller <- sellerQuery if buyer.conversationId === seller.conversationId
} yield buyer

val action = query.map(_.conversationId).result  // omit .map(...) to fetch the whole model instead
PersistenceUtils.run(action)

As you can see I combined three queries in a monadic join using a for comprehension. You can take this even further and reuse query in another db query and so on. Actually I did this when I mapped Conversation to its id (query.map(_.conversationId)).

Slick is a bit hard to grasp at the beginning especially when you are used to ORM's like Hibernate but I hope you got the general idea behind.

Upvotes: 1

Related Questions