Help with .sortBy in join

[Craig Tataryn]

I am using Slick with a MySql database and I have the following query, where I get an error stating:

[error]    java.sql.SQLException: Operand should contain 1 column(s) (SQLError.java:1078)

val subQuery =
  for {
    uc <- UserContentTable.query if uc.userId === userId.toString && adoptedClause(uc) && !uc.deleted
    c  <- ContentTable.query if uc.contentId === c.id
  } yield (uc, c)

  val query =
    UserTable.query.filter(_.id === userId.toString)
      .joinLeft(subQuery).on { case (u, (uc, c)) => u.id === uc.userId}
      .sortBy { case (u, subq) => subq.map { case (uc, c) => uc.created } }

      .map { case (u, subq) => (u, subq.map { case (uc, c) => c }) }

   

The SQL that is generated is:

select x2.x3, x2.x4, x2.x5, (case when (x6.`content_id` is null) then null else 1 end), x7.`id`, x7.`title`, x7.`source`, x7.`source_id`, x7.`content_type`, x7.`duration`, x7.`privacy_status`, x7.`thumbnail_url`, x7.`publisher`, x7.`default_language`
from (select `id` as x3, `email` as x4, `created` as x5 from `user` where `id` = 'b1455c42-e8e0-4b43-b738-17846fb85f22') x2
left outer join (`user_content` x6 inner join `content` x7
on (((x6.`user_id` = 'b1455c42-e8e0-4b43-b738-17846fb85f22')
and ((not (x6.`adopted` is not null)) or (not x6.`adopted`))) and (not x6.`deleted`))
and (x6.`content_id` = x7.`id`)) on x2.x3 = x6.`user_id`
order by ((case when (x6.`content_id` is null) then null else 1 end), x6.`created`)

If I remove the .sortBy everything works, but I need my results to be sorted by the user_content.created field.

The schema involved is:

CREATE TABLE `content` (
  `id` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `source` varchar(255) NOT NULL,
  `source_id` varchar(255) NOT NULL,
  `content_type` varchar(255) NOT NULL,
  `duration` int(11) NOT NULL,
  `privacy_status` varchar(255) NOT NULL,
  `thumbnail_url` varchar(255) DEFAULT NULL,
  `publisher` varchar(255) DEFAULT NULL,
  `default_language` varchar(255) DEFAULT NULL,
  UNIQUE KEY `pk_content` (`id`),
  UNIQUE KEY `unique_ck_source_source_id` (`source`,`source_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `user_content` (
  `user_id` varchar(255) NOT NULL,
  `content_id` varchar(255) NOT NULL,
  `adopted` tinyint(1) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `pk_user_content` (`user_id`,`content_id`),
  KEY `fk_user_content_content` (`content_id`),
  CONSTRAINT `fk_user_content_content` FOREIGN KEY (`content_id`) REFERENCES `content` (`id`),
  CONSTRAINT `fk_user_content_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Any help would be greated appreciated, thanks!

Craig

   
   
     
 
 
 

 
   
 

[akashic seer]

Have you tried copying and pasting the generated SQL into MySQL workbench and running it and letting it tell you what is wrong? I'd try that. You may have to replace some of that with actual column names or something, but Workbench will give you hints. Joins are hard to get right in generated SQL I had a class for it a long time ago. I had to run tons of queries with many joins etc to get the SQL generated correctly.

You may just want to try writing the SQL you need out to test it too. I think the error means you have nothing or null for the column value somehow. as in the executed code is turning out to be "order by null" or it is "order by"