Multiple rows delete with sortBy and drop not working in Play Slick 0.8.0 (Slick 2.1)

[lalkhum]

Hi @cvogt (Hope you do not mind being mentioned) My delete query does not work and gives the error. Query: val someData = ..... // returns Query[Table, (x1, x2, ...), Seq] someData.sortBy(_.x1.desc).drop(5).delete

Error: scala.slick.SlickException: Invalid query for DELETE statement: A single source table is required, found: List((s2,Comprehension))

I wonder if "sortBy" and "drop" are not supported for multiple-rows delete in Slick 2.1?

[cvogt]

Yeah, I think you are right about sortBy and drop getting in the way. Error message could be better. @szeiger can you confirm this ?

[lalkhum]

Actually, it is working if I drop "drop" bit from the query. @cvogt

It would really be cool if you guys could support this. :)

Thanks

[cvogt]

how would you express that in SQL?

[lalkhum]

@cvogt, it would something like:

create view temp_cv as select version from MyTable where status='archived' order by last_modified desc limit 1;

delete from MyTable where status='archived' and version not in (select version from temp_cv);

drop view temp_cv;

Or maybe put this in a stored procedure.

[cvogt]

It's interesting, but I doubt Stefan or I will be able to spend time on this in the near future. But Slick is open source and does benefit from community contributions. Also we are also planning on putting some effort into making it easier to contribute to Slick soon. So maybe you or someone in the community will step up :).

You example SQL uses several queries. Right now Slick translates every query into exactly one SQL query. So this would be new and interesting questions would be how something like this relates to consistency and transactions.

However, maybe there is another way I just thought of like this:

DELETE FROM Mytable WHERE primaryKeyFields NOT IN (SELECT primaryKeysFields FROM MyTable ORDER BY last_modified DESC LIMIT 5,0)

Also in newer SQL versions there is the TOP operator. See http://stackoverflow.com/questions/8955897/how-to-delete-the-top-1000-rows-from-a-table-using-sql-server-2008

[lalkhum]

I was trying to run the query that you have given above, and I got the following error,

 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Mysql version at my machine is:

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.6.25, for osx10.8 (x86_64) using  EditLine wrapper

I suppose the SQL version that you use at Slick supports the above query (subquery with LIMIT) already. I would be interested in contributing to resolve this issue. Please let me know the hows on contribution. :)