Where clause not working

[cranston]

I'm attempting to verify a given username and password but my query
just returns
all values in the database rather than filtering.

Here's the output when I run the code below:

> Authenticating: (user2, pass2)
> ******** QL Results **********
> User(user1,pass1,true,true)
> User(user2,pass2,false,true)
> QL: SELECT "t1"."name","t1"."password","t1"."isAdmin","t1"."isPrivileged" FROM "WebUsers" "t1","WebUsers" "t2" WHERE (("t2"."name"='user2') and ("t2"."password"='pass2'))
> ********************************
> ******** All Filtered **********
> allFilt matched: user2,pass2
> ********************************


And here's my source code:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++

case class User(name: String, password: String, isAdmin: Boolean,
isPrivileged: Boolean)

object User extends Table[(String, String, Boolean, Boolean)]
("WebUsers") {

lazy val database = Database.forDataSource(DB.getDataSource())

def name = column[String]("name", O NotNull, O PrimaryKey, O DBType
"varchar(64)")
def password = column[String]("password", O NotNull, O DBType
"varchar(64)")
def isAdmin = column[Boolean]("isAdmin", O NotNull)
def isPrivileged = column[Boolean]("isPrivileged", O NotNull)

def * = name ~ password ~ isAdmin ~ isPrivileged

def mapped = * <> ((x1, x2, x3, x4) ⇒ User(x1, x2, x3, x4),
User.unapply)

def authenticate(providedName: String, providedPassword: String) =
database withSession {
implicit db: Session ⇒
{

println("Authenticating: (" + providedName + ", " +
providedPassword + ")")

val ql = for (u ← this if u.name.is(providedName) &&
u.password.is(providedPassword)) yield mapped

println("******** QL Results **********")
ql.list foreach println
println("QL: " + ql.selectStatement)
println("********************************")

val allFiltered = all.filter(user ⇒ user.name equals
providedName)

println("******** All Filtered **********")
allFiltered.foreach(x ⇒ println("allFilt matched: " + x.name +
"," + x.password))
println("********************************")

allFiltered match {
case List() ⇒ Option.empty
case head :: tail ⇒ Some(head)
}
}
}

def all = database withSession {
implicit db: Session ⇒
{
(for (u ← this) yield mapped).list
}
}
}

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++

[virtualeyes]

The sql statement shows why all values in the DB are returned, there
is no join between t1 & t2 (unlikely that you want to alias the table
and join against itself in this case anyway)

Not sure why it's happening, but you can this:
object Users extends Table[User]

in place of:

object User extends Table[(String, String, Boolean, Boolean)]

and:
val ql = for (u ← Users...

in place of:
val ql = for (u ← this...

Might not make any difference, I have had no luck with same named case
class and companion object.

[cranston]

Tried your suggestions but no difference. This doesn't seem like any
exotic so I'm sure I'm doing something wrong. Any help would be
greatly appreciated.

> > +++++++++++- Hide quoted text -
>
> - Show quoted text -

[virtualeyes]

The mapped * line looks odd, of course I am new to SQ, so maybe it's
perfectly viable.

Have you tried stripping that down to something like:

def mapped = name ~ password ~ isAdmin ~ isPrivileged <> (User,
User.unapply _)

I think that may be where the trouble is as q1 query "should" only
query against a single table, not two as your print statement shows.

[nafg]

Perhaps try changing

 val ql = for (u ← this if u.name.is(providedName) && 
u.password.is(providedPassword)) yield mapped 

to

val ql = for {

  u <- this

  if (u.name is providedName) && (u.password is providedPassword)

  m <- u.mapped

} yield m

Actually there are two differences between my code and your code. One is that I'm calling mapped on u, not on the real User, and the other is that I put the direct reference to mapped inside the for block, not the yield part. Either one may help.

I don't know for a fact that this will help, but while we're at it let's look at the desugared code. First your code:

val ql = this.filter(u => u.name.is(providedName)&&u.password.is(providedPassword)).map(u => mapped)

My code (not sure if I got this right, but approximately):

val ql = this.filter(u => (u.name is providedName) && (u.password is providedPassword)).flatMap(u => u.mapped.map(m=>m))

See, u is typed as User.type --- the singleton type of User --- but behind the scenes it's apparently not the actual User instance. So u.mapped is not this.mapped.

[virtualeyes]

I don't understand the purpose of defining a case class and then in
the companion object doing:

object User extends Table[(String, String, Boolean, Boolean)]

why not just do:
object User extends Table[User]

and then:
val ql = for { u <- User ... }

and be done with it? *mapping it's just pointless extra work as far as
I can see, must be missing something...

On Apr 29, 10:20 pm, nafg <naftoli...@gmail.com> wrote:
> Perhaps try changing
>
>  val ql = for (u ← this if u.name.is(providedName) &&
> u.password.is(providedPassword)) yield mapped
>
> to
>
> val ql = for {
>   u <- this
>   if (u.name is providedName) && (u.password is providedPassword)

>   *m <- u.mapped*
> *} yield m*
> *
> *