Squeryl relation 1:N

Question

I experienced some troubles with Squeryl ORM. Very basic functionality like select or insert rows without relations works fine, problems begin when I try to get data with relation 1:N in trivial scenario. In test DB I create two tables Questions and Answers. One question can contain several answers. Here is tables creation SQL:

DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nid` int(11) NOT NULL,
  `text` text NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `questions` (`id`, `nid`, `text`, `active`)
VALUES
(1,1,'test question',1),
(2,2,'second question',1);

DROP TABLE IF EXISTS `answers`;
CREATE TABLE `answers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(11) unsigned NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `qwerty` (`question_id`),
  CONSTRAINT `qwerty` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `answers` (`id`, `question_id`, `text`)
VALUES
(1,1,'test answer');

And here is Scala code with model classes:

class BaseEntity extends KeyedEntity[Long] { val id: Long = 0 }

class Question(var nid: Long, var text: String, 
      var active: Integer) extends BaseEntity {

    def this() = this(0, "", 1)
    lazy val Answers: List[Answer] = DataBase.answersToQuestion.left(this).toList
}

class Answer(var question_id: Long, var text: String) extends BaseEntity { 
    def this() = this(0, "")
    lazy val Question: Question = DataBase.answersToQuestion.right(this).head
}

object DataBase extends Schema {
    val questions = table[Question]("questions")
    val answers = table[Answer]("answers")
    val answersToQuestion = oneToManyRelation(questions, answers)
        .via((q, a) => q.id === a.question_id)

    on(answers)(a => declare( a.id is (autoIncremented) ))
    on(questions)(q => declare( q.id is (autoIncremented) ))
}

So on data selection I get full Question object but Answers is always null.

var question: Option[Question] = inTransaction { DataBase.questions.lookup(1L) }
//question.Answers is null !!

Can someone please look at is and tell me what is my mistake? 

Are there any errors returned from SQL to your database connector? You might want to start there. — Squeegy, Feb 24 '16 at 20:33

score 0 · Answer 1 · answered Feb 25 '16 at 20:30

0

Maybe I just hope for another behavior or misconfigured ORM somehow, although I get null in debugger, but it is work fine this way for Answer class:

def getQuestion = inTransaction(Question.headOption)

And this way for Question:

def getAnswers = inTransaction(Answers.toList)

Hope this would be helpful for someone who is new to Squeryl and experienced the same problem as I do.

Don't mark question as answered so far cause maybe there is better solution.

answered Feb 25 '16 at 20:30

Greebo

13
2

All calls to the database need to happen within a transaction, so it makes sense that requesting the answers outside of one would not work - I am just surprised there was no error, as Squeryl would usually thrown one. In any case, if you removed the `lazy` it would probably also work for you, although it would populate that on retrieval. Alternately you could also add inTransaction in the original object - like: `lazy val Answers: List[Answer] = inTransaction { DataBase.answersToQuestion.left(this).toList }`. `inTransaction` will join an existing transaction if one exists, so nesting is fine – jcern Feb 26 '16 at 20:51

Squeryl relation 1:N

1 Answers1