קוד SQL טהור לחיפוש חופשי בעמודה

ארכיטקט

שבוע טוב לציבור הקדוש
שנים רבות אני שובר את הראש מהי הדרך לאפשר ליוזר לחפש בחופשיות בתוך עמודה כלשהי (נניח, בלי תיקוני ווי"ן ויו"דין) כידוע יש בעיה של סדר המילים, ואם אני רוצה להיצמד לכלל של גוגל שהדף חייב להכיל את המילים שהוקלדו (שבינינו זה 95% מההצלחה של חיפוש) אז אם אתה שומר שם משפחה לפני פרטי והוא מקליד פרטי ומשפחה וכיוצא בזה, נוצרות לנו בעיות.
הדרך הטובה ביותר ששמעתי עד כה היא לחלק את מפתח החיפוש למילים ולחפש כל מילה בנפרד (את רעיון זה שמעתי מהרב נדרים פלוס שליט"א)
כזה:

DECLARE @UserInput NVARCHAR(100) = 'אלי רחמים';
DECLARE @UserInput_Word_1 NVARCHAR(100) = 'אלי';
DECLARE @UserInput_Word_2 NVARCHAR(100) = 'רחמים';

SELECT 'רחמים אליהו וזאנה' AS Name INTO #table;

SELECT * 
FROM #table
WHERE 
Name LIKE CONCAT('%',@UserInput_Word_1,'%')
AND Name LIKE CONCAT('%',@UserInput_Word_2,'%');
DROP TABLE #table;

דא עקא שיש לג'נרט את הסקריפט בדוט נט מן הסתם ויש בזה לא מעט מן הסיוט.
לאחרונה זכיתי לפתור את זה בדמות קוד SQL טהור ונקי ללא צורך בהתערבות של ג'ינרוטים חיצוניים
כזה:

DECLARE @UserInput NVARCHAR(100) = 'אלי רחמים';

SELECT 'רחמים אליהו וזאנה' AS Name INTO #table;

SELECT * 
FROM #table
WHERE 1 = ALL ( SELECT CASE WHEN Name LIKE CONCAT('%',value,'%') THEN 1 ELSE 0 END FROM STRING_SPLIT(@UserInput,' ') )
DROP TABLE #table;

אבי

במקרה הזה הבעיה האמיתית היא שהשם פרטי + שם משפחה שמורים באותו שדה, אם כל נתון היה בשדה משלו השאלה לא היתה מתחילה.
אבל אולי זה מתאים למקרים אחרים..

מנצפך

@אבי
גם אם השדות שמורים בנפרד, אבל רוצים לתת למשתמש שדה אחת לחיפוש, שבה יוכל להכניס שם משפחה ופרטי, לא משנה הסדר, או כל אחד בנפרד.

אבל למקרה כזה (שהעמודות נפרדות), צריך לחבר את השם משפחה ופרטי ביחד (עם רווח ביניהם) ולחפש על שניהם ביחד.

אבי

@מנצפך לא משנה מה מוצג למשתמש
אם בדטהבייס העמודות נפרדות אפשר לחפש בקלות לפי שניהם

אבי

יש הכרח נוסף לשמור כל נתון בשדה נפרד בפרט בשמות פרטיים / משפחה
למקרים של יותר משם פרטי אחד או שם משפחה שמורכב משני מילים, שם לא יועיל הפתרון שכתבת, אולי הפתרון הראשון שהוא יותר גנרי, אבל עדיף מראש לשמור הכל מסודר ולמנוע את הבעיה מהשורש.

ארכיטקט

@אבי אמר בקוד SQL טהור לחיפוש חופשי בעמודה:

השאלה לא היתה מתחילה

יש לך דוגמת קוד להראות איך אתה מחפש ב2 שדות בצורה פשוטה וקלה?

חגי

@ארכיטקט

with cte
(
select column1 + ' ' + column2 + ' ' + column3 ... as myColumn from myTable
)
SELECT * 
FROM cte
WHERE 1 = ALL ( SELECT CASE WHEN myColumn LIKE CONCAT('%',value,'%') THEN 1 ELSE 0 END FROM STRING_SPLIT(@UserInput,' ') )

לא בדקתי, אבל משהו כזה.

ארכיטקט

@חגי קל מאוד לייצר קוד שמדביקים אותו ב SSMS ועובד
אבי דיבר על זה שהשאלה לא מתחילה אני מדבר על יוזר פשוט שמחפש את ישראל כהן בדטה בייס לא הבנתי מה עושה כאן שאילתה רקורסיבית????

חגי

@ארכיטקט
זה לא רקורסיבי, אני מאחד את כל העמודות ומבצע עליהן חיפוש כאילו הן עמודה אחת.
אתה יכול גם להשתמש בHAVING במקום WITH cte ... SELECT ... WHERE אם זה יותר נוח לך.

בצורה הזו אתה מחפש על כמה עמודות שרק תרצה בכל הDB
אולי ל @אבי יש פיתרון יותר יעיל שאני לא מכיר.

אבי

אני לא ליד מחשב כרגע
חשבתי על כיוון של JOIN לשני העמודות (פרטי, משפחה) מול ה STRING_SPLIT
זכור לי משהו שראיתי בעבר, אבדוק בלנ"ד מאוחר יותר.
אולי הגזמתי עם "השאלה לא מתחילה", כי בהחלט דרושה כאן יצירתיות לפתרון יעיל ונקי.
פשוט הפריע לי הקטע שדוחסים ערכים לתוך שדה אחד ואז שוברים את הראש איך לפרק אותם, כשכל הרעיון של מסד נתונים (רלציוני, לפחות) הוא לשמור את הנתונים ברמת פירוק גבוהה כדי לאפשר חיתוכים מהירים בהמשך.

ארכיטקט

@חגי אמר בקוד SQL טהור לחיפוש חופשי בעמודה:

אני מאחד את כל העמודות ומבצע עליהן חיפוש כאילו הן עמודה אחת

עד כמה שאני יודע עושים תת שאילתה ככה:

SELECT * FROM 
(SELECT 
CONCAT(COL1,' ',COL2) AS ConcatenateColumn  
FROM MyTable) AS T 
WHERE ConcatenateColumn like '%bla%'

אפשר כמובן לקנקט רק בWHERE ואז זה לא מצריך אפילו תת שאילתה.
לא מבין מה הביא אותך לקוד הנ"ל שעושים בשאילתות רקורסיביות בלבד. לגבי HAVING הוא רלוונטי רק אחרי GROUP BY והוא למעשה סוג של WHERE על אגרגציה
כמו"כ השירשור עם פלוס יש בו בעיה בערכי NULL והפונקציה CONCAT פותרת את זה.
מלבד זאת אתה חוזר לפתרון שהצעתי למעלה רק בעמודות מקונקטות.
אבל אולי אני בכל זאת מפספס משהו, הכי פשוט לשים קוד עם טבלה זמנית שכולנו נוכל לראות את הפעולה ולהבין מה החלופות הטובות יותר או פחות.

ארכיטקט

@אבי אמר בקוד SQL טהור לחיפוש חופשי בעמודה:

חשבתי על כיוון של JOIN לשני העמודות (פרטי, משפחה)

JOIN זה בין 2 טבלאות לא בין 2 שדות, זה נקרא "שירשור" או CONCAT ב SQL.

@אבי אמר בקוד SQL טהור לחיפוש חופשי בעמודה:

כשכל הרעיון של מסד נתונים (רלציוני, לפחות) הוא לשמור את הנתונים ברמת פירוק גבוהה כדי לאפשר חיתוכים מהירים בהמשך.

הנכון לדעתי: שכל הרעיון של מסד נתונים הוא לשמור כל נתון במקומו המתאים המתייחס לנתון הזה (לכן הוא נקרא "יחסי"), וזה מאוד מאוד תלוי הקשר ונתון תמיד לשיפוטו של המפתח לפי המטרות שהוא צופה לפרוייקט.
אחד השיקולים הוא אופטימיזציה של סינונים וביצועים, אבל זה רק "אחד" השיקולים הרלוונטיים יש עוד ימבה של שיקולים. (אגב אני יודע וגם אני נוהג כך שכל המפתחים מקפידים להפריד שם פרטי ממשפחה. ועדיין, כל עוד אתה לא משרד הפנים קשה לי להבין מה הצורך הגדול בזה??? אתה רוצה סינון של כל הכהנים? או של כל המשפחות המרוקאיות??? נו נו)
יש למשל דברים שעדיף לשמור אותם בתור JSON בעמודת טקסט כי אין שום טעם להקים עבורם טבלה או חמישים עמודות.

chagold

@ארכיטקט אמר בקוד SQL טהור לחיפוש חופשי בעמודה:

(אגב אני יודע וגם אני נוהג כך שכל המפתחים מקפידים להפריד שם פרטי ממשפחה. ועדיין, כל עוד אתה לא משרד הפנים קשה לי להבין מה הצורך הגדול בזה???

במוסד כשיש כמה ילדים למשפחה אחת ורוצים לחשב דברים לפי משפחה או אפילו לחסוך את הצורך לכתוב כתובת לכ"א.

רפאל

@ארכיטקט אמר בקוד SQL טהור לחיפוש חופשי בעמודה:

(לכן הוא נקרא "יחסי"),

לא בדיוק.

@ארכיטקט אמר בקוד SQL טהור לחיפוש חופשי בעמודה:

מה עושה כאן שאילתה רקורסיבית????

לא זכיתי להבין מדוע אתה מכנה CTE "שאילתה רקורסיבית".

ארכיטקט

@רפאל אמר בקוד SQL טהור לחיפוש חופשי בעמודה:

לא בדיוק.

אשמח לדעת מה כן בדיוק. לא הרווחנו הרבה מה"לא בדיוק" חוץ מלהשאיר אותנו במתח.
כאן טבלה נקראת בשם "Relation" רוצה לומר Person הוא האובייקט המתייחס לאיש, הלא כן?

אשר לרקורסיביות מעולם לא ראיתי שימוש בביטוי WITH כי אם בשאילתה רקורסיבית, עכשיו אני רואה שמשתמשים בזה במקום תת שאילתה. זה חדש לי לגמרי.

רפאל

@ארכיטקט עיין בהמשך המקור שציינת:

E. F. Codd used the term "relation" in its mathematical sense of a finitary relation, a set of tuples on some set of n sets S1, S2, .... ,Sn.[4] Thus, an n-ary relation is interpreted, under the Closed-World Assumption, as the extension of some n-adic predicate: all and only those n-tuples whose values, substituted for corresponding free variables in the predicate, yield propositions that hold true, appear in the relation.

Finitary Relation

In mathematics, a finitary relation over sets X1, ..., Xn is a subset of the Cartesian product X1 × ⋯ × Xn; that is, it is a set of n-tuples (x1, ..., xn) consisting of elements xi in Xi.[1][2][3] Typically, the relation describes a possible connection between the elements of an n-tuple. For example, the relation "x is divisible by y and z" consists of the set of 3-tuples such that when substituted to x, y and z, respectively, make the sentence true.

במילים אחרות:
In a relational database, the table is a relation because it stores the relation between data in its column-row format. The columns are the table's attributes, and the rows represent the data records. A single row is known as a tuple
(Definition of Database Relation)

תחומים - פורום חרדי מקצועי

קוד SQL טהור לחיפוש חופשי בעמודה