Symfony Profiler

select 
  * 
from 
  (
    SELECT 
      distinct on (di.ref_id) di.ref_id, 
      'bible_' || trans.url || '_' || cb.url || '_' || cc1.ordering || (
        case when cc1.id <> cc2.id then '-' || cc2.ordering else '' end
      ) || '_' || cv1.ordering || (
        case when rb.startverse_id <> rb.endverse_id then '-' || cv2.ordering else '' end
      ) as multilink, 
      true as is_bibleref, 
      1 as section_order, 
      (
        case when trans.language_id in (
          select 
            language_id 
          from 
            expositiontranslation 
          where 
            id = 63465
        ) then 1 else case when trans.language_id = 123 then 2 else 3 end end
      ) as priority_order, 
      cb.url as book_url, 
      trans.url as trans_url, 
      cc1.ordering as chapter_order1, 
      cc2.ordering as chapter_order2, 
      cv1.ordering as verse_order1, 
      cv2.ordering as verse_order2, 
      trans.url as div_url, 
      0 as passage_order 
    FROM 
      refbible rb, 
      expositiontextunit t, 
      expositiontranslationtext tt, 
      expositiondisplayitem di, 
      canonicalbibleverse cv1, 
      canonicalbiblechapter cc1, 
      canonicalbiblebook cb, 
      canonicalbibleverse cv2, 
      canonicalbiblechapter cc2, 
      biblebook b, 
      bibletranslation trans 
    WHERE 
      t.passage_id = 48244 
      and t.id = tt.placement_id 
      and rb.container_id = di.ref_id 
      and di.container_id = tt.id 
      and di.ref_id is not null 
      and cv1.id = rb.startverse_id 
      and cv1.chapter_id = cc1.id 
      and cc1.book_id = cb.id 
      and cv2.id = rb.endverse_id 
      and cv2.chapter_id = cc2.id 
      and b.canonicalization_id = cb.id 
      and b.translation_id = trans.id 
      and trans.enabled 
    order by 
      di.ref_id, 
      priority_order asc, 
      trans.languagedefault desc
  ) as b 
union 
select 
  * 
from 
  (
    SELECT 
      distinct on (di.ref_id) di.ref_id, 
      (
        case when w.swedenborgtype is not null then 'swedenborg' else 'explanation' end
      ) || '_' || trans.url || (
        case when p.swedenborgsection is not null then '_' || p.swedenborgsection else '' end
      ) as multilink, 
      false as is_bibleref, 
      t2.ordering as section_order, 
      (
        case when trans.id = 63465 then 1 else case when trans.language_id in (
          select 
            language_id 
          from 
            expositiontranslation 
          where 
            id = 63465
        ) then 2 else case when trans.language_id = 123 then 3 else 4 end end end
      ) as priority_order, 
      trans.url as book_url, 
      trans.url as trans_url, 
      0 as chapter_order1, 
      0 as chapter_order2, 
      0 as verse_order1, 
      0 as verse_order2, 
      d.url as div_url, 
      p.ordering as passage_order 
    FROM 
      refexposition re, 
      expositiontextunit t, 
      expositiontextunit t2, 
      expositionpassage p, 
      expositiondivision d, 
      expositiontranslationtext tt, 
      expositiondisplayitem di, 
      expositionwork w, 
      expositiontranslation trans 
    WHERE 
      t.passage_id = 48244 
      and t2.id = re.startlocation_id 
      and p.id = t2.passage_id 
      and d.id = p.division_id 
      and re.container_id = di.ref_id 
      and t.id = tt.placement_id 
      and di.container_id = tt.id 
      and di.ref_id is not null 
      and d.work_id = w.id 
      and w.id = trans.work_id 
      and trans.url is not null 
      and trans.ispublic 
      and w.ispublic 
    order by 
      di.ref_id, 
      priority_order asc, 
      trans.islanguagedefault desc, 
      trans.isoriginal desc
  ) as e

