Weird Group by Select behavior

[Joe Gaudet]

Hey all,

I am implementing a simple messaging system with the following schema

Messages

- Subject

- Content

- senderId

MessageToUsers (join table for both sender and receiver)

- userId

- messageId

- isSender

- isRead

- isFlagged

- isDeleted

And I am currently trying to get all of a user's messages in a single quety

I am trying to get something equivalent of :

select m.*, group_concat(mtu.userId separator ',') as recipients, mtu.isRead, mtu.isFlagged, mtu.isDeleted from 

message_to_user mtu, message m where 

mtu.messageId in (

  select messageId from message_to_user where userId = 3 and isSender = 0 and isDeleted = 0

) 

and mtu.isSender = 0 and m.id = mtu.messageId group by m.id

the closest I could sort out was 

        val messageIds = from(usersToMessages)((utm) => 

            where(utm.userId === user.id and utm.isSender === isSender and utm.isDeleted === isDeleted) 

            select(utm.messageId))

        val result = from(messages, usersToMessages)((m, utm) =>

        where( m.id in(messageIds) and m.id === utm.messageId and utm.isSender === false)

        groupBy(m.id, m.subject, m.content))

I want to select the additional information that is on the join, in a single query (as shown in my SQL).

However when I append any parameters from the join in groupBy it ends up breaking the where clause and selecting all of the messages anyway.

Additionally Max gave me the following to implement group concat but it doesn't seem to want to compile:

With the following error: 

Description Resource Path Location Type

overloaded method constructor FunctionNode with alternatives:   (name: String,args: org.squeryl.dsl.ast.ExpressionNode*)org.squeryl.dsl.ast.FunctionNode[String] <and>   (name: String,_mapper: Option[org.squeryl.internals.OutMapper[String]],args: Iterable[org.squeryl.dsl.ast.ExpressionNode])org.squeryl.dsl.ast.FunctionNode[String]  cannot be applied to (java.lang.String, Some[org.squeryl.internals.OutMapper[String]], org.squeryl.dsl.StringExpression[String]) SQLUtils.scala /backend/src/main/scala/com/matygo/util line 33 Scala Problem

---- The code

class GroupConcat(e: StringExpression[String], m:OutMapper[String])
extends FunctionNode[String]("group_concat", Some(m), e) with StringExpression[String] {     
  override def doWrite(sw: StatementWriter) = {
    sw.write(name)
    sw.write("(")
    sw.writeNodesWithSeparator(args, ",", false)
    sw.write(" separator ',')")
  }
}

Any thoughts would be greatly appreciated.

.joe

[Maxime Lévesque]

For your custom function, the last argument must be an Iterable, not the StringExpression itself,
you can use a Seq here :

    class GroupConcat(e: StringExpression[String], m:OutMapper[String])

    extends FunctionNode[String]("group_concat", Some(m), Seq(e)) with StringExpression[String] {    

      override def doWrite(sw: StatementWriter) = {
        sw.write(name)
        sw.write("(")
        sw.writeNodesWithSeparator(args, ",", false)
        sw.write(" separator ',')")
      }
    }

You were probably mislead by the example here :

  http://squeryl.org/custom-functions.html

which had this error, i've corrected it, my appologies...


Now for this query :

  select m.*, group_concat(mtu.userId separator ',') as recipients, mtu.isRead, mtu.isFlagged, mtu.isDeleted
  from
  message_to_user mtu, message m where
  mtu.messageId in (
    select messageId from message_to_user where userId = 3 and isSender = 0 and isDeleted = 0
  )
  and mtu.isSender = 0 and m.id = mtu.messageId
  group by m.id

It's not valid SQL, perhaps MySql does something with it, but most DBs won't accept it,
the

 1) selected colums must all be included in the group by columns
 2) additional colums must be inside aggregate functions

this would be valid :
  select m.i, group_concat(mtu.userId separator ',') as recipients,
this also :
  select m.i, group_concat(mtu.userId separator ',') as recipients, sum(mtu.isRead), sum(mtu.isFlagged), sum(mtu.isDeleted)

also, grouping on content is suspicious :

  groupBy(m.id, m.subject, m.content))

if your trying to minimize DB roundtrips, you could select something like :

  select m.i, group_concat(mtu.userId separator ',') as recipients,

and then join this with the message table (with the ke m.id).

Hope that helps...

ML

2011/9/22 Joe Gaudet <j...@matygo.com>

[Santosh Ganapa]

Thanks a lot everyone, it saved my days efforts....