Cannot get same number of rows by equivalent slick query

Question

Following is the Postgresql query and it returns 400+ rows

SELECT 
*

FROM 
investors, 
investments, 
investor_personal_information,
sub_products, 
products, 
company, 
issuers, 
investment_admin_approvals, 
users

WHERE 
investments.investor_id = investor_personal_information.id AND
investments.investor_id = investors.id AND 
investments.sub_product_id = sub_products.id AND 
sub_products.product_id = products.id AND 
products.company_id = company.id AND 
investments.id = investment_admin_approvals.investment_id AND 
issuers.owner = users.id

I've written its equivalent Slick query in Scala

val query = {
  val baseQuery = Investments join InvestorPersonalInformation on {
    case (its, ipi) => its.investorId === ipi.id
  } join Investors on {
    case ((its, ipi), irs) => its.investorId === irs.id
  } join SubProducts on {
    case (((its, ipi), irs), sbp) => its.subProductId === sbp.id
  } join Products on {
    case ((((its, ipi), irs), sbp), pds) => sbp.productId === pds.id
  } join Tables.Company on {
    case (((((its, ipi), irs), sbp), pds), cpy) => pds.companyId === cpy.id
  } join Tables.Issuers on {
    case ((((((its, ipi), irs), sbp), pds), cpy), iss) => cpy.issuerId === iss.id
  } join InvestmentAdminApprovals on {
    case (((((((its, ipi), irs), sbp), pds), cpy), iss), iaa) => its.id === iaa.investmentId
  } join Users on {
    case ((((((((its, ipi), irs), sbp), pds), cpy), iss), iaa), usrs) => iss.owner === usrs.id
  } map {
    case ((((((((its, ipi), irs), sbp), pds), cpy), iss), iaa), usrs) => (its, ipi, irs, sbp, pds, cpy, iss, iaa, usrs)
  }

  baseQuery
}

When I run db.run(query.result) I get only 12 rows. I am using inner join in both queries and both are equivalent. What can be root cause of this?

check this: http://stackoverflow.com/questions/10799540/select-or-equivalent-in-scala-query — JoelBonetR, Sep 26 '16 at 11:34
Your slick query is quite hard to read. Are you sure you didn't mess up a variable? Have you considered using a monadic join instead? — Roman, Sep 26 '16 at 12:24

score 2 · Answer 1 · answered Sep 26 '16 at 13:50

2

The SQL query doesn't contain the join on company.issuer_id = issuers.id.

answered Sep 26 '16 at 13:50

devkat

1,624
14
15

score 0 · Accepted Answer · answered Sep 26 '16 at 12:14

0

If you are slick 3.0 and above. Print the sql statement generated by slick and compare with your raw sql query.

Following code will help you get the sql generated by slick

query.result.statements.foreach(println)

answered Sep 26 '16 at 12:14

Nagarjuna Pamu

14,737
3
22
40

Cannot get same number of rows by equivalent slick query

2 Answers2