select * from (SELECT distinct on (di.ref_id) di.ref_id, 'bible_' || trans.url || '_' || cb.url || '_' || cc1.ordering || (case when cc1.id <> cc2.id then '-' || cc2.ordering else '' end) || '_' || cv1.ordering || (case when rb.startverse_id <> rb.endverse_id then '-' || cv2.ordering else '' end) as multilink, true as is_bibleref, 1 as section_order, (case when trans.language_id in (select language_id from expositiontranslation where id = 63465) then 1 else case when trans.language_id = 123 then 2 else 3 end end ) as priority_order, cb.url as book_url, trans.url as trans_url, cc1.ordering as chapter_order1, cc2.ordering as chapter_order2, cv1.ordering as verse_order1, cv2.ordering as verse_order2, trans.url as div_url, 0 as passage_order FROM refbible rb, expositiontextunit t, expositiontranslationtext tt, expositiondisplayitem di, canonicalbibleverse cv1, canonicalbiblechapter cc1, canonicalbiblebook cb, canonicalbibleverse cv2, canonicalbiblechapter cc2, biblebook b, bibletranslation trans  WHERE t.passage_id = 48244 and t.id = tt.placement_id and rb.container_id = di.ref_id and di.container_id = tt.id and di.ref_id is not null and cv1.id = rb.startverse_id and cv1.chapter_id = cc1.id and cc1.book_id = cb.id and cv2.id = rb.endverse_id and cv2.chapter_id = cc2.id and b.canonicalization_id = cb.id and b.translation_id = trans.id and trans.enabled order by di.ref_id, priority_order asc, trans.languagedefault desc) as b union select * from (SELECT distinct on (di.ref_id) di.ref_id, (case when w.swedenborgtype is not null then 'swedenborg' else 'explanation' end) || '_' || trans.url || (case when p.swedenborgsection is not null then '_' || p.swedenborgsection else '' end) as multilink, false as is_bibleref, t2.ordering as section_order,  (case when trans.id = 63465 then 1 else case when trans.language_id in (select language_id from expositiontranslation where id = 63465) then 2 else case when trans.language_id = 123 then 3 else 4 end end end) as priority_order, trans.url as book_url, trans.url as trans_url, 0 as chapter_order1, 0 as chapter_order2, 0 as verse_order1, 0 as verse_order2, d.url as div_url, p.ordering as passage_order FROM refexposition re, expositiontextunit t, expositiontextunit t2, expositionpassage p, expositiondivision d, expositiontranslationtext tt, expositiondisplayitem di, expositionwork w, expositiontranslation trans WHERE t.passage_id = 48244 and t2.id = re.startlocation_id and p.id = t2.passage_id and d.id = p.division_id and re.container_id = di.ref_id and t.id = tt.placement_id and di.container_id = tt.id and di.ref_id is not null and d.work_id = w.id and w.id = trans.work_id and trans.url is not null and trans.ispublic and w.ispublic order by di.ref_id, priority_order asc, trans.islanguagedefault desc, trans.isoriginal desc) as e;

SELECT 
  v.id, 
  cv.ordering as verse_order, 
  scc.ordering as chapter_order, 
  t.id as translation_id, 
  scc.id as cchapter_id, 
  v.chapter_id, 
  'bible' || '_' || bt.url || '_' || cb.url || '_' || c.ordering || '_' || v.indexdisplay as bible_spec, 
  'explanation' || '_' || t.url as explanation_spec, 
  bt.url as bible_trans_url, 
  cb.url as book_url, 
  v.indexdisplay as verse_index, 
  case when t.language_id = 123 then 1 else case when t.language_id = 246 then 2 end end as lang_order, 
  bt.language_id 
FROM 
  (
    select 
      * 
    from 
      biblestory 
    where 
      level_id = 2
  ) as bs, 
  canonicalbibleverse cv, 
  (
    select 
      * 
    from 
      canonicalbibleverse_bibleverse 
    where 
      bibleverse_id in (171688)
  ) as cvv, 
  canonicalbiblechapter scc, 
  expositionwork w, 
  expositiontranslation t, 
  expositiontranslationtext tt, 
  story_work sw, 
  biblechapter c, 
  biblebook b, 
  bibletranslation bt, 
  canonicalbiblebook cb, 
  bibleverse v 
WHERE 
  w.ispublic 
  and t.ispublic 
  and t.language_id in (123, 246) 
  and v.id = cvv.bibleverse_id 
  and cv.id = cvv.canonicalbibleverse_id 
  and bs.startverse_id = cv.id 
  and cv.chapter_id = scc.id 
  and v.verse_id is null 
  and sw.story_id = bs.id 
  and w.id = sw.work_id 
  and t.work_id = w.id 
  and v.chapter_id = c.id 
  and b.id = c.book_id 
  and b.translation_id = bt.id 
  and scc.book_id = cb.id 
  and t.id = tt.translation_id 
  and tt.text is not null 
  and tt.text <> '' 
