Ari: ddlZddlZddlZddlmZddlmZmZmZm Z ddl m Z m Z m Z ddl Z ddlZddlZddlZddlddlmZddlZddlZddlZddlZddlZddlZddlZddlmZej<ZgdZ gd Z!gd Z"e"gd z Z"e"gd z Z"e"d gz Z"e"gdz Z"gdZ#gdZ$e%e e!ze"ze$ze#zZ"gdZ&gdZ'e%e e!ze$zZ(gdZ)ddZ*ddZ+GddejjXjZZ-y)N) namedtuple)ListSetDictOptional) dataclassfieldfields)*)SQLAlchemyDataDriver) BeautifulSoup)иввонечтооннаяссокакатовсеонатакегонодатыкужевызабыпо толькоеемнебыловототменяещенетоизему теперь когдадажену вдруглиеслиужеилинибытьбылнегодовас нибудь опятьужвамведьтам потомсебя ничегоей можетонитутгдеестьнадонейдлямытебяихчембыласамчтоббез будточегоразтожесебепод будетж тогдактоэтоттого потому этого какой совсемним здесьэтомодин почтимойтем чтобынее сейчасбыликуда зачемвсехникогда можнопринаконецuдваоб другойхоть посленадu большетот черезэтинаспро всегоних какаяu много развеuтриэтумоявпрочемu хорошосвоюэтой перед иногда лучшечутьтомu нельзя такойим более всегдаконечновсю между)imemymyselfweourours ourselvesyouyouryoursyourself yourselveshehimhishimselfsheherhersherselfititsitselftheythemtheirtheirs themselveswhatwhichwhowhomthisthatthesethoseamisarewaswerebebeenbeinghavehashadhavingdodoesdiddoingaantheandbutiforbecauseasuntilwhileofatbyforwithaboutagainstbetweenintothroughduringbeforeafterabovebelowtofromupdowninoutonoffoverunderagainfurtherthenonceheretherewhenwherewhyhowallanybotheachfewmoremostothersomesuchnonornotonlyownsamesothantooverystcanwilljustdonshouldnow)u сделаuделuсделuделаэтrrrrrrrrrrrruвсёrrrrr r!r"r#r$r%r&r'r(r)rrr?r-r.ruещёr1r2r3rr5r6r7r8r9r:r;r<r=r>r?r?rrBr%rDu нибытьrErFr%rHrIrJrKrLruмочьrOrPrQrRrSrrUrVr!rOrYr?r[r\r]r^rr`rarKrcr?rerfrgrhrrjэтоrlrmrOrorrqrrrsrtrurrwr?ryrzвесьr|r}r~rr2rrrrrrrhrVrr rOrlrrhrsruсвойrhrrrrrrOrrrr r).u такоеrirrrrWrMrZrrdrr+r4rnrAr0rrrkrbr{rrGrrNrCr*rrrprxr/rrTrrr,rrXrDrrr_r@rrv)u нужноuхотяuзатоu именноu вообщеuтудаuтакиu делатьuпомнитьu менееuоноu кромеuтвойu откудаuсчитатьвнеuтойu каждыйu никтоuкоторыйu затемu вопросuобаuдлит)rlruещuнибuмочrrвеr"uсвоr"ruнужнuхотзатu вообщuтудruменruкромuтвоr!ruкаждuниктu которr#r) rrlu почемуryrgrQr6u можешьrY)docwwwhttphttpsmailto)u авторuбизнес-процессuвладелецuдатuисполнителuконтрагентuлогическu отделuпостановщикuприоритетu проектuпроцессu спискuстандартнu статусuтип)approvbaseclosedefaultdocumentopentasku бизнесuдокументu задачu задачаu закрытu обычнu системu созданuчерновик)textml_textnametagscomments addon_fields key_phrasescddlm}m}ddlm}||}|j D]-\}}||j vr|j|||}/t|j|jddiS)Nr)r0 bindparam) postgresql)value literal_bindsT)dialectcompile_kwargs) sqlalchemy.sqlr0r8sqlalchemy.dialectsr9items _bindparams bindparamsstrcompiler<)sqlparamsr0r8r9stmtkvs ./cmf/models/cmf_full_search.pysql_debug_not_workrK?sy.. 9D||~4!D$$$  __Yq2 34 t||J$6$6$8Z^H_|` aac|}|jD]w\}}t|ttfrt j d|d|d|};|t j d|dd|}Yt j d|dd|d|}y|S)Nz(:z([^1-9a-zA-Z_]))z\2zNULL\2"z"\2)r@ isinstanceintfloatresub)rErFresrHrIs rJ sql_debugrUJs C  F1 a#u &&&Bqc!12s"IsCC Y&&Bqc!12vDC&&Bqc!12q3KEC F JrLceZdZdZdZgdZdZgdZedZ edZ edZ ed Z ed Z ed'd Zed(d Zed)d ZedZedZe d*dZedZedZedZedej0j2fdZed+dZeedd,dZedZeedddddZ edZ!e d-d Z"e d.d!Z#e d/ d0d"Z$ed#Z%ed1d$Z&ed2d%Z'eed&Z(y)3 CmfFullSearchuC Сервис полнотекстового поиска. russian)r2 text_renderr0 text_draftr3 parent_idtree_parent_id project_idcmf_created_atcmf_modified_atcode cmf_deleted cmf_archivedloginemailphonephone_internal phone_mobilephone_2phone_assistant ip_addressemail_2birthdayT)fulltext_searchrun_force_reindex index_statsctt|djdjdjS)Nz html.parser ignore)errors)r get_textencodedecode)clstext_with_htmls rJ strip_htmlzCmfFullSearch.strip_htmls2^];DDTJQQYaQbiikkrLcd}d}t||kr|gStdt||Dcgc] }||||z|z}}|Scc}w)Nidr)lenrange)rwr0 text_len_maxoverlaprrTs rJ_split_size_to_partsz"CmfFullSearch._split_size_to_partss\  t9| #6M7|&\/%@!`+=;.])rrRrlowerALL_STOP_WORDSrs rJ_strip_not_wordzCmfFullSearch._strip_not_words;xxBHH-CT$Jnqaggi_mNmnoons A A cddl}d}d|d|}|jjjj |j dd|ij }|S)NrzCmfFullSearchLock:_zKSELECT pg_try_advisory_xact_lock(('x' || md5(:lock_key))::bit(64)::bigint);lock_key) sqlalchemydp_ddSessionexecuter0scalar)rwobj_idsapart_norrTs rJ_fullsearch_obj_lock_get_pgz)CmfFullSearch._fullsearch_obj_lock_get_pgsk'xq :ffjj  "**2774A,B D  68  rLcptj}tjd|d|z}|s tdddl}|j j }|j|}|j}|j} |j|} | stjd|dyd} |j|jjgj|jj|k(j|jj | k(j#} | j%| j'} | rv| d}|j)j|jj|k(j+d|j,j/}| j%|nvtjd ||j1}|j3j+||d|j,j/| | }| j%||r+dd lm}|t8j:j<d |id tj|z dkDr.tjdtj|z yy)Nzcmf_full_search: mark_dirty z DEBUG_MSG: empty obj_idr)cmf_full_search: _text_search_sql_insert  lockedT)is_dirtydirty_atz#cmf_full_search: mark_dirty insert )idrrrrobj_name)schedule_deferred_jobr)kwargs countdowng?zPROF mark_dirty() got )timegdebug ValueErrorrr data_driver dp_model_cls __table__rrselectcrrrrwith_for_updaterfirstupdatevaluesfuncrgen_idinsert cmf.includermodels CmfPersoncelery_full_search_index)rwr fast_index debug_msgprof_strddsa_modeltablerrTrget_stmtget_resid_ update_stmt insert_stmtrs rJmark_dirty_deferzCmfFullSearch.mark_dirty_defers,))+ .vh78!I- |- - VV  ??3'"" JJL--f5 GG?xwO P  IIuwwzzl #uww~~/0uww'12 "  ))H%++- !*C U577::,-V!WW[[]  IIk " GG9&B C**,C V!!WW[[]#&   IIk "  9 !&"2"2"K"KU]_eTfrs t 99; 4 ' GG,TYY[7-B,CD E (rLc|r|j||Stjj|}|r |ddk(rd}||dtj|<t tjdk\r t dyy)N)rrTrri'uDEV: WARNING! Большое количество блокировок может падать на некоторых инсталяциях)rrdeferred_fullsearch_dirty_listgetr| cmf_alert)rwrrforcerprevs rJ mark_dirtyzCmfFullSearch.mark_dirtys '':'F F//33F; D&,JBL[d3e((0 q// 0E 9 f g :rLcl|r it_ytjsytj}t|dkDrtdt|t |j D]9}||}|j ||jd|jd;it_y)NruaDEV: CmfFullSearch помечено грязными слишком много объектов: rrr)rrr|rsortedkeysrr)rw only_clean dirty_listrrs rJapply_deferred_dirtyz"CmfFullSearch.apply_deferred_dirtys /1A , // 55 z?R  yz}I{JzKL MZ__./ qF'F  FJJ|4LX^XbXbcnXo p q,.(rLc|s tdddl}|jj}|j |}|j }|j }|j|jjgj|jj|k(j|jjdk(j}|j|j}|rv|d} |j!j|jj| k(j#dd|j$j'} |j| |jj)y)NrrF)ris_dirty_patchr)rrrrrrrrrrrrrrrrrrrrcommit rwrrrrrrrrrrs rJ mark_cleanzCmfFullSearch.mark_clean s |- - VV  ??3'"" JJL IIuwwzzl #uww~~/0uww!+, "  ))H%++- !*C U577::,-V"#(WW[[]  IIk "  rLc8|s tdddl}|jj}|j |}|j }|j }|j|jjgj|jj|k(j|jjdk(j}|j|j}|r|d} |j!j|jj| k(j#|j$j't)j*dz} |j| |jj-y)Nrr)hours)delay_error_reindex)rrrrrrrrrrrrrrrrrrrrdatetime timedeltarrs rJmark_delay_error_reindexz&CmfFullSearch.mark_delay_error_reindex)s+|- - VV  ??3'"" JJL IIuwwzzl #uww~~/0uww!+, "  ))H%++- !*C U577::,-V(* 8J8JQR8S(S  IIk "  rLNc& |dk(r,|j||jjy|j|dd}|}&d}'|r|'|z }'| r|'d| zz }'| r|'d| zz }'|!r|'d|!zz }'|&d|!zz }&|j |'}(|j|(}(|dk(r'|rA|j|})|j |)}*|ddd|*d})|j |)}+n|dd})|)g}*|j |)}+|r%|}|j |},|j|,},nd}d},| r(|j| } | dd} |j | }-nd} d}-| r(|j| }.|.dd}.|j |.}/nd}.d}/|r1t |}0|j|0dd}0|j |0}1nd}0d}1|!r|!}!|!}2n%d}!d}2n |d k(rdg}*d}+d}d},d} d}-d}.d}/d}0d}1d}!d}2d})d}3|r|}3|!r |!dddz|3z}3d}4|r5|4djd t|jDdd z }4|r |4d |ddzz }4|4d| zz }4|j|3}3|j|4}4|j |3}5|j |4}6|5jd}7|6jd}8|7|8z}9|9d krn|9d z }:|7d kDrr|8dk\rEdj|5jdd}5dj|6jdd}6ndj|5jdd|8z }5|6}6nq|7dkr(|5}5dj|6jdd|7z }6nDdj|5jdd}5dj|6jdd}6|dz|3zdz|4z};|;dd};|;j};|j |;}<|jd|ddd|>})|j |)}+|jdz-CmfFullSearch.index_object..s*Z1*Zs iu Тэги i>ii*i:rr obj_model obj_parent_idobj_tree_parent_idobj_project_idobj_company_idobj_created_atobj_modified_atobj_code obj_deleted obj_archivedobj_owner_nameobj_author_nameobj_modified_by_nameobj_responsible_namesobj_hrefobj_logic_type_codeobj_activity_codeobj_status_typeobj_text text_for_vecr name_for_vecobj_tags tags_for_vecobj_result_textresult_text_for_vec obj_ml_textml_text_for_vec obj_commentscomments_for_vecobj_addon_fieldsobj_addon_fields_for_vecobj_user_ratingobj_key_phraseskey_phrases_for_vecobj_breadcrumbsobj_related_person_loginsobj_user_portal_topobj_client_portal_topr)delete_from_partnor)'_text_search_sql_delete_obj_with_childsrrrrrrCrsetrcountrr_text_search_sql_insertr| enumerate_text_search_sql_delete_partno)?rw model_namerrr2r0 text_prefix text_suffixfull_search_type company_name project_namemodel_verbose_namer comments_textrrrrrrrrrrrrrrrrrobj_addon_fields_dictr r r r rrr smart_namerr obj_text_listrrrobj_comments_textcomments_text_for_vecrrr obj_ml_text_mainobj_ml_text_suffixml_text_main_for_vecml_text_suffix_for_vecmain_token_lensuffix_token_lentotal_token_len oversize_lenrrr obj_text_parts? rJ index_objectzCmfFullSearch.index_objectFsT { *  7 7 ? FFMMO mmK0$7   $ J  # , ,J  # , ,J  #/ /J  O3 3H**:6 }}\2 ~ -==. # 8 8 B )%401=3C2DE"228< )%401!) "228< #"228< "}}\: # "%--"@"1'6":&)&9&9/&J#"&&*#$'MM-$@!$5gv$>!(+(;(;+>?O+P(#' +/("1&5##'&*#  )!FMLHL"O"&  $ $( !# '+ $"O"& H #  .u5;>NN  #((*Zc:O:V:V:X6Y*Z"Z[`\`"a a   ,%4"@ @ cM11==)9: ]]+=>"223CD!$!4!45G!H-33C8177<(+;; V # +V3L&#u,+.884H4N4N4PQXRX4Y+Z(-0XX6L6R6R6TU[V[6\-]*+.884H4N4N4PQqR`aqRq4r+s(-C*&(';$),2H2N2N2PQoR`aoRo2p)q&(+xx0D0J0J0LWf0U'V$),2H2N2N2PQWRW2X)Y&"C'*::S@CUU !'6* "'') --k: $## A f  ' S . Ia , ,! BU! ",# $'@% &!4' (#8) , }  !*3M!"4Eq*I &)%401=/B"228< +++GFV`"/DVguGU#1CR&4?NZky%4 K_  +@  KS  )<  Pa  sB &5A!%7;&59"15%)?C"&9=&6Qi%4 %)! ?C!"%4#$/H%&)<'(+@)  4 **6c-FX*Y  rLcp|s tdddl}|jj}|j |}|j }|j }|j|j|jj|k(|jj|k(z}|j|yNrr) rrrrrrrdeleterrrrr)rwrrrrrrdel_stmts rJrz5CmfFullSearch._text_search_sql_delete_obj_with_childsCs|- - VV  ??3'"" JJL99U#)) WW " "f , WW^^v % '  (rLc|s tdddl}|jj}|j |}|j }|j }|j|j|jj|k(j|jj|k\}|j|yr/) rrrrrrrr0rrrrr) rwrrrrrrrr1s rJrz,CmfFullSearch._text_search_sql_delete_partnoWs|- -VV  ??3'"" JJL IIe  U577>>V+ , U577??&88 9  (rLc*  tjd||s tdddl}*|jj }+|+j |},|,j}-|+j}.|j|}/|/stjd|dy|*j|-jjgj|-jj|k(j|-jj|k(j!}0d}1|r|j#dd}1t%|.j'|0}2|2rt)|2dkDrt+d|d|d d |2dd}3|-j-j|-jj|3k(j.d>V+ , U577??g- . _     ,2237: qyy*+ 7|a26()G9MNOW[\!*Q-C uwwzzS()/"/# / $) / ( /&/&/!,/&/%4/"./&6/#0/&6/ (:!/"$2#/$$2%/&$2'/(%4)/*#%&&++"9"9#//<"X+/,#%&&++"9"9#//<"X-/.&(VV[[%<%$2?/@%4A/B*>C/D+@E/F&G/H)".?0@$2A0B%4C0D*>E0F+@G0H&I0J)|j|| gtAtjjC|tEYd} ~ d} ~ wwxYw)/NzStart cron_index_dirtyuJНе индексируем поскольку запущен импортrTr)r=rOR)rrvT)rrvTr<)minutes)rrvNr)rrvNz -dirty_at)r filterrMrLzcron_index_dirty process rrrv)r r|rOr)rrrrr)rr ucron_index_dirty: Не индексируем поскольку объект не найден или удален в бд fullsearch id:z obj name:z obj id: z dirty_at:z is_dirty:)levelzobj_id=u#ERROR! При индексации u" произошла ошибка: -uacron_index_dirty превышен лимит времени 20 секунд обработано u объектовu&cron_index_dirty обработано u объектов, sleep 1u2При индексации были ошибки:z: zEnd cron_index_dirty at )%rrrTenable_import_modeimport_is_runningrsleeprrWslistrrrget_model_by_idrappendr@r|rirrVrrrrloggingERRORrrW Exception traceback format_exc cmf_rollbackr cmf_commit astimezonehour)stn exceptions obj_id_listobj_ids_by_modelrr]id_listrrD search_rawerr_strers rJcron_index_dirtyzCmfFullSearch.cron_index_dirtysu ()  % % 7 7 9 GG` a  1 YY[   ..44XJ*=+/1HJg*h+/-7h>O>O>S>S>UX`XjXjstXu>u,v,C+, *.-BCIZIZI^I^I`,a,N)*.("1<}-.G'55K(! % >// > 00.0$U+ '..v}}=  > #3"8"8": %w3E7!CL>JKg"%C))5+K+KUY[^`cTdvz){CFA%+%9%9%=%=SRF%=&G %11;z*J]J]I^^ghrhyhygz{EFPFYFYEZZ,,6,?,?+@#Bw}}=,,TTUXY GGgcffX./%--/% %6yy{R"$((,,.99;@@19yy{R'",GGAB@CCTUV L GG*:;<;%%"EcU!C5Prstru vFLLM 4 4 67"))3(3$,,EEcJ" %s P R/ BR**R/chtjjjj|i|Sr)rPrcmf_search_engineCmfSearchEngine fts_search)rwargsrs rJrmzCmfFullSearch.fulltext_searchs)zz++;;FFWPVWWrLc gSr)rIrL addon_filterrarchiveddeletedtree_parent_filterformatr_build_tags_filtertag_name#_build_related_person_logins_filterALLOWED_FIELDSrrPrrWrrrrrRrStuplequery_qstop_words query_likequery_obj_coderA)*rw model_name_in tsquery_strforce_field_nameage_dayslabel force_sliceinclude_attachmentsearchmarkrIrLr[modified_by_nameresponsible_namelogic_type_code status_typemodified_at_beforemodified_at_afterrr user_rating owner_name author_namerinclude_attachment_filter tags_filter tags_params rpl_filter rpl_params search_fieldheadline_fieldtsvector_field slice_from slice_forage_days_subquery max_partnotsquery_str_h1tsquery_str_h2ts_rank_normalizerE found_objss* rJ search_oncezCmfFullSearch.search_onces rLc  dj|j} |xs | j} | tvrt d| dd| } d| } | dk(s| dk(rd} | d }d }|||\}}}d }d }d }d }d }d }|r d |d }d|d}|r d|d}d|d}|r d|d}d|d}d}| j r5t | j jddk\r| dk(rd}nd| d}|r|}n | j}| jjdd}| jjd}| jjdd}| jjd d}| jjd!d}| jjd"} | jjd#}!| j}"| j}#| jjd$}$| jjd%d}%| jjd&|%}&|%xs|&}%| j}'|'r d'jd(j|'nd}'| jjd)xsg}(|j|(\})}*|r|j|\}+},nM| jjd*r.|j| jjd*\}+},nd}+i},|d+}-|d,|d+z }.d}/|rt!|}d-|d.}/d+}0| d/k(rd0}0t#t%j&d1d|j}1d2j|1t#t(z }2d2j|1t#t(z }3|2}4|3}5d }6d}7| j*r)d2j| j*j}7d3jdFi|'| ||/| |)|6|+|||||||d4}8id5| j,d6|d7| j.d8|2d9|3d:|4d;|5d<|-d=|.d>t1|d?|d@|7xsddA| j xsddB| j2xsddC| j4dD|0d||%|&||||| |!|"|#|$|||dE|*|,}9t6j8j:j<j?jA|8|9jC}:tE|:S)GNz & 6Недопустимое значение field_name: Tr5obj_r2r1r _tsvectorcg}g}g}d}|dgk(rd}tjd|}tjd|}ttjdd|j D]}|tjvs|tj|vr|j |.tsquery2idfquerys2JIIH +((7AG((7AGRVVJsG<BBDE - 2 22hcFXFXY^F_6_$$U+''.x87B$$U+''.x87B$$U+%%e, -::j)JJy)JJy)* *rL0z&ts_rank(setweight(name_tsvector,'C')||z, :query_high)zts_rank(setweight(z,'C'), :query_high)z&ts_rank(setweight(name_tsvector,'B')||z , :query_mid)z,'B'), :query_mid)z&ts_rank(setweight(name_tsvector,'A')||z , :query_low)z,'A'), :query_low)rrrz\ or (obj_ml_text LIKE '%' || :query_like || '%' or obj_text LIKE '%' || :query_like || '%') z or z LIKE '%' || :query_like || '%' r[rrrrrrrrr AND obj_tree_parent_id IN ('{}')','rrelated_person_loginrr$obj_modified_at > now() - interval ' days' ANDr0@BrruD SET gin_fuzzy_search_limit=100000; SET default_text_search_config = 'russian'; SELECT subs.*, subs.obj_name as title, ts_headline( 'russian', substring(COALESCE(subs.headline_raw, subs.obj_name) FROM 0 FOR 100000), plainto_tsquery(:param_search_query), 'MaxFragments=1,MaxWords=25,MinWords=24' ) AS headline FROM ( ( SELECT obj_id, obj_code, {headline_field} as headline_raw, obj_model, --obj_name || ' - ' || rank as title, obj_breadcrumbs as breadcrumbs, obj_project_id as obj_project_id, obj_parent_id as obj_parent_id, obj_related_person_logins as obj_related_person_logins, :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 3*365 then 'R' else '' end) as label, --- Множители сбросили на 1 тк выше уже есть умножение через A B C D но можно убрать A B C D Сделать только С а множители расставить уже здесь {txt_tsrank_high_string} + {txt_tsrank_mid_string} * 1 + {txt_tsrank_low_string} * 1 as rank, EXTRACT(days from (now()-obj_modified_at)) as age_days, {tsvector_field} as result_tsvector, {search_field} as result_text, name_tsvector, obj_name, obj_modified_at, obj_user_portal_top, obj_client_portal_top, 'flow_main' as mark FROM cmf_full_search WHERE -- obj_code='DOC-006092' and {age_days_subquery} ({search_field} != '' and {search_field} is not null) and ( ( {tsvector_field} @@ :txt_tsquery_str_where_OR --ниже спорная строка часть данных может не найтись сложная проблема релевантности текста и имени and ts_rank({tsvector_field}, :txt_tsquery_str_rank_OR) > 0.0001 ) OR (:query_like is not null and {search_field} LIKE '%' || :query_like || '%') ) -- Это делаем, чтобы исключить отсюда результаты из top1 -- но это может привести к тому, что по top1 ранк низкий а здесь высокий, но мы его исключили -- and not(name_tsvector @@ :tsquery_str_where_OR) and ((obj_model IN :model_name_in)) and (:query_qstop_words_str is null or not(text_tsvector @@ :query_qstop_words_str)) and part_no <= :max_partno and ( :parent_id is null or obj_project_id = :parent_id ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' ) {tags_filter} {rpl_filter} {tree_parent_filter} and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code ) and ( :status_type is null or obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) and ( :archived is null or obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and ( :user_rating is null or obj_user_rating >= :user_rating) and 1=1 ORDER BY rank desc LIMIT :slice_for OFFSET :slice_from ) UNION ALL ( SELECT obj_id, obj_code, COALESCE({headline_field}, obj_name) as headline_raw, obj_model, --obj_name || ' - ' || rank as title, obj_breadcrumbs as breadcrumbs, obj_project_id as obj_project_id, obj_parent_id as obj_parent_id, obj_related_person_logins as obj_related_person_logins, :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 3*365 then 'R' else '' end) as label, --- Множители сбросили на 1 тк выше уже есть умножение через A B C D но можно убрать A B C D Сделать только С а множители расставить уже здесь {txt_tsrank_high_string} + {txt_tsrank_mid_string} * 1 + {txt_tsrank_low_string} * 1 as rank, EXTRACT(days from (now()-obj_modified_at)) as age_days, ml_text_tsvector as result_tsvector, obj_text as result_text, name_tsvector, obj_name, obj_modified_at, obj_user_portal_top, obj_client_portal_top, 'flow_attach' as mark FROM cmf_full_search WHERE {age_days_subquery} (obj_ml_text != '' and obj_ml_text is not null) and (obj_parent_model != 'CmfPerson') and ( ( ml_text_tsvector @@ :txt_tsquery_str_where_OR --ниже спорная строка часть данных может не найтись сложная проблема релевантности текста и имени and ts_rank(ml_text_tsvector, :txt_tsquery_str_rank_OR) > 0.0001 ) OR (:query_like is not null and obj_text LIKE '%' || :query_like || '%') OR (:query_like is not null and obj_name LIKE '%' || :query_like || '%') OR (:query_like is not null and obj_ml_text LIKE '%' || :query_like || '%') ) and ('{tsvector_field}' = 'ml_text_tsvector') -- Это делаем, чтобы исключить отсюда результаты из top1 -- но это может привести к тому, что по top1 ранк низкий а здесь высокий, но мы его исключили -- and not(name_tsvector @@ :tsquery_str_where_OR) and (obj_parent_model IN :model_name_in and obj_model = 'CmfAttachment') and (:query_qstop_words_str is null or not(ml_text_tsvector @@ :query_qstop_words_str)) and part_no <= :max_partno and ( :parent_id is null or obj_project_id = :parent_id ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' ) {tags_filter} {rpl_filter} {tree_parent_filter} and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code ) and ( :status_type is null or obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) and ( :archived is null or obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and ( :user_rating is null or obj_user_rating >= :user_rating) and 1=1 ORDER BY rank desc LIMIT 20 OFFSET :slice_from/:slice_for*4 ) UNION ALL ( SELECT obj_id, obj_code, ts_rank(setweight(name_tsvector,'C') || {tsvector_field}, :tsquery_str_rank_OR)|| obj_name ||' '|| {headline_field} as headline_raw, obj_model, --obj_name as title, obj_breadcrumbs as breadcrumbs, obj_project_id as obj_project_id, obj_parent_id as obj_parent_id, obj_related_person_logins as obj_related_person_logins, :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 3*365 then 'R' else '' end) as label, -- (ts_rank(setweight(name_tsvector,'C') || ml_text_tsvector, :tsquery_str_rank_OR, {ts_rank_normalize})) -- * (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 3*365 then -- ((3*365-EXTRACT(days from (now()-obj_modified_at)))/30/100+1) -- else 1 end) as rank, --- Множители сбросили на 1 тк выше уже есть умножение через A B C D но можно убрать A B C D Сделать только С а множители расставить уже здесь {tsrank_high_string} + {tsrank_mid_string} * 1 + {tsrank_low_string} * 1 as rank, EXTRACT(days from (now()-obj_modified_at)) as age_days, COALESCE({tsvector_field}, name_tsvector) as result_tsvector, {search_field} as result_text, name_tsvector, obj_name, obj_modified_at, obj_user_portal_top, obj_client_portal_top, 'flow_name' as mark FROM cmf_full_search WHERE {age_days_subquery} ({search_field} != '' and {search_field} is not null) and ( ( name_tsvector @@ :tsquery_str_where_OR and ts_rank(setweight(name_tsvector,'C') || {tsvector_field}, :tsquery_str_rank_OR) > 0.0001 ) OR (:query_like is not null and {search_field} LIKE '%' || :query_like || '%') -- <% медленная и на ml_text запускать не вариант 10сек -- experimental OR (:search_query_wo_qstop <<% obj_name) ) and (obj_model IN :model_name_in) and (:query_qstop_words_str is null or not(text_tsvector @@ :query_qstop_words_str)) and part_no <= :max_partno and ( :parent_id is null or obj_project_id = :parent_id ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' ) {tags_filter} {rpl_filter} {tree_parent_filter} and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code ) and ( :status_type is null or obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) and ( :archived is null or obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and ( :user_rating is null or obj_user_rating >= :user_rating) and 1=1 ORDER BY rank desc LIMIT :slice_for OFFSET :slice_from ) UNION ALL /* Дополнительно проверяем Полное совпадение CODE или ID или ilike выдаем с высоким рангом 100 в теории можно объединить с выше через CASE LIKE или с once_top */ ( SELECT obj_id, obj_code, obj_name ||' '|| obj_code || ' ' || obj_id || ' ' || {headline_field} as headline_raw, obj_model, --obj_name, obj_breadcrumbs as breadcrumbs, obj_project_id as obj_project_id, obj_parent_id as obj_parent_id, obj_related_person_logins as obj_related_person_logins, :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 365 then 'R' else '' end) as label, 1000 as rank, -- 100 -- * (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 365 then -- (365-EXTRACT(days from (now()-obj_modified_at)))/30+1 else 1 end) as rank, EXTRACT(days from (now()-obj_modified_at)) as age_days, {tsvector_field} as result_tsvector, {search_field} as result_text, name_tsvector, obj_name, obj_modified_at, obj_user_portal_top, obj_client_portal_top, 'flow_idcodelike' as mark FROM cmf_full_search WHERE ({search_field} != '' and {search_field} is not null) and /* Дополнительно проверяем полное совпадение CODE ID ML_TEXT >2WORDS по ilike с высоким рангом */ ( (:query_obj_code is not null and ( -- не дает бонуса возможно даже хуже obj_code = :query_obj_code or obj_code LIKE '%' || :query_obj_code ) ) or (:query_obj_id is not null and obj_id = :query_obj_id) {idcodelike_like_string} ) and (:query_qstop_words_str is null or not({tsvector_field} @@ :query_qstop_words_str)) and obj_model IN :model_name_in and part_no <= :max_partno and ( :parent_id is null or obj_project_id = :parent_id ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' ) {tags_filter} {rpl_filter} {tree_parent_filter} and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code ) and ( :status_type is null or obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) and ( :archived is null or obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and 1=1 /*ORDER BY obj_id desc -- не будем делать, т.к. тормозить будет*/ -- хак 50 здесь предполагает что запросы делаются по 50 и будем давать больше лайкответов на след слайсах LIMIT 2+:slice_from/25 ) ) as subs )rrrrrrrrtsrank_high_stringtsrank_mid_stringtsrank_low_stringtxt_tsrank_high_stringtxt_tsrank_mid_stringtxt_tsrank_low_stringidcodelike_like_stringparam_search_query param_querysearch_query_wo_qstoptsquery_str_rank_ORtsquery_str_where_ORtxt_tsquery_str_rank_ORtxt_tsquery_str_where_ORrrrrquery_qstop_words_strr query_obj_idrr)rrrrrrrrrrrrrrr)#rrrIrrrr|rLrrrrrrrrrPrrRrSEVA_OR_QUERY_STOP_WORDS_NORMr search_queryrrrrrrWrrrrfetchallrA);rwrqueryrrrrforce_related_person_loginrr param_tsqueryrIrrrrrrrrrrrrrrrLr[rrrrrrrrrrrr tags_namesrrrrrrrrquery_token_listrrrrrrrErFrs; rJsearch_once_top_bm25z"CmfFullSearch.search_once_top_bm25s- 5;;=1 %:):): ^+ Nzl[cg hj\*  |,  :#:'N&JZZg j (:>:JJ\$_ ! $J>JZZg j (:>:JJ\$_ !"$   V%6%6%<% !..223EF!..223EtL --112CTJ))--mTB #00445IJ"//334GH??..))--m< ((,,\4@ ))--mZH .; #66j|?FFuzzRdGefCE((,,Z8>B # #9*#E [ %%(%L%LMg%h "J  $ $%; <%(%L%LVM`M`MdMde{M|%} "J JJ1X !HuQx'  (m8!EhZzZ    Jrvvje<BBDE %zz*:SA]=^*^_$zz*:SA]=^*^_"5#7   $  # #$)JJv/G/G/M/M/O$P !HH P FQX R#5(,!2,&!2$"4!2!2&<%:%:&< QX t! &(;(;! 5! #f&B&B!  "#6!  #$8 ! &'> ! '(@ !  *!  !  U=1!  U!  $%:%Bd!  &++3t!  V008D!  f33!  *! !! "%& 0 0.&"4!2 &$""=! >?! @A! F)),,88@@BJJ3PVW``b JrLcv|xs |j}|jjd} |jjd} |jjd} |jjd} |jjd} |jjd}|jjd}|jjd}|jjd d}|jjd |}|xs|}|j}|r d j d j |nd }|jjdxsg}|j |\}}|r|j|\}}nM|jjdr.|j|jjd\}}nd }i}d}|rtd|x}} d}|tvrtd|dd|}|jsd}d}n.|jd}|jd|jdz }d }|rt|}d|d}|dk(rdnd}d|d|d |d!|d"|d#|d#|d#|d$}tjjjj!j#|id%|d&|d't%|d(|d)|d| d |d |d| d| d| d| d|d|d*|j&d+|j(d|||} t+| }!|!S),Nr[rrrrrrrrrrrrrrz AND ( :responsible_name IS NULL OR obj_responsible_names ILIKE '%' || :responsible_name || '%' ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) z:Param related_user is deprecated. Use related_person_loginz AND ( :responsible_name IS NULL OR obj_responsible_names ILIKE '%' || :responsible_name || '%' OR obj_owner_name = :owner_name ) rTr5rrr{rrrr0ra SELECT obj_id, obj_code, obj_model, obj_name as title, obj_breadcrumbs as breadcrumbs, :label || (CASE WHEN EXTRACT(DAYS FROM (NOW() - obj_modified_at)) < 365 then 'R' else '' end) as label, 100 / (EXTRACT(DAYS FROM (NOW() - obj_modified_at)) + 1) as rank, EXTRACT(DAYS FROM (NOW() - obj_modified_at)) as age_days,  as , substring(` FROM 0 FOR 240) as headline FROM cmf_full_search WHERE a] obj_model IN :model_name_in AND part_no <= :max_partno AND ( :parent_id IS NULL OR obj_parent_id = :parent_id ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) z a AND ( :logic_type_code IS NULL OR obj_logic_type_code = :logic_type_code ) AND ( :status_type IS NULL OR obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) AND ( :archived IS NULL OR obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and ( :user_rating is null or obj_user_rating >= :user_rating) AND obj_modified_at is not null ORDER BY obj_modified_at DESC LIMIT :slice_for OFFSET :slice_from; rrrrrrr)rIrrrrrrrrrfullsearch_slicerPrrWrrrrrrrrA)"rwr related_userrrrrrrIr[rrrrrrrrrrrrrrrowner_and_responsible_filterrrrrrrErall_objss" rJ filter_oncezCmfFullSearch.filter_onces4&:):): ''++K8 !..223EF!..223EF --112CD))--m< #00445IJ"//334GH))--m< ((,,\4@ ))--mZH .; #66j|?FFuzzRdGefCE((,,Z8>B # #9*#E [ %%(%L%LMg%h "J  $ $%; <%(%L%LVM`M`MdMde{M|%} "J JJ( $  R S,8 8J), (^+ Nzl[cg hj\* &&JI003J//2V5L5LQ5OOI 8}H"FxjPZ [  *f 4W!  d<.1'.)##$ ../  #$ % /" H)),,88@@BJJ3Q *Q Q  U=1Q  U Q * Q  Q  *Q  ;Q   0Q   0Q  Q  ;Q  !"4Q  !2Q  Q v~~!Q " ;#Q $%Q &'Q  * #rLc,ttjj}|j |\}}d}d}|rt |}d|d}d}d}d} d} dj gd |d |d |d |d |d|d |d |d |d|d|d |d |d |d|d} tjjjjj| | | |d|j} t| S)NrrrEMTrrr{a (SELECT obj_id, obj_code, obj_model, obj_name as title, obj_breadcrumbs as breadcrumbs, :label || (CASE WHEN EXTRACT(DAYS FROM (NOW() - obj_modified_at)) < 365 then 'R' else '' end) as label, 100 / (EXTRACT(DAYS FROM (NOW() - obj_modified_at)) + 1) as rank, EXTRACT(DAYS FROM (NOW() - obj_modified_at)) as age_days, rr r zW obj_model = 'CmfTask' AND part_no = 0 u AND obj_modified_at is not null /* Временный хак пока не поправят багу TEM-1625047513 */ ORDER BY obj_modified_at DESC LIMIT :slice_for OFFSET :slice_from) UNION (SELECT obj_id, obj_code, obj_model, obj_name as title, obj_breadcrumbs as breadcrumbs, :label || (CASE WHEN EXTRACT(DAYS FROM (NOW() - obj_modified_at)) < 365 then 'R' else '' end) as label, 100 / (EXTRACT(DAYS FROM (NOW() - obj_modified_at)) + 1) as rank, EXTRACT(DAYS FROM (NOW() - obj_modified_at)) as age_days, z[ obj_model = 'CmfDocument' AND part_no = 0 z obj_model != 'CmfDocument' AND obj_model != 'CmfTask' AND part_no = 0 u AND obj_modified_at is not null /* Временный хак пока не поправят багу TEM-1625047513 */ ORDER BY obj_modified_at DESC LIMIT :slice_for OFFSET :slice_from); )rrr)rCr current_userrcrrPrrrWrrrrrrA) rwrrrrrrrrrrrErs rJfilter_empty_topzCmfFullSearch.filter_empty_topuso !5!56!$!H!HI]!^ J 8}H"FxjPZ [ $   @@ @@#@$0.@1@(.@)@##@$@" #@"#@>?@>#?@>$0.?@>1?@@(.A@@)A@F##G@F$G@L M@LM@hi@h#i@h$0.i@h1i@j(.k@j)k@p##q@p$q@x y@x y@D)),,88@@BJJ3$"Q  Q  8: JrLcXi}d}|r!d}ddjd|Dd|d<||fS)Nrz AND obj_tags ~* :tags_re(^| |,)(|c3FK|]}tj|ywrrRescape)rrs rJrz3CmfFullSearch._build_tags_filter..s8hQY89L8hs!)( |,|$)tags_re)r)rrrs rJrz CmfFullSearch._build_tags_filtersF  5K'/8h]g8h0h/iiq%rK "K''rLcVi}d}|r d}tj|}d|d|d<||fS)Nrz; AND obj_related_person_logins ~* :related_person_logins_rerrrelated_person_logins_rer) user_loginrrs rJrz1CmfFullSearch._build_related_person_logins_filtersC  VJ:.J7? |85TJ1 2:%%rLct|dkryg}|ddD]i}|djds|djds,i}|d|d<|jddr |dd|d<nd|d<|j|ktj }||_d |_||_tjj|_ d |_ tj5|j!dddy#1swYyxYw) Nrr{rz CmfDocument:zCmfTask:rparentr]rF)r| startswithrrr CmfSearchStatr`actionrrrr person_id aggregatedrT disable_aclsave)robj_dictst_dictrecst_recstats rJ_do_calc_statisticsz!CmfFullSearch._do_calc_statisticss | q CR= #CI((8CIrRsK ",,00 >!#   ! @A W!\\!cc ,!GGK:"#58J#JMi#il{#{U$U V M"x'*<<NO_b  mCJJ..<<mrL