AI: address review on PR #461

backend/app/services/post_tag_search.rb

@@ -3,8 +3,8 @@ class PostTagSearch
   def self.literals(locale, raw)
     tokens = raw.split.reject { _1.downcase == 'type:or' }
     names = tokens.map { _1.sub(/\Anot:/i, '') }
-    registered = TagName.where(language_code: locale.language_code, name: names)
+    registered = matching_names(TagName.where(language_code: locale.language_code),
-        .pluck(:name, :tag_id).to_h
+        names, 'tag_names.name', :tag_id).to_h
     primary = TagName.where(language_code: locale.language_code,
         tag_id: registered.values, primary_flg: true).pluck(:tag_id, :name).to_h
     tokens.zip(names).map do |token, name|
@@ -19,12 +19,25 @@ class PostTagSearch
 
   def self.references_for(locale, literals)
     names = literals.map { _1[:name] }.uniq
-    internal = TagName.where(language_code: locale.language_code, name: names)
+    internal = matching_names(TagName.where(language_code: locale.language_code),
-        .pluck(:name, :tag_id).group_by(&:first)
+        names, 'tag_names.name', :tag_id).group_by(&:first)
-    external = names.empty? ? {} : ExternalTag.where(
+    external = matching_names(ExternalTag.all, names,
-        "CONCAT(platform, ':', name) IN (?)", names)
+        "CONCAT(external_tags.platform, ':', external_tags.name)", :id).group_by(&:first)
-        .pluck(Arel.sql("CONCAT(platform, ':', name)"), :id).group_by(&:first)
     names.to_h { |name| [name, [internal.fetch(name, []).map(&:last).uniq,
                               external.fetch(name, []).map(&:last).uniq]] }
   end