order by 
  cv.ordering, 
  lang_order, 
  t.is_modern desc, 
  w.ordering, 
  t.islanguagedefault desc 
limit 
  1

SELECT v.id, cv.ordering as verse_order, scc.ordering as chapter_order, t.id as translation_id, scc.id as cchapter_id, v.chapter_id, 'bible' || '_' || bt.url || '_' || cb.url  || '_' || c.ordering  || '_' || v.indexdisplay as bible_spec, 'explanation' || '_' || t.url as explanation_spec, bt.url as bible_trans_url, cb.url as book_url, v.indexdisplay as verse_index, case when t.language_id = 123 then 1 else case when t.language_id = 246 then 2 end  end as lang_order, bt.language_id FROM (select * from biblestory where level_id = 2) as bs, canonicalbibleverse cv, (select * from canonicalbibleverse_bibleverse where bibleverse_id in (171688)) as cvv,  canonicalbiblechapter scc, expositionwork w, expositiontranslation t, expositiontranslationtext tt, story_work sw, biblechapter c, biblebook b, bibletranslation bt, canonicalbiblebook cb, bibleverse v WHERE w.ispublic and t.ispublic and t.language_id in (123,246) and v.id = cvv.bibleverse_id and cv.id = cvv.canonicalbibleverse_id and bs.startverse_id = cv.id and cv.chapter_id = scc.id and v.verse_id is null and sw.story_id = bs.id and w.id = sw.work_id and t.work_id=w.id and v.chapter_id = c.id and b.id = c.book_id and b.translation_id=bt.id and scc.book_id = cb.id and t.id = tt.translation_id and tt.text is not null and tt.text <> '' order by cv.ordering, lang_order, t.is_modern desc, w.ordering, t.islanguagedefault desc limit 1;

select 
  t3.* 
