5
\$\begingroup\$

This works, but it's huge.

We need to repeat each table column on each UNION SELECT, as well as the WHERE clause for DATE comparison.

( 
SELECT GROUP_CONCAT(APA_T.district), t.name 
FROM tbl_activity AS t 
INNER JOIN tbl_activity_package AS ap ON t.id = ap.activity_id 
INNER JOIN ( 
SELECT DISTINCT apa.district AS district, ( 
SELECT s1.activity_package_id 
FROM tbl_activity_package_address s1 
WHERE apa.district = s1.district 
ORDER BY s1.id DESC 
LIMIT 1 
) AS idActivityPackage 
FROM 
tbl_activity_package_address apa 
ORDER BY apa.district 
) AS APA_T 
ON ap.id = APA_T.idActivityPackage 
WHERE (ap.publication_date <= CURDATE()) 
AND (DATE_ADD(ap.publication_date, INTERVAL ap.publication_duration_in_days DAY) >= CURDATE()) 
GROUP BY t.name 
ORDER BY APA_T.district 
) 
UNION DISTINCT 
( 
SELECT GROUP_CONCAT(DISTINCT apa2.district ORDER BY apa2.district), t2.name 
FROM tbl_activity AS t2 
INNER JOIN tbl_activity_package AS ap2 ON t2.id = ap2.activity_id 
INNER JOIN tbl_activity_package_address AS apa2 ON ap2.id = apa2.activity_package_id 
WHERE (ap2.publication_date <= CURDATE()) 
AND (DATE_ADD(ap2.publication_date, INTERVAL ap2.publication_duration_in_days DAY) >= CURDATE()) 
GROUP BY t2.name 
ORDER BY apa2.district 
) 
LIMIT 6, 6

I have removed the WHERE CLAUSE for DATE comparisons, because I can't have it on this fiddle structure, but, the repetition is still an issue. I've been told about INTERSECT but found nothing about that on MySQL.

http://sqlfiddle.com/#!2/37dd94/51

\$\endgroup\$
4
\$\begingroup\$

Your SQL Fiddle and the question have inconsistent column names: the fiddle has id_activity_package, while the code in this question has activity_package_id. I'll consider the fiddle as supplementary information, and treat the code in the question as authoritative.


The two halves of the UNION are nearly identical, with the exception of the second INNER JOIN. Let's analyze it one step at a time.

  1. In the first half of the UNION, you join with APA_T, which is…

    SELECT DISTINCT apa.district AS district,
        (SELECT s1.activity_package_id
            FROM tbl_activity_package_address s1
            WHERE apa.district = s1.district
            ORDER BY s1.id DESC
            LIMIT 1
        ) AS idActivityPackage
        FROM tbl_activity_package_address apa 
        ORDER BY apa.district
    

    The DISTINCT there is superfluous, since the innermost SELECT is guaranteed to return at most one row per district.

    As I read it, APA_T is a subquery that lists the most recently added activity_package_id for each district. I think it's worth creating a view for clarity.

    CREATE VIEW latest_activity_package_address AS
    SELECT *
        FROM tbl_activity_package_address
        WHERE id IN (
            SELECT MAX(id)
                FROM tbl_activity_package_address AS latest_apa
                GROUP BY district
        );
    

    Then, the first half of the UNION becomes:

    SELECT GROUP_CONCAT(district ORDER BY district), t.name
        FROM tbl_activity AS t
            INNER JOIN tbl_activity_package AS ap
                ON t.id = ap.activity_id
            INNER JOIN latest_activity_package_address AS apa
                ON ap.id = apa.activity_package_id
        WHERE (ap.publication_date <= CURDATE())
            AND (DATE_ADD(ap.publication_date, INTERVAL ap.publication_duration_in_days DAY) >= CURDATE())
        GROUP BY t.name
        ORDER BY apa.district
    

    I've copied the ORDER BY clause into the GROUP_CONCAT(), where I think it belongs.

  2. Next, I'd like to point out that LIMIT 6,6 will produce non-deterministic results, since there is no ORDER BY clause on the UNION query. You've put an ORDER BY on each half of the UNION, but that doesn't guarantee that the result will be ordered the same way.

  3. Surprisingly, I haven't found a way to reduce duplication between the two halves of the UNION. However, there is now a nice parallelism which may be more aesthetically pleasing. You don't need to select new table aliases for the second half; they aren't in the same scope as the first half.

    SELECT GROUP_CONCAT(district ORDER BY district), t.name
        FROM tbl_activity AS t
            INNER JOIN tbl_activity_package AS ap
                ON t.id = ap.activity_id
            INNER JOIN latest_activity_package_address AS apa
                ON ap.id = apa.activity_package_id
        WHERE (ap.publication_date <= CURDATE())
            AND (DATE_ADD(ap.publication_date, INTERVAL ap.publication_duration_in_days DAY) >= CURDATE())
        GROUP BY t.name
        ORDER BY apa.district
    UNION DISTINCT
    SELECT GROUP_CONCAT(district ORDER BY district), t.name
        FROM tbl_activity AS t
            INNER JOIN tbl_activity_package AS ap
                ON t.id = ap.activity_id
            INNER JOIN tbl_activity_package_address AS apa
                ON ap.id = apa.activity_package_id
        WHERE (ap.publication_date <= CURDATE())
            AND (DATE_ADD(ap.publication_date, INTERVAL ap.publication_duration_in_days DAY) >= CURDATE())
        GROUP BY t.name
        ORDER BY apa.district
    