+
+  def self.matching_names(scope, names, expression, identifier)
+    return [] if names.empty?
+
+    queries = names.uniq.map do |name|
+      scope.klass.sanitize_sql_array(['SELECT ? AS name', name])
+    end
+    # Return the input spelling so Ruby does not recheck database equivalence.
+    scope.joins(<<~SQL).pluck(Arel.sql('search_names.name'), identifier)
+      INNER JOIN (#{ queries.join(' UNION ALL ') }) AS search_names
+        ON #{ expression } = search_names.name COLLATE utf8mb4_0900_ai_ci
+    SQL
+  end
+  private_class_method :matching_names
 end

backend/spec/requests/posts_spec.rb

@@ -206,6 +206,25 @@ RSpec.describe 'Posts API', type: :request do
     end
 
     context 'when q is provided' do
+      ['SPEC_TAG', 'SpEc_TaG', 'spec_tág', 'MANKO', 'MaNkO', 'mánko'].each do |name|
+        it "matches #{ name } using database collation" do
+          get '/posts', params: { locale: 'ja', tags: name }
+
+          expect(response).to have_http_status(:ok)
+          expect(json.fetch('count')).to eq(2)
+          expect(json.fetch('posts').map { _1.fetch('id') })
+              .to contain_exactly(post_record.id, hit_post.id)
+        end
+
+        it "excludes #{ name } using database collation" do
+          get '/posts', params: { locale: 'ja', tags: "not:#{ name }" }
+
+          expect(response).to have_http_status(:ok)
+          expect(json.fetch('count')).to eq(1)
+          expect(json.fetch('posts').map { _1.fetch('id') }).to eq([miss_post.id])
+        end
+      end
+
       it 'filters posts by q (hit case)' do
         get '/posts', params: { tags: 'spec_tag' }
 
@@ -266,6 +285,26 @@ RSpec.describe 'Posts API', type: :request do
         PostExternalTag.create!(post: miss_post, external_tag:)
       end
 
+      ['NICO:SEARCH_EXTERNAL', 'NiCo:SeArCh_ExTeRnAl', 'nico:séarch_external'].each do |name|
+        it "matches #{ name } using database collation" do
+          get '/posts', params: { locale: 'ja', tags: name }
+
+          expect(response).to have_http_status(:ok)
+          expect(json.fetch('count')).to eq(2)
+          expect(json.fetch('posts').map { _1.fetch('id') })
+              .to contain_exactly(miss_post.id, both_post.id)
+        end
+
+        it "excludes #{ name } using database collation" do
+          get '/posts', params: { locale: 'ja', tags: "not:#{ name }" }
+
+          expect(response).to have_http_status(:ok)
+          expect(json.fetch('count')).to eq(2)
+          expect(json.fetch('posts').map { _1.fetch('id') })
+              .to contain_exactly(post_record.id, hit_post.id)
+        end
+      end
+
       it 'keeps internal name searches independent of colliding external ids' do
         get '/posts', params: { tags: primary_tag_name_for(tag, 'ja').name }
 

frontend/src/components/PostEditForm.tsx

@@ -1,8 +1,6 @@
-import { useQuery } from '@tanstack/react-query'
 import { useEffect, useMemo, useState } from 'react'
 
 import { useLocale } from '@/lib/useLocale'
-import PostList from '@/components/PostList'
 import PostOriginalCreatedTimeField from '@/components/PostOriginalCreatedTimeField'
 import FieldError from '@/components/common/FieldError'
 import PostDurationField from '@/components/posts/PostDurationField'
@@ -12,15 +10,14 @@ import { Button } from '@/components/ui/button'
 import { toast } from '@/components/ui/use-toast'
 import { isApiError } from '@/lib/api'
 import useDialogue from '@/lib/dialogues/useDialogue'
-import { fetchPosts, updatePost } from '@/lib/posts'
+import { updatePost } from '@/lib/posts'
-import { postsKeys } from '@/lib/queryKeys'
 import { msToTime } from '@/lib/utils'
 import { useValidationErrors } from '@/lib/useValidationErrors'
 import { useUITranslation } from '@/lib/useUITranslation'
 
 import type { FC, FormEvent } from 'react'
 
-import type { FetchPostsParams, Post, TagWithSections } from '@/types'
+import type { Post, TagWithSections } from '@/types'
 
 type PostFormField =
     'parentPostIds' | 'tags' | 'videoMs'
@@ -71,33 +68,6 @@ const PostEditForm: FC<Props> = ({ post, onSave }) => {
   const [tags, setTags] = useState<string> (() => tagsToStr (post.tags))
   const [title, setTitle] = useState (post.title)
 
-  const [importVisible, setImportVisible] = useState (false)
-  const [sourceTitle, setSourceTitle] = useState ('')
-  const [sourcePage, setSourcePage] = useState (1)
-  const sourceParams: FetchPostsParams = {
-      title: sourceTitle, page: sourcePage, limit: 20,
-      url: '', tags: '', match: 'all', order: 'original_created_at:desc',
-      originalCreatedFrom: '', originalCreatedTo: '',
-      createdFrom: '', createdTo: '', updatedFrom: '', updatedTo: '' }
-  const { data: sources, isError: sourceError } = useQuery ({
-      queryKey: postsKeys.index (sourceParams, locale),
-      queryFn: () => fetchPosts (sourceParams, locale),
-      enabled: importVisible && !disabled })
-
-  const importTags = (source: Post) => {
-    if (disabled)
-      return
-    setTags (current => {
-      const existing = new Set (current.split (/\s+/))
-      const additions = tagsToStr (source.tags).split (/\s+/)
-	  .filter (tag => tag !== '' && !existing.has (tag))
-      return additions.length > 0
-	  ? `${current}${current.trim () === '' ? '' : ' '}${additions.join (' ')}`
-	  : current
-    })
-    setImportVisible (false)
-  }
-
   const videoFlg =
       useMemo (() => tags.split (/\s+/).some (tag => tag.replace (/\[.*\]$/, '') === '動画'),
 	       [tags])
@@ -222,48 +192,6 @@ const PostEditForm: FC<Props> = ({ post, onSave }) => {
 	    setTags={setTags}
 	    errors={fieldErrors.tags}/>
 
-	<Button
-	    type="button"
-	    variant="outline"
-	    disabled={disabled}
-	    aria-expanded={importVisible}
-	    onClick={() => setImportVisible (visible => !visible)}>
-	  {label ('edit.import')}
-	</Button>
-	{importVisible && (
-	    <div className="space-y-2" onKeyDown={event => {
-	      if (event.key === 'Enter' && event.target instanceof HTMLInputElement)
-		event.preventDefault ()
-	    }}>
-	      <PostTextField
-		  label={label ('shared:labels.title')}
-		  type="search"
-		  value={sourceTitle}
-		  disabled={disabled}
-		  onChange={value => {
-		    setSourceTitle (value)
-		    setSourcePage (1)
-		  }}/>
-	      <FieldError messages={sourceError ? [label ('common:fetchError')] : []}/>
-	      {!disabled && <PostList posts={sources?.posts ?? []} onSelect={importTags}/>}
-	      <div className="flex flex-col gap-2 md:flex-row">
-		<Button
-		    type="button"
-		    variant="outline"
-		    disabled={disabled || sourcePage <= 1}
-		    onClick={() => setSourcePage (page => page - 1)}>
-		  {label ('common:pagination.previous')}
-		</Button>
-		<Button
-		    type="button"
-		    variant="outline"
-		    disabled={disabled || sourcePage * 20 >= (sources?.count ?? 0)}
-		    onClick={() => setSourcePage (page => page + 1)}>
-		  {label ('common:pagination.next')}
-		</Button>
-	      </div>
-	    </div>)}
-
 	{/* オリジナルの作成日時 */}
 	<PostOriginalCreatedTimeField
 	    disabled={disabled}

frontend/src/components/PostList.tsx

@@ -3,7 +3,6 @@ import { useRef } from 'react'
 import { useLocation } from 'react-router-dom'
 
 import PrefetchLink from '@/components/PrefetchLink'
-import { Button } from '@/components/ui/button'
 import { useUITranslation } from '@/lib/useUITranslation'
 import { useClientBehaviourSettings } from '@/lib/useClientBehaviourSettings'
 import { cn } from '@/lib/utils'
@@ -14,11 +13,10 @@ import type { FC, MouseEvent } from 'react'
 import type { Post } from '@/types'
 
 type Props = { posts:     Post[]
-	       onSelect?: (post: Post) => void
 	       onClick?:  (event: MouseEvent<HTMLElement>) => void }
 
 
-const PostList: FC<Props> = ({ posts, onClick, onSelect }) => {
+const PostList: FC<Props> = ({ posts, onClick }) => {
   const { label } = useUITranslation ('shared')
   const location = useLocation ()
   const behaviourSettings = useClientBehaviourSettings ()
@@ -41,11 +39,20 @@ const PostList: FC<Props> = ({ posts, onClick, onSelect }) => {
 	  const sharedId = `page-${ post.id }`
 	  const layoutId = sharedId
 
-	  const card = (
+	  return (
+	      <PrefetchLink
+		  to={`/posts/${ post.id }`}
+		  key={post.id}
+		  className="w-40 h-40"
+		  state={{ sharedId }}
+		  onClick={e => {
+		    setForLocationKey (location.key, sharedId)
+		    onClick?.(e)
+		  }}>
 		<motion.div
 		    ref={cardRef}
 		    layout={animationMode === 'off' ? false : true}
-		  layoutId={(onSelect || animationMode === 'off') ? undefined : layoutId}
+		    layoutId={animationMode === 'off' ? undefined : layoutId}
 		    className={cn ('w-full h-full overflow-hidden rounded-xl shadow',
 				   'transform-gpu will-change-transform',
 				   (post.childPosts ?? []).length > 0 && 'ring-4 ring-green-500',
@@ -80,34 +87,13 @@ const PostList: FC<Props> = ({ posts, onClick, onSelect }) => {
 			  loading={
 			      thumbnailMode === 'light'
 			      ? 'lazy'
-			    : (i < 12 ? 'eager' : 'lazy')
+			      : i < 12
+				? 'eager'
+				: 'lazy'
 			  }
 			  decoding="async"
 			  className="object-cover w-full h-full"/>)}
-	      </motion.div>)
+		</motion.div>
-
-	  return onSelect
-	  ? (
-	      <Button
-		  key={post.id}
-		  type="button"
-		  variant="ghost"
-		  className="w-40 h-40 p-0"
-		  aria-label={post.title || post.url}
-		  onClick={() => onSelect (post)}>
-		{card}
-	      </Button>)
-	  : (
-	      <PrefetchLink
-		  to={`/posts/${ post.id }`}
-		  key={post.id}
-		  className="w-40 h-40"
-		  state={{ sharedId }}
-		  onClick={e => {
-		    setForLocationKey (location.key, sharedId)
-		    onClick?.(e)
-		  }}>
-		{card}
 	      </PrefetchLink>)
 	})}
       </div>)

frontend/src/locales/ain-Kana/posts.json

@@ -1,5 +1,4 @@
 {
-  "_untranslated": ["edit.import"],
   "list": {
     "tagline": "{{brand}}",
     "empty": "ミンタㇻ　タ　ネン　カ　イサㇺ",
@@ -32,7 +31,6 @@
     "showExternal": "ソイタㇰ　ソㇱ　アヌカレ"
   },
   "edit": {
-    "import": "インポート",
     "validationFailed": "アシㇼ　カㇻ　カ　エアイカㇷ゚……",
     "conflictTitle": "モㇱマ　クㇽ　アシㇼ　カㇻ　ワ　アン！",
     "conflictDescription": "モㇱマ　イカㇻ　クㇽ　エエトコ　アシㇼ　カㇻ　ワ　アン。",

frontend/src/locales/ja/posts.json

@@ -22,7 +22,6 @@
     "question": "表示しますか?", "showExternal": "外部ページを表示"
   },
   "edit": {
-    "import": "インポート",
     "validationFailed": "更新はできなかったよ……",
     "conflictTitle": "競合が発生しました．",
     "conflictDescription": "ほかの耕作員が先に更新してゐます．",