from 
  (
    select 
      * 
    from 
      (
        SELECT 
          distinct on (tc.textunit_id, rc.id) tc.textunit_id, 
          rc.id as ref_id, 
          rc.originallinktext as link_text, 
          cc1.book_id, 
          rb.startverse_id as startloc_id, 
          rb.endverse_id as endloc_id, 
          true as is_bibleref, 
          'bible_' || trans.url || '_' || cb.url || '_' || cc1.ordering || (
            case when cc1.id <> cc2.id then '-' || cc2.ordering else '' end
          ) || '_' || cv1.ordering || (
            case when rb.startverse_id <> rb.endverse_id then '-' || cv2.ordering else '' end
          ) as multilink_text, 
          cc1.ordering as ordering1, 
          cv1.ordering as ordering2, 
          (
            case when trans.language_id = 123 then case when trans.languagedefault then 3 else 4 end end
          ) as priority_order, 
          cb.url as book_url, 
          trans.url as trans_url, 
          cc1.ordering as chapter_order1, 
          cc2.ordering as chapter_order2, 
          cv1.ordering as verse_order1, 
          cv2.ordering as verse_order2, 
          trans.url as div_url, 
          0 as passage_order 
        FROM 
          refbible rb, 
          refcombined rc, 
          textunit_combinedreference tc, 
          expositiontextunit t, 
          canonicalbibleverse cv1, 
          canonicalbiblechapter cc1, 
          canonicalbiblebook cb, 
          canonicalbibleverse_bibleverse cbb1, 
          bibleverse v1, 
          biblechapter c1, 
          canonicalbibleverse cv2, 
          canonicalbiblechapter cc2, 
          biblebook b, 
          bibletranslation trans 
        WHERE 
          t.passage_id = 48244 
          and t.id = tc.textunit_id 
          and rc.id = tc.combinedreference_id 
          and rc.id not in (
            437, 464, 594, 1112, 2203, 3453, 3543, 
            5352, 9338, 12285, 12471, 14754, 15542, 
            15650, 17845, 19951, 20049, 20797, 
            22382, 23112, 24615, 39354, 39355, 
            43788, 45778, 59675, 65533, 65539, 
            65540, 65543, 65546, 65547, 65548, 
            65551, 65552, 65553, 65555, 65557, 
            65560, 96440, 97297, 97611, 98517, 
            98628, 99055, 107571, 108512, 116866, 
            117164, 118961, 119217, 122122, 122123, 
            122126, 147035, 148271, 148272, 148274, 
            148275, 148276
          ) 
          and rb.container_id = rc.id 
          and cv1.id = rb.startverse_id 
          and cv1.chapter_id = cc1.id 
          and cc1.book_id = cb.id 
          and cv1.id = cbb1.canonicalbibleverse_id 
          and v1.id = cbb1.bibleverse_id 
          and v1.chapter_id = c1.id 
          and c1.book_id = b.id 
          and cv2.id = rb.endverse_id 
          and cv2.chapter_id = cc2.id 
          and b.translation_id = trans.id 
          and trans.enabled 
        order by 
          tc.textunit_id, 
          rc.id, 
          priority_order asc, 
          trans.languagedefault desc
      ) as b 
    union 
    select 
      * 
    from 
      (
        SELECT 
          distinct on (tc.textunit_id, rc.id) tc.textunit_id, 
          rc.id as ref_id, 
          rc.originallinktext as link_text, 
          d.work_id as book_id, 
          re.startlocation_id as startloc_id, 
          re.endlocation_id as endloc_id, 
          false as is_bibleref, 
          (
            case when w.swedenborgtype is not null then 'swedenborg_' else 'explanation_' end
          ) || trans.url || (
            case when p.swedenborgsection is not null then '_' || p.swedenborgsection else '' end
          ) as multilink_text, 
          p.ordering as ordering1, 
          t2.ordering as ordering2, 
          (
            case when trans.id = 63465 then 1 else case when trans.language_id = 123 then case when trans.islanguagedefault then 3 else 4 end end end
          ) as priority_order, 
          trans.url as book_url, 
          trans.url as trans_url, 
          0 as chapter_order1, 
          0 as chapter_order2, 
          0 as verse_order1, 
          0 as verse_order2, 
          d.url as div_url, 
          p.ordering as passage_order 
        FROM 
          refexposition re, 
          refcombined rc, 
          textunit_combinedreference tc, 
          expositiontextunit t, 
          expositiontextunit t2, 
          expositionpassage p, 
          expositiondivision d, 
          expositionwork w, 
          expositiontranslation trans, 
          expositiontranslationtext tt 
        WHERE 
          t.passage_id = 48244 
          and t.id = tc.textunit_id 
          and rc.id = tc.combinedreference_id 
          and rc.id not in (
            437, 464, 594, 1112, 2203, 3453, 3543, 
            5352, 9338, 12285, 12471, 14754, 15542, 
            15650, 17845, 19951, 20049, 20797, 
            22382, 23112, 24615, 39354, 39355, 
            43788, 45778, 59675, 65533, 65539, 
            65540, 65543, 65546, 65547, 65548, 
            65551, 65552, 65553, 65555, 65557, 
            65560, 96440, 97297, 97611, 98517, 
            98628, 99055, 107571, 108512, 116866, 
            117164, 118961, 119217, 122122, 122123, 
            122126, 147035, 148271, 148272, 148274, 
            148275, 148276
          ) 
          and re.container_id = rc.id 
          and t2.id = re.startlocation_id 
          and p.id = t2.passage_id 
          and d.id = p.division_id 
          and d.work_id = w.id 
          and w.id = trans.work_id 
          and trans.url is not null 
          and trans.ispublic 
          and w.ispublic 
          and t2.id = tt.placement_id 
          and trans.id = tt.translation_id 
        order by 
          tc.textunit_id, 
          rc.id, 
          priority_order asc, 
          trans.islanguagedefault desc, 
          trans.isoriginal desc
      ) as e
  ) as t3 
order by 
  split_part(link_text, ' ', 1), 
  book_id, 
  ordering1, 
  ordering2

