CODEWITHSUNDEEP
postgresqlscalaslick
Fahad Siddiqui
Fahad Siddiqui

Reputation: 1849

Cannot get same number of rows by equivalent slick query

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?

Upvotes: 0

Views: 46

Answers (2)

devkat
devkat

Reputation: 1644

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

Upvotes: 2

Nagarjuna Pamu
Nagarjuna Pamu

Reputation: 14825

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)

Upvotes: 0

Related Questions