I came across this SQL query in a web app I am pitching to replace.

I'm not going to name the guilty party, nor make any comments.

      SELECT
        0 as zero,
        ff.f_id as f_id, f.number, f.primary_ff_id, f.template, f.`order` as f_order,
          fi.id as fi_id, fi.datetime, fi.parent_fi_id, fi.`order`, fi.u_id,
  
          ff.type, ff.id as ff_id, ff.sub_id, ff.label, ff.desc,  ff.`order` as ff_order,
          ff.type, ff.name,

          IFNULL(t.tag, IFNULL(select_ffv.string, IFNULL(ffv.number, IFNULL(ffv.string, ffv.text)))) as value,
          ffv_value.fi_id as multi_fi, ffv_value.string as multi_value,

          ffp.key as ffp_key, ffp.value as ffp_value
      FROM f_fieldset f
  
        LEFT JOIN fi_item fi ON
          fi.f_id = f.id
          ".($parent_fiid != 0 ? " AND fi.parent_fi_id = '".(strpos($parent_fiid,"-")===false ? $parent_fiid : 0)."'" : "")."
      LEFT JOIN ff_field ff ON
          ff.f_id = f.id
      LEFT JOIN ffp_param ffp ON
            ffp.ff_id = ff.id
      LEFT JOIN ffv_value ffv ON
          ffv.fi_id = fi.id AND
          ffv.ff_id = ff.id
      LEFT JOIN f_fieldset select_f ON
        ff.type = 'select' AND
        select_f.id = ff.sub_id
      LEFT JOIN ffv_value select_ffv ON
        select_ffv.fi_id = ffv.number AND
        select_ffv.ff_id = select_f.primary_ff_id
      LEFT JOIN f_fieldset multi_f ON
        ff.type = 'multi_select' AND
        multi_f.id = 1051
      LEFT JOIN fi_item multi_fi ON
        multi_f.id = multi_fi.f_id AND
        multi_fi.parent_fi_id = fi.id
      LEFT JOIN ffv_value multi_ffv ON
        multi_fi.id = multi_ffv.fi_id
      LEFT JOIN f_fieldset f_value ON
        f_value.id = ff.sub_id
      LEFT JOIN fi_item fi_value ON
        fi_value.f_id = f_value.id
      LEFT JOIN ffv_value ffv_value ON
        ffv_value.ff_id = f_value.primary_ff_id AND
        ffv_value.fi_id = fi_value.id AND
        multi_ffv.number = fi_value.id
      LEFT JOIN t_tags t ON
        t.id = ffv.number AND
        ff.f_id = 1013
      WHERE
        ".($fid ? "f.id = '".$fid."'" : "fi.parent_fi_id = '".(strpos($parent_fiid,"-")===false ? $parent_fiid : 0)."' OR f.id BETWEEN 1000 AND 1010")."
      ORDER BY
        IF(
          IFNULL(f.`order`,'user') = 'user',
          IFNULL(fi.`order`, 2000000000 + fi.id),
          IF(f.`order` = 'newest',
            4294967295 - fi.datetime,
            fi.datetime)
          ),
        ff.`order`
    "

comments powered by Disqus