select t3.* from  (select * from (SELECT distinct on (tc.textunit_id, rc.id) tc.textunit_id, rc.id as ref_id, rc.originallinktext as link_text, cc1.book_id, rb.startverse_id as startloc_id, rb.endverse_id as endloc_id, true as is_bibleref, 'bible_' || trans.url || '_' || cb.url || '_' || cc1.ordering || (case when cc1.id <> cc2.id then '-' || cc2.ordering else '' end) || '_' || cv1.ordering || (case when rb.startverse_id <> rb.endverse_id then '-' || cv2.ordering else '' end) as multilink_text, cc1.ordering as ordering1, cv1.ordering as ordering2, (case when trans.language_id = 123 then case when trans.languagedefault then 3 else 4 end end) as priority_order, cb.url as book_url, trans.url as trans_url, cc1.ordering as chapter_order1, cc2.ordering as chapter_order2, cv1.ordering as verse_order1, cv2.ordering as verse_order2, trans.url as div_url, 0 as passage_order  FROM refbible rb, refcombined rc, textunit_combinedreference tc, expositiontextunit t, canonicalbibleverse cv1, canonicalbiblechapter cc1, canonicalbiblebook cb, canonicalbibleverse_bibleverse cbb1, bibleverse v1, biblechapter c1, canonicalbibleverse cv2, canonicalbiblechapter cc2, biblebook b, bibletranslation trans WHERE t.passage_id = 48244 and t.id = tc.textunit_id and rc.id = tc.combinedreference_id  and rc.id not in (437,464,594,1112,2203,3453,3543,5352,9338,12285,12471,14754,15542,15650,17845,19951,20049,20797,22382,23112,24615,39354,39355,43788,45778,59675,65533,65539,65540,65543,65546,65547,65548,65551,65552,65553,65555,65557,65560,96440,97297,97611,98517,98628,99055,107571,108512,116866,117164,118961,119217,122122,122123,122126,147035,148271,148272,148274,148275,148276) and rb.container_id = rc.id and cv1.id = rb.startverse_id and cv1.chapter_id = cc1.id and cc1.book_id = cb.id and cv1.id = cbb1.canonicalbibleverse_id and v1.id = cbb1.bibleverse_id and v1.chapter_id = c1.id and c1.book_id = b.id and cv2.id = rb.endverse_id and cv2.chapter_id = cc2.id and b.translation_id = trans.id and trans.enabled order by tc.textunit_id, rc.id, priority_order asc, trans.languagedefault desc) as b union select * from (SELECT distinct on (tc.textunit_id, rc.id) tc.textunit_id, rc.id as ref_id, rc.originallinktext as link_text, d.work_id as book_id, re.startlocation_id as startloc_id, re.endlocation_id as endloc_id, false as is_bibleref, (case when w.swedenborgtype is not null then 'swedenborg_' else 'explanation_' end) || trans.url || (case when p.swedenborgsection is not null then '_' || p.swedenborgsection else '' end) as multilink_text, p.ordering as ordering1, t2.ordering as ordering2, (case when trans.id = 63465 then 1 else case when trans.language_id = 123 then case when trans.islanguagedefault then 3 else 4 end end end) as priority_order, trans.url as book_url, trans.url as trans_url, 0 as chapter_order1, 0 as chapter_order2, 0 as verse_order1, 0 as verse_order2, d.url as div_url, p.ordering as passage_order FROM refexposition re, refcombined rc, textunit_combinedreference tc, expositiontextunit t, expositiontextunit t2, expositionpassage p, expositiondivision d, expositionwork w, expositiontranslation trans, expositiontranslationtext tt WHERE t.passage_id = 48244 and t.id = tc.textunit_id and rc.id = tc.combinedreference_id  and rc.id not in (437,464,594,1112,2203,3453,3543,5352,9338,12285,12471,14754,15542,15650,17845,19951,20049,20797,22382,23112,24615,39354,39355,43788,45778,59675,65533,65539,65540,65543,65546,65547,65548,65551,65552,65553,65555,65557,65560,96440,97297,97611,98517,98628,99055,107571,108512,116866,117164,118961,119217,122122,122123,122126,147035,148271,148272,148274,148275,148276) and re.container_id = rc.id and t2.id = re.startlocation_id and p.id = t2.passage_id and d.id = p.division_id and d.work_id = w.id and w.id = trans.work_id and trans.url is not null and trans.ispublic and w.ispublic and t2.id = tt.placement_id and trans.id = tt.translation_id order by tc.textunit_id, rc.id, priority_order asc, trans.islanguagedefault desc, trans.isoriginal desc) as e) as t3 order by split_part(link_text, ' ', 1), book_id, ordering1, ordering2;

select 
  t3.* 
