Complex SQL Query With Join In Liftweb

I've looked this up. It looks as though the joins are done in the object layer.

Extrapolated from http://exploring.liftweb.net/master/index-8.html to your case:

// Accessing foreign objects  class Employee extends LongKeyedMapper[Employee] with IdPK {   ...    object department extends MappedLongForeignKey(this, Department)    def departmentName =      Text("My department is " + (department.obj.map(_.name.is) openOr "Unknown"))  }  class Department ... {    ...    def entries = Employee.findAll(By(Employee.department, this.id))  }  

If you want to do many-to-many mappings you’ll need to provide your own
“join” class with foreign keys to both of your mapped entities.

// DepartmentId Entity  class DepartmentId extends LongKeyedMapper[DepartmentId] with IdPK {    def getSingleton = DepartmentId    object name extends MappedString(this,100)  }  object DepartmentId extends DepartmentId with LongKeyedMetaMapper[DepartmentId] {    override def fieldOrder = List(name)  }  

Next, we define our join entity, as shown below.
It’s a LongKeyedMapper just like the rest of the entities,
but it only contains foreign key fields to the other entities.

// Join Entity  class DepartmentIdTag extends LongKeyedMapper[DepartmentIdTag] with IdPK {    def getSingleton = DepartmentIdTag    object departmentid extends MappedLongForeignKey(this,DepartmentId)    object Employee extends MappedLongForeignKey(this,Employee)  }  object DepartmentIdTag extends DepartmentIdTag with LongKeyedMetaMapper[DepartmentIdTag] {    def join (departmentid : DepartmentId, tx : Employee) =      this.create.departmentid(departmentid).Employee(tx).save  }  

To use the join entity, you’ll need to create a new instance and set the
appropriate foreign keys to point to the associated instances. As you can see,
we’ve defined a convenience method on our Expense meta object to do just that.
To make the many-to-many accessible as a field on our entities, we can use the
HasManyThrough trait, as shown below

// HasManyThrough for Many-to-Many Relationships  class Employee ... {    object departmentids extends HasManyThrough(this, DepartmentId,       DepartmentIdTag, DepartmentIdTag.departmentid, DepartmentIdTag.Employee)  }  

I noticed that department is spelled in two ways: departement department

Perhaps the mapper does not know how to list * results from a join operation
Have you tried the following

SELECT   e.birthday     as birthDay     ,   e.departmentId as departmentId ,  e.salary       as salaryFROM   Employee e     INNER JOIN Department d     ON e.departmentId = d.departmentIdWHERE     d.budget < 100 AND     e.salary > 10

Disclaimer: I have 0 experience with Mapper/Lift, but I do have experience with mapping query result sets to objects in Borland C++Builder, Delphi and Java. Some of these object-based systems are buggy and can't expand SELECT * to all the fields, so you need to EXPLICITLY tell them which fields to get.

In your case, you have e.departmentId and d.departmentId which can confuse Mapper into not knowing which one is the real departmentId in *. Some systems will actually return departmentId and departmentId_1 (this one gets the _1 tacked on to the end by default)Other systems just hang, error out, have unpredictable behavior.

I've also seen the existence or non-existence of the termination ';' character at the end be an issue in some canned SQL apps.

You can always run any query you like to with exec or runQuery:http://scala-tools.org/mvnsites/liftweb-2.4-M1/#net.liftweb.db.DBAlthough you can do joins in Mapper as well.

You could use OneToMany or ManyToMany traits

To use ManyToMany you put your join fields. See example code:

class Meeting extends LongKeyedMapper[Meeting] with IdPK with CreatedUpdated with OneToMany[Long, Meeting] with ManyToMany {  def getSingleton = Meeting  object owner extends MappedLongForeignKey(this, User)  object title extends MappedString(this, 100)  object beginDate extends MappedDateTime(this)  object endDate extends MappedDateTime(this)  object location extends MappedString(this,100)  object description extends MappedText(this)  object allDay extends MappedBoolean(this)  object users extends MappedManyToMany(MeetingUser, MeetingUser.meeting, MeetingUser.user, User)  object contacts extends MappedManyToMany(MeetingContact, MeetingContact.meeting, MeetingContact.contact, Contact)}

And Here is the joining entity.

class MeetingContact extends LongKeyedMapper[MeetingContact] with IdPK with CreatedUpdated {  def getSingleton = MeetingContact  object meeting extends MappedLongForeignKey(this, Meeting)  object contact extends MappedLongForeignKey(this, Contact)}object MeetingContact extends MeetingContact with LongKeyedMetaMapper[MeetingContact] {  def join(m: Meeting, c: Contact) = this.create.meeting(m).contact(c).save  def assignedTo(c: Contact) = this.findAll(By(MeetingContact.contact, c)).filter(_.meeting.obj.isDefined).map(_.meeting.obj.open_!)  override def beforeCreate() = MailSender.sendInviteToMeetingContact _ ::      super.beforeCreate}