\$\endgroup\$
  • 1
    \$\begingroup\$ Thanks a lot for taking your time analysing the code. Please allow me some time to study. \$\endgroup\$ – MEM May 10 '14 at 21:53
2
\$\begingroup\$

The nesting is incomprehensible without indentation. For your benefit and for the benefit of anyone attempting to review this code, I've reformatted the code, changing nothing but whitespace.

(
    SELECT GROUP_CONCAT(APA_T.district), t.name
        FROM tbl_activity AS t
            INNER JOIN tbl_activity_package AS ap
                ON t.id = ap.activity_id
            INNER JOIN (
                SELECT DISTINCT apa.district AS district,
                    (SELECT s1.activity_package_id
                        FROM tbl_activity_package_address s1
                        WHERE apa.district = s1.district
                        ORDER BY s1.id DESC
                        LIMIT 1
                    ) AS idActivityPackage
                    FROM tbl_activity_package_address apa 
                    ORDER BY apa.district
            ) AS APA_T
                ON ap.id = APA_T.idActivityPackage
        WHERE (ap.publication_date <= CURDATE())
            AND (DATE_ADD(ap.publication_date, INTERVAL ap.publication_duration_in_days DAY) >= CURDATE())
        GROUP BY t.name
        ORDER BY APA_T.district
)
UNION DISTINCT
(
    SELECT GROUP_CONCAT(DISTINCT apa2.district ORDER BY apa2.district), t2.name
        FROM tbl_activity AS t2
            INNER JOIN tbl_activity_package AS ap2
                ON t2.id = ap2.activity_id
            INNER JOIN tbl_activity_package_address AS apa2
                ON ap2.id = apa2.activity_package_id
        WHERE (ap2.publication_date <= CURDATE())
            AND (DATE_ADD(ap2.publication_date, INTERVAL ap2.publication_duration_in_days DAY) >= CURDATE())
        GROUP BY t2.name
        ORDER BY apa2.district
)
LIMIT 6, 6
\$\endgroup\$
  • \$\begingroup\$ I have edit my question with your indentation work. Thanks. \$\endgroup\$ – MEM May 8 '14 at 18:50
  • \$\begingroup\$ Edit rolled back. Code in a question must be preserved, so as not to invalidate answers. \$\endgroup\$ – 200_success May 8 '14 at 18:51
  • \$\begingroup\$ It was preserved. Changing spaces will do nothing, but clarification. :) You suggest a edit, and I've done it. :) No? I believe your answer serves more an edit to the OP question. But that's my 2 cents. :) No issues either way. \$\endgroup\$ – MEM May 8 '14 at 18:53
  • \$\begingroup\$ No. As mentioned in the meta post that I linked to, all facets of the code are subject to review, including whitespace. \$\endgroup\$ – 200_success May 8 '14 at 18:55
  • 1
    \$\begingroup\$ I understand. Even if, the answer doesn't answer all, it answers some parts of it, by reviewing some parts of the code. It makes sense on a "code review" stackexchange. :) \$\endgroup\$ – MEM May 8 '14 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.