from 
  (
    select 
      * 
    from 
      (
        SELECT 
          distinct on (tc.textunit_id, rc.id) tc.textunit_id, 
          rc.id as ref_id, 
          rc.originallinktext as link_text, 
          cc1.book_id, 
          rb.startverse_id as startloc_id, 
          rb.endverse_id as endloc_id, 
          true as is_bibleref, 
          'bible_' || trans.url || '_' || cb.url || '_' || cc1.ordering || (
            case when cc1.id <> cc2.id then '-' || cc2.ordering else '' end
          ) || '_' || cv1.ordering || (
            case when rb.startverse_id <> rb.endverse_id then '-' || cv2.ordering else '' end
          ) as multilink_text, 
          cc1.ordering as ordering1, 
          cv1.ordering as ordering2, 
          (
            case when trans.language_id = 123 then case when trans.languagedefault then 3 else 4 end end
          ) as priority_order, 
          cb.url as book_url, 
          trans.url as trans_url, 
          cc1.ordering as chapter_order1, 
          cc2.ordering as chapter_order2, 
          cv1.ordering as verse_order1, 
          cv2.ordering as verse_order2, 
          trans.url as div_url, 
          0 as passage_order 
        FROM 
          refbible rb, 
          refcombined rc, 
          textunit_combinedreference tc, 
          expositiontextunit t, 
          canonicalbibleverse cv1, 
          canonicalbiblechapter cc1, 
          canonicalbiblebook cb, 
          canonicalbibleverse_bibleverse cbb1, 
          bibleverse v1, 
          biblechapter c1, 
          canonicalbibleverse cv2, 
          canonicalbiblechapter cc2, 
          biblebook b, 
          bibletranslation trans 
        WHERE 
          t.passage_id = 47544 
          and t.id = tc.textunit_id 
          and rc.id = tc.combinedreference_id 
          and rc.id not in (49919, 147120, 147121) 
          and rb.container_id = rc.id 
          and cv1.id = rb.startverse_id 
          and cv1.chapter_id = cc1.id 
          and cc1.book_id = cb.id 
          and cv1.id = cbb1.canonicalbibleverse_id 
          and v1.id = cbb1.bibleverse_id 
          and v1.chapter_id = c1.id 
          and c1.book_id = b.id 
          and cv2.id = rb.endverse_id 
          and cv2.chapter_id = cc2.id 
          and b.translation_id = trans.id 
          and trans.enabled 
        order by 
          tc.textunit_id, 
          rc.id, 
          priority_order asc, 
          trans.languagedefault desc
      ) as b 
    union 
    select 
      * 
    from 
      (
        SELECT 
          distinct on (tc.textunit_id, rc.id) tc.textunit_id, 
          rc.id as ref_id, 
          rc.originallinktext as link_text, 
          d.work_id as book_id, 
          re.startlocation_id as startloc_id, 
          re.endlocation_id as endloc_id, 
          false as is_bibleref, 
          (
            case when w.swedenborgtype is not null then 'swedenborg_' else 'explanation_' end
          ) || trans.url || (
            case when p.swedenborgsection is not null then '_' || p.swedenborgsection else '' end
          ) as multilink_text, 
          p.ordering as ordering1, 
          t2.ordering as ordering2, 
          (
            case when trans.id = 16407 then 1 else case when trans.language_id = 123 then case when trans.islanguagedefault then 3 else 4 end end end
          ) as priority_order, 
          trans.url as book_url, 
          trans.url as trans_url, 
          0 as chapter_order1, 
          0 as chapter_order2, 
          0 as verse_order1, 
          0 as verse_order2, 
          d.url as div_url, 
          p.ordering as passage_order 
        FROM 
          refexposition re, 
          refcombined rc, 
          textunit_combinedreference tc, 
          expositiontextunit t, 
          expositiontextunit t2, 
          expositionpassage p, 
          expositiondivision d, 
          expositionwork w, 
          expositiontranslation trans, 
          expositiontranslationtext tt 
        WHERE 
          t.passage_id = 47544 
          and t.id = tc.textunit_id 
          and rc.id = tc.combinedreference_id 
          and rc.id not in (49919, 147120, 147121) 
          and re.container_id = rc.id 
          and t2.id = re.startlocation_id 
          and p.id = t2.passage_id 
          and d.id = p.division_id 
          and d.work_id = w.id 
          and w.id = trans.work_id 
          and trans.url is not null 
          and trans.ispublic 
          and w.ispublic 
          and t2.id = tt.placement_id 
          and trans.id = tt.translation_id 
        order by 
          tc.textunit_id, 
          rc.id, 
          priority_order asc, 
          trans.islanguagedefault desc, 
          trans.isoriginal desc
      ) as e
  ) as t3 
