test.sql

SELECT
  `t`.`id`                              AS `t0_c0`,
  `t`.`page_id`                         AS `t0_c1`,
  `t`.`publish_date`                    AS `t0_c2`,
  `t`.`box_type`                        AS `t0_c3`,
  `t`.`color_id`                        AS `t0_c4`,
  `t`.`title`                           AS `t0_c5`,
  `t`.`sub_headline`                    AS `t0_c6`,
  `t`.`text_color`                      AS `t0_c7`,
  `t`.`description`                     AS `t0_c8`,
  `t`.`icon_id`                         AS `t0_c9`,
  `t`.`icon_color_id`                   AS `t0_c10`,
  `t`.`p3_media_id`                     AS `t0_c11`,
  `t`.`status`                          AS `t0_c12`,
  `t`.`tags`                            AS `t0_c13`,
  `t`.`event_date`                      AS `t0_c14`,
  `icon`.`id`                           AS `t1_c0`,
  `icon`.`name`                         AS `t1_c1`,
  `icon`.`css_class`                    AS `t1_c2`,
  `icon`.`content`                      AS `t1_c3`,
  `color`.`id`                          AS `t2_c0`,
  `color`.`name`                        AS `t2_c1`,
  `color`.`hex`                         AS `t2_c2`,
  `iconColor`.`id`                      AS `t3_c0`,
  `iconColor`.`name`                    AS `t3_c1`,
  `iconColor`.`hex`                     AS `t3_c2`,
  `page`.`id`                           AS `t4_c0`,
  `page`.`default_menu_name`            AS `t4_c1`,
  `page`.`status`                       AS `t4_c2`,
  `page`.`name_id`                      AS `t4_c3`,
  `page`.`tree_parent_id`               AS `t4_c4`,
  `page`.`tree_position`                AS `t4_c5`,
  `page`.`default_page_title`           AS `t4_c6`,
  `page`.`layout`                       AS `t4_c7`,
  `page`.`view`                         AS `t4_c8`,
  `page`.`url_json`                     AS `t4_c9`,
  `page`.`default_url_param`            AS `t4_c10`,
  `page`.`default_keywords`             AS `t4_c11`,
  `page`.`default_description`          AS `t4_c12`,
  `page`.`custom_data_json`             AS `t4_c13`,
  `page`.`access_owner`                 AS `t4_c14`,
  `page`.`access_domain`                AS `t4_c15`,
  `page`.`access_read`                  AS `t4_c16`,
  `page`.`access_update`                AS `t4_c17`,
  `page`.`access_delete`                AS `t4_c18`,
  `page`.`access_append`                AS `t4_c19`,
  `page`.`copied_from_id`               AS `t4_c20`,
  `page`.`created_at`                   AS `t4_c21`,
  `page`.`updated_at`                   AS `t4_c22`,
  `p3PageTranslations`.`id`             AS `t5_c0`,
  `p3PageTranslations`.`p3_page_id`     AS `t5_c1`,
  `p3PageTranslations`.`language`       AS `t5_c2`,
  `p3PageTranslations`.`menu_name`      AS `t5_c3`,
  `p3PageTranslations`.`status`         AS `t5_c4`,
  `p3PageTranslations`.`page_title`     AS `t5_c5`,
  `p3PageTranslations`.`url_param`      AS `t5_c6`,
  `p3PageTranslations`.`keywords`       AS `t5_c7`,
  `p3PageTranslations`.`description`    AS `t5_c8`,
  `p3PageTranslations`.`access_owner`   AS `t5_c9`,
  `p3PageTranslations`.`access_read`    AS `t5_c10`,
  `p3PageTranslations`.`access_update`  AS `t5_c11`,
  `p3PageTranslations`.`access_delete`  AS `t5_c12`,
  `p3PageTranslations`.`copied_from_id` AS `t5_c13`,
  `p3PageTranslations`.`created_at`     AS `t5_c14`,
  `p3PageTranslations`.`updated_at`     AS `t5_c15`
FROM `box` `t` LEFT OUTER JOIN `icon` `icon` ON (`t`.`icon_id` = `icon`.`id`)
  LEFT OUTER JOIN `box_color` `color` ON (`t`.`color_id` = `color`.`id`)
  LEFT OUTER JOIN `box_color` `iconColor` ON (`t`.`icon_color_id` = `iconColor`.`id`)
  LEFT OUTER JOIN `p3_page` `page` ON (`t`.`page_id` = `page`.`id`)
  LEFT OUTER JOIN `p3_page_translation` `p3PageTranslations`
    ON (`p3PageTranslations`.`p3_page_id` = `page`.`id`) AND (p3PageTranslations.language = 'de')
WHERE ((t.status = 'publish' AND t.publish_date <= CURDATE()) AND (FIND_IN_SET('lorem', tags)))
ORDER BY t.publish_date
  DESC
LIMIT 30