Przypisanie klucza obcego na podstawie reguł

W bazie danych przechowuję płatności. Każda płatność może być przypisana do jakiejś kategorii, jednak te przypisywanie ma być na podstawie reguł, np.:

• jeśli tytuł to pyszne.pl, to kategoria to jedzenie
• jeśli tytuł to enea, to kategoria to rachunki

Teraz chciałbym napisać takiego selecta, który zwróci mi płatności wraz z kategoriami przypisanymi na podstawie tychże reguł.

Baza wygląda tak:

Płatności:

create table payment
(
    id int,
    title text,
    recipient text,
    amount int,
    category_id int null
);

Kategorie:

create table category
(
    id int,
    title text
);

Reguły:

create table assign_rule
(
    id int,
    description text
);

Przypisanie reguły do kategorii:

create table assign_rule_category
(
    id int,
    assign_rule_id int,
    value text,
    category_id int
);

Przypisywanie płatności do kategorii ogarnąłem na zasadzie takiej, że najpierw pobieram listę wszystkich płatności, potem listę wszystkich przypisań reguł do kategorii i łączę to w serwisie na zasadzie:

public int? GetCategory(PaymentEntity entity)
{
    var rulesCategories = GetAllRulesCategories().Result;

    foreach (var rule in rulesCategories)
    {
        switch ((Rule)rule.Rule_id)
        {
            case Rule.NAME_CONTAINS:
                if (entityName.Contains(rule.Value))
                    return rule.Category_id;
                break;

            case Rule.TITLE_CONTAINS:
                if (entityTitle.Contains(rule.Value))
                    return rule.Category_id;
                break;
        }
    }

    return null;
}

Zastanawiam się, czy jest możliwość ogarnięcia tego w 1 zapytaniu. Chciałbym, by dla tych danych wejściowych:
https://www.db-fiddle.com/f/puzvVCStRZX58G7Wbxj2Eu/0

wynik selecta wyglądał tak:

| id | title               | recipient        | amount | category_id |
|:--:|---------------------|------------------|-------:|:-----------:|
| 1  | internet bill       | electric company | 100    | 3           |
| 2  | money               | Mike             | 200    | 2           |
| 3  | food ordered online | BestPizza        | 80     | 1           |

Jak widać, przypisanie powinno być na podstawie tych reguł:

insert into assign_rule_category values
(1, 1, 'bill', 3), -- if 'title' contains 'bill', then assign payment to 'BILLS' category
(2, 1, 'food', 1), -- if 'title' contains 'food', then assign payment to 'FOOD' category
(3, 2, 'Mike', 2); -- if 'recipient' contains 'Mike', then assign payment to 'LOAN' category

Jakieś pomysły?

Spróbuj coś takiego:

UPDATE
    payment
SET
    category_id = 
         CASE 
            WHEN title LIKE 'internet' THEN 3
            WHEN title LIKE 'money' THEN 2
            WHEN title LIKE 'food' THEN 1
         END

@Gothes: Ja mając podobny problem rozwiązałem tworząc widok, w moim przypadku materialized view. Nie pracujesz na oryginalnych danych, a masz dokładnie to czego oczekujesz

Widok jest ok. Pytanie tylko czy jedna płatność nie będzie mogła wylądować w dwóch kategoriach kiedyś.

Hmm nie bardzo rozumiem waszych rozwiązań. W zasadzie to udało mi się ogarnąć to w 1 zapytaniu tak jak chciałem:
https://www.db-fiddle.com/f/puzvVCStRZX58G7Wbxj2Eu/4

Powinienem z jakiegoś powodu rozważyć wasze opcje?

Wszystko zależy ile masz rekordów. Przy małej bazie to rozwiązanie pewnie się sprawdzi. like, concat i % to bardzo drogie operacje. Pomijam oczywiście aspekt późniejszego refaktoru/rozbudowywania query.

Samo przyznawanie katagerii po słowie kluczowym, nie jest jednoznaczne, jeżeli na przykład zrobię płatność: online food bill to wg. reguł trafi do dwóch kategorii co jest prawidłowe, ale tego w swoim zapytaniu nie uwzględniasz, czy raczej pomijasz.

@Dregorio
Ale ja zawsze będę wyciągał ilość wpisów zgodnie z paginacją, czyli np. 20. Dla każdego z tych wpisów będę przypisywał kategorię, tak że like i concat mnie nie zabolą

@Panczo
W zasadzie, to obojętne mi jest do której kategorii to trafi. Nie będę miał raczej przypadków, że jakaś płatność będzie mogła być przypisana do dwóch.

Sprawdź spreparowany przykład: https://www.db-fiddle.com/f/puzvVCStRZX58G7Wbxj2Eu/5

I zobacz jak id kategorii ma się do jej nazwy.

To już lepiej zamiast max użyj row_number i weź pierwszy pasujący wiersz

Gothes napisał(a):

@Dregorio
Ale ja zawsze będę wyciągał ilość wpisów zgodnie z paginacją, czyli np. 20. Dla każdego z tych wpisów będę przypisywał kategorię, tak że like i concat mnie nie zabolą

Zabolą, bo używasz ich w sekcji WHERE. Napaś te tabele setkami tysięcy rekordów, odpal sobie zapytanko pytając o dziesiąta dwudziestkę rekordów (181-200) i zobacz, jak zapiernicza.

@Gothes: Tak jak kolega wyżej powiedział. Kolejność jest teoretycznie* FROM -> WHERE ->LIMIT -> SELECT.

Pamiętaj, że jak chcesz paginowac to musisz dodać jeszcze ORDER BY bo inaczej wyniki będą nieprzewidywalne. Ponadto raczej nie powinno się stosować limit/offset do paginacji, są to operacje bezstanowe z perspektywy bazy, lepiej utworzyć np.cursor

* Najlepiej użyć EXPLAIN to ci pokaże jak planner zaplanował wykonanie danego query.