order by 
  split_part(link_text, ' ', 1), 
  book_id, 
  ordering1, 
  ordering2

select t3.* from  (select * from (SELECT distinct on (tc.textunit_id, rc.id) tc.textunit_id, rc.id as ref_id, rc.originallinktext as link_text, cc1.book_id, rb.startverse_id as startloc_id, rb.endverse_id as endloc_id, true as is_bibleref, 'bible_' || trans.url || '_' || cb.url || '_' || cc1.ordering || (case when cc1.id <> cc2.id then '-' || cc2.ordering else '' end) || '_' || cv1.ordering || (case when rb.startverse_id <> rb.endverse_id then '-' || cv2.ordering else '' end) as multilink_text, cc1.ordering as ordering1, cv1.ordering as ordering2, (case when trans.language_id = 123 then case when trans.languagedefault then 3 else 4 end end) as priority_order, cb.url as book_url, trans.url as trans_url, cc1.ordering as chapter_order1, cc2.ordering as chapter_order2, cv1.ordering as verse_order1, cv2.ordering as verse_order2, trans.url as div_url, 0 as passage_order  FROM refbible rb, refcombined rc, textunit_combinedreference tc, expositiontextunit t, canonicalbibleverse cv1, canonicalbiblechapter cc1, canonicalbiblebook cb, canonicalbibleverse_bibleverse cbb1, bibleverse v1, biblechapter c1, canonicalbibleverse cv2, canonicalbiblechapter cc2, biblebook b, bibletranslation trans WHERE t.passage_id = 47544 and t.id = tc.textunit_id and rc.id = tc.combinedreference_id  and rc.id not in (49919,147120,147121) and rb.container_id = rc.id and cv1.id = rb.startverse_id and cv1.chapter_id = cc1.id and cc1.book_id = cb.id and cv1.id = cbb1.canonicalbibleverse_id and v1.id = cbb1.bibleverse_id and v1.chapter_id = c1.id and c1.book_id = b.id and cv2.id = rb.endverse_id and cv2.chapter_id = cc2.id and b.translation_id = trans.id and trans.enabled order by tc.textunit_id, rc.id, priority_order asc, trans.languagedefault desc) as b union select * from (SELECT distinct on (tc.textunit_id, rc.id) tc.textunit_id, rc.id as ref_id, rc.originallinktext as link_text, d.work_id as book_id, re.startlocation_id as startloc_id, re.endlocation_id as endloc_id, false as is_bibleref, (case when w.swedenborgtype is not null then 'swedenborg_' else 'explanation_' end) || trans.url || (case when p.swedenborgsection is not null then '_' || p.swedenborgsection else '' end) as multilink_text, p.ordering as ordering1, t2.ordering as ordering2, (case when trans.id = 16407 then 1 else case when trans.language_id = 123 then case when trans.islanguagedefault then 3 else 4 end end end) as priority_order, trans.url as book_url, trans.url as trans_url, 0 as chapter_order1, 0 as chapter_order2, 0 as verse_order1, 0 as verse_order2, d.url as div_url, p.ordering as passage_order FROM refexposition re, refcombined rc, textunit_combinedreference tc, expositiontextunit t, expositiontextunit t2, expositionpassage p, expositiondivision d, expositionwork w, expositiontranslation trans, expositiontranslationtext tt WHERE t.passage_id = 47544 and t.id = tc.textunit_id and rc.id = tc.combinedreference_id  and rc.id not in (49919,147120,147121) and re.container_id = rc.id and t2.id = re.startlocation_id and p.id = t2.passage_id and d.id = p.division_id and d.work_id = w.id and w.id = trans.work_id and trans.url is not null and trans.ispublic and w.ispublic and t2.id = tt.placement_id and trans.id = tt.translation_id order by tc.textunit_id, rc.id, priority_order asc, trans.islanguagedefault desc, trans.isoriginal desc) as e) as t3 order by split_part(link_text, ' ', 1), book_id, ordering1, ordering2;