New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Missing UNION ALL for SQLSubQueries #129

Closed
letiro opened this Issue Apr 17, 2012 · 3 comments

Comments

Projects
None yet
2 participants
@letiro

letiro commented Apr 17, 2012

Hello,

I posted the issue in the google group, and as Timo Westkämper pointed out it seems that this case is not yet properly supported.

Original Post:

https://groups.google.com/forum/#!topic/querydsl/E9yNWkBe3uw

I'm trying to to achieve the following SQL query using QueryDSL.
I was able to build almost the whole statement, but only with a 'normal' UNION of the SubQueries, and not a UNION ALL.

SQL: (https://gist.github.com/2399663)

SELECT SUM(x.length), 
       COUNT(x.port), 
       x.port 
FROM   (SELECT source_port AS port, 
               tcp_length  AS length 
        FROM   droid_tcp_segment 
               JOIN droid_proc_res_tcp_segmnets 
                 ON tcp_segm_id = id 
        WHERE  source_port < 1024 
               AND droid_proc_res_id = 1 
        UNION ALL 
        SELECT dest_port  AS port, 
               tcp_length AS length 
        FROM   droid_tcp_segment 
               JOIN droid_proc_res_tcp_segmnets 
                 ON tcp_segm_id = id 
        WHERE  dest_port < 1024 
               AND droid_proc_res_id = 1 
        UNION ALL 
        SELECT dest_port AS port, 
               length    AS length 
        FROM   droid_udp_packet 
               JOIN droid_proc_res_udp_packets 
                 ON udp_packet_id = id 
        WHERE  dest_port < 1024 
               AND droid_proc_res_id = 1 
        UNION ALL 
        SELECT source_port AS port, 
               length      AS length 
        FROM   droid_udp_packet 
               JOIN droid_proc_res_udp_packets 
                 ON udp_packet_id = id 
        WHERE  source_port < 1024 
               AND droid_proc_res_id = 1) AS x 
GROUP  BY x.port 

Current Code: (https://gist.github.com/7ad8fb9ebeb3345e3562)

JPASQLQuery sqlQuery = new JPASQLQuery(
        DroidProcessingResult.entityManager(), new MySQLTemplates());
QSqlDroidTcpSegment tcpSeg = QSqlDroidTcpSegment.droidTcpSegment;
QSqlDroidProcessingResult procRes = QSqlDroidProcessingResult.droidProcessingResult;
QSqlDroidUdpPacket udpPacket = QSqlDroidUdpPacket.droidUdpPacket;
QSqlDroidProcResTcpSegmnets procResTcpSeg = QSqlDroidProcResTcpSegmnets.droidProcResTcpSegmnets;
QSqlDroidProcResUdpPackets procResUdpPkt = QSqlDroidProcResUdpPackets.droidProcResUdpPackets;

NumberPath<Integer> port = new NumberPath<Integer>(Integer.class,
        "port");
NumberPath<Integer> length = new NumberPath<Integer>(Integer.class,
        "length");

ListSubQuery<com.mysema.query.Tuple> subTcpSeg1 = new SQLSubQuery()
        .from(tcpSeg)
        .innerJoin(procResTcpSeg)
        .on(procResTcpSeg.tcpSegmId.eq(tcpSeg.id))
        .innerJoin(procRes)
        .on(procResTcpSeg.droidProcResId.eq(procRes.id))
        .where(tcpSeg.sourcePort.lt(1024).and(
                procRes.id.eq(processingResultId)))
        .list(new QTuple(tcpSeg.sourcePort.as(port), tcpSeg.tcpLength
                .as(length)));

ListSubQuery<com.mysema.query.Tuple> subTcpSeg2 = new SQLSubQuery()
        .from(tcpSeg)
        .innerJoin(procResTcpSeg)
        .on(procResTcpSeg.tcpSegmId.eq(tcpSeg.id))
        .innerJoin(procRes)
        .on(procResTcpSeg.droidProcResId.eq(procRes.id))
        .where(tcpSeg.destPort.lt(1024).and(
                procRes.id.eq(processingResultId)))
        .list(new QTuple(tcpSeg.destPort.as(port), tcpSeg.tcpLength
                .as(length)));

ListSubQuery<com.mysema.query.Tuple> subUdpPkt1 = new SQLSubQuery()
        .from(udpPacket)
        .innerJoin(procResUdpPkt)
        .on(procResUdpPkt.udpPacketId.eq(udpPacket.id))
        .innerJoin(procRes)
        .on(procResUdpPkt.droidProcResId.eq(procRes.id))
        .where(udpPacket.sourcePort.lt(1024).and(
                procRes.id.eq(processingResultId)))
        .list(new QTuple(udpPacket.sourcePort.as(port),
                udpPacket.length.as(length)));

ListSubQuery<com.mysema.query.Tuple> subUdpPkt2 = new SQLSubQuery()
        .from(udpPacket)
        .innerJoin(procResUdpPkt)
        .on(procResUdpPkt.udpPacketId.eq(udpPacket.id))
        .innerJoin(procRes)
        .on(procResUdpPkt.droidProcResId.eq(procRes.id))
        .where(udpPacket.destPort.lt(1024).and(
                procRes.id.eq(processingResultId)))
        .list(new QTuple(udpPacket.destPort.as(port), udpPacket.length
                .as(length)));

String subQueryAlias = "sub";
PathBuilder<Tuple> sub = new PathBuilder<Tuple>(Tuple.class,
        subQueryAlias);

SimpleExpression<List<com.mysema.query.Tuple>> union = new SQLSubQuery()
        .union(subTcpSeg1, subTcpSeg2, subUdpPkt1, subUdpPkt2).as(
                subQueryAlias);

List<Object[]> trafficPortCounts = sqlQuery.from(union)
        .list(sub.get(length).sum(), sub.get(length).count(),
                sub.get(port));

If i try to use the UNION ALL of the JPASqlQuery like

 List<com.mysema.query.Tuple> tuples = sqlQuery.unionAll(subTcpSeg1, subTcpSeg2, subUdpPkt1, subUdpPkt2).list()

I have no possibility to select or group the content.

So my question is if there is some way to achieve a UNION ALL with my first approach,
or if i can somehow add some further statements (select, group by) to the second approach,
or if there's a different way to solve the problem?

timowest added a commit that referenced this issue May 4, 2012

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest May 4, 2012

Member

It is now possible to refer to a union query via an alias like this

SimpleSubQuery<Object[]> sq1 = sq().from(employee).unique(employee.id, employee.firstname);
SimpleSubQuery<Object[]> sq2 = sq().from(employee).unique(employee.id, employee.firstname);
query().union(employee, sq1, sq2).list(employee.id.count());

This should enable your use case

Member

timowest commented May 4, 2012

It is now possible to refer to a union query via an alias like this

SimpleSubQuery<Object[]> sq1 = sq().from(employee).unique(employee.id, employee.firstname);
SimpleSubQuery<Object[]> sq2 = sq().from(employee).unique(employee.id, employee.firstname);
query().union(employee, sq1, sq2).list(employee.id.count());

This should enable your use case

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest May 25, 2012

Member

Released in 2.6.0

Member

timowest commented May 25, 2012

Released in 2.6.0

@timowest timowest closed this May 25, 2012

@letiro

This comment has been minimized.

Show comment
Hide comment
@letiro

letiro May 30, 2012

Thank you very much for looking into the issue. My use case is now perfectly supported.

Sorry for the late reply (had no time to work on it lately)!

letiro commented May 30, 2012

Thank you very much for looking into the issue. My use case is now perfectly supported.

Sorry for the late reply (had no time to work on it lately)!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment