Filtering by existence in a List[Int]

[vincilbishop]

I have an userId Int value, and in the Postgres database I have a column:

def userIds = column[List[Int]]

I can't for the life of me figure out how to perform a filter query on these values.

I'd like something like:

val userId : Int = 1 posts.filter(post => userId inSet post.userIds).result

But I can't seem to make it work. Seems like it should be the simplest thing. It says that post.userIds is of type Rep[List[Int]], but I can't figure out how to map on it.

(and in case you couldn't tell from the contents of my post, I am still new to Scala)

Thanks in advance for any assistance you might be able to give.

[tminglei]

You can try

val userId : Int = 1 posts.filter(post => userId === post.userIds.any).result
// or
val userId : Int = 1 posts.filter(post => post.userIds @> userId).result

For more op/details, pls check postgres' array related doc and slick-pg's related doc.

[vincilbishop]

Thanks for the syntax pointer, that's very helpful. I still can't get it to recognize the any keyword, or the @> operator. I'm pretty sure it's just due to my lack of Scala knowledge, it's been elusive at times. I've visually checked all my imports, and they look the same. My next step will be to dissect the example project in an IDE to see what I might be missing. I'll share that I do receive the error below when trying to compile:

PostDAO.scala:159: ambiguous implicit values: [error] both value BooleanCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[Boolean] [error] and value BooleanOptionColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Option[Boolean]]] [error] match expected type slick.lifted.CanBeQueryCondition[Nothing] [error] dbConfig.db.run(posts.filter(post => userId === post.userIds.any).result)

I know this issue is not a defect in your library so I highly appreciate you entertaining my newby question. Thanks so much for authoring the library and tending to my questions. All the best!

[tminglei]

Hi @premosystems, you need provide more details. I can't determine what happened on your side from current info.

[vincilbishop]

Again thanks so much for taking the time to look at my issue.

I've forked your example project and added a representative property to the Companies Table class, and a query here: https://github.com/premosystems/slick-pg/blob/master/examples/play-slick-example/app/dao/CompaniesDAO.scala#L51

I get the same result as in my own project, it does not recognize the @> operator. See the error below:

I'm sure it's some import I am missing or something simple like that, but any advice you can give would be greatly appreciated. Thanks in advance.

[tminglei]

Sorry, made some minor mistake.

Change to this will work:

  /** Find matching companies */
  def find(userId: Int): Future[Seq[Company]] =
    db.run(companies.filter(company => company.userIds @> List(userId)).result)

//or

  /** Find matching companies */
  def find(userId: Int): Future[Seq[Company]] =
    db.run(companies.filter(company =>  userId.bind === company.userIds.any).result)

[tminglei]

In fact, check into source codes and test cases, you can avoid this problem. ;-)

[vincilbishop]

Thanks so much for your help. The issue still persists. It might be a bug in IntelliJ? You've been great thanks so much for your time.

[tminglei]

Well, there's another issue in your codes.

You added one more field in case class Company, but you forgot to fill it in Company(id, name, props). Forgot to remind you just row, since it's an obvious issue.

[xiantang]

i can not import @> or any. how can i to import them @tminglei

[NupurKothare]

I am not able import @> or any. how can i to import them @tminglei

[tminglei]

@NupurKothare you can do it like this: define your profile like this, and then import it.