前提・実現したいこと
scala・play framework・slick・mySQLでwebアプリを作成しています、
その中で、DBからのデータ取得時にロジックで作成したリスト値をSQLで使い、データの絞り込みや参照するテーブルを変更する処理があります。
そこでロジックで作成した値をQuery
型に変換してjoin
することで絞り込みを行うことを考えたのですが、
リスト値のサイズが少ないときは正常でも多くなるとネストが多すぎるというエラーになってしまいます。
回避の仕方やより良い実装についてご教示ください。
その際、ページング処理が必須であり、絞り込みなしではSQL結果のレコードは数百万件ほど返り得ます。
そのため一度全データを取得してロジックでfilter
などを行うことは可能な限り避け、SQLで完結させたいです。
またDB側を変更することはできません。
該当のソースコード
scala
1val fromLogics: Iterable[Hoge] = <ロジックでのデータ生成処理> 2val base = fromLogics.map(e => Query(e.foo, e.hogefoo, e.bar)).reduceLeft(_ ++ _) // ← ここで`select ... from ... union all select... from ... union all ...` が大量に作られてしまい、エラーになると考えている 3val query = base.joinLeft(table).on{case (b, t) => b.foo === t.foo && b.hogefoo === t.hogefoo} 4 .joinLeft(table2).on{case ((b, _), t2) => b.foo === t2.foo && b.hogefoo === t2.hogefoo} 5 .map{case ((b, t), t2) => (b._1, Case.If(b._3 === aaa).Then(t.map(_._3)).If(b._3 === bbb).Then(t2.map(_._3)).Else(None))} 6 .filter(_._2.isDefined) 7query.<ソートやページングなど>
発生している問題・エラーメッセージ
- エラー内容
java.sql.SQLException: Too high level of nesting for select at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) at slick.jdbc.LoggingPreparedStatement.$anonfun$execute$5(LoggingStatement.scala:153) at scala.runtime.java8.JFunction0$mcZ$sp.apply(JFunction0$mcZ$sp.java:12) at slick.jdbc.LoggingStatement.logged(LoggingStatement.scala:84) at slick.jdbc.LoggingPreparedStatement.execute(LoggingStatement.scala:153) at slick.jdbc.StatementInvoker.results(StatementInvoker.scala:38) at slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:21) at slick.jdbc.Invoker.foreach(Invoker.scala:47) at slick.jdbc.Invoker.foreach$(Invoker.scala:46) at slick.jdbc.StatementInvoker.foreach(StatementInvoker.scala:15) at slick.jdbc.StreamingInvokerAction.run(StreamingInvokerAction.scala:22) at slick.jdbc.StreamingInvokerAction.run$(StreamingInvokerAction.scala:20) at slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1.run(JdbcActionComponent.scala:215) at slick.jdbc.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1.run(JdbcActionComponent.scala:215) at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:242) at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:242) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)
- 出力されたSQLのイメージ
SQL
1SELECT <columns> 2FROM ( 3 SELECT <columns> 4 FROM (( 5 SELECT x85.x86 AS x41,x85.x87 AS x42,x85.x88 AS x43 6 FROM (( 7 SELECT x89.x90 AS x86,x89.x91 AS x87,x89.x92 AS x88 8 FROM (( 9 10 ... 11 12 SELECT x153.x154 AS x150,x153.x155 AS x151,x153.x156 AS x152 13 FROM (( 14 SELECT x157.x158 AS x154,x157.x159 AS x155,x157.x160 AS x156 15 FROM (( 16 SELECT <data from logic> AS x158,<data from logic> AS x159,<data from logic> AS x160 17 ) UNION ALL( 18 SELECT <data from logic> AS x158,<data from logic> AS x159,<data from logic> AS x160 19 )) x157 20 ) UNION ALL( 21 SELECT <data from logic> AS x154,<data from logic> AS x155,<data from logic> AS x156 22 )) x153 23 24 ... 25 26 ) UNION ALL( 27 SELECT <data from logic> AS x90,<data from logic> AS x91,<data from logic> AS x92 28 )) x89 29 ) UNION ALL( 30 SELECT <data from logic> AS x86,<data from logic> AS x87,<data from logic> AS x88 31 )) x85 32 ) UNION ALL( 33 SELECT <data from logic> AS x41,<data from logic> AS x42,<data from logic> AS x43 34 )) x40 35 LEFT OUTER JOIN <table> x44 36 37 ... 38 39) x2 40WHERE 41 42 ...
検討案
Query
型への変換時、slickが作成するSQLのようにfrom句がなければネストもなくなり、回避できるのではと考えた。そのためその部分のみSQLで作成し、残りをslickで実装するselect hoge, foo union all select hoge2, foo2 ...
→ これができれば解決しそうだがSQLとslickを組み合わせた実装の仕方がわかっていないQuery
型へ変換したデータをgrouped
で分割するなどで1回のSQLはエラーにならないネスト量にして、複数回SQLを発行し、それをロジックで結合する
→Query
型へ変換したデータは数百万件になるケースもあるため、何万回ものSQLが発行される可能性があることが懸念- slickを使わずSQLで作成する
→ 作成の難易度や改修が前提の機能となるため、メンテナンス性が懸念
補足情報(FW/ツールのバージョンなど)
バージョン
- scala: 2.12.3
- java: 8
- play: 2.6
- play-slick: 3.0.2
- mySQL: 5.7
あなたの回答
tips
プレビュー