Witam
Chciałbym wykonać złączenie dwóch encji w JPA, ale pod warunkiem nieco
bardziej rozbudowanym niż zwykłe bycie w relacji. Konkretny przykład:

mamy tabele:
- dostawców szkoleń (id, nazwa)
- szkoleń (id, data, dostawca_id, ocena)

I chcemy np. wyświetlić wszystkich dostawców a obok nich średnią ocen
szkoleń przeprowadzonych zadanym okresie. Jeśli dostawca w danym okresie
nie prowadził szkoleń, średnia ocen ma być null. Czyli w SQL-u to by
było jakoś tak:

SELECT d.id, AVG(s.ocena) FROM dostawcy d LEFT OUTER JOIN szkolenia s ON
d.id = s.dostawca_id AND :data1 <= s.data AND s.data <= :data2 GROUP BY d.id

Jak to zrobić w JPA? Nie ma tu czegoś takiego jak ON, gdzie można
rozszerzyć warunek poza zwykłe porównanie id. WHERE nie można
zastosować, bo ograniczy to listę dostawców do spełniających warunek, a
chcemy pobrać wszystkich. Ma ktoś jakiś pomysł?

Pozdrawiam
Szarak

wto, 17 cze 2008 o 15:22 GMT, Szarak napisał(a):

> Jak to zrobić w JPA? Nie ma tu czegoś takiego jak ON,

Sa tez niereformowalni SQLowcy ktorzy nie stsujac JOINow, a FROM tab1,
tab2, i da sie ogolnie osiagnac taki sam efekt

> gdzie można
> rozszerzyć warunek poza zwykłe porównanie id. WHERE nie można
> zastosować, bo ograniczy to listę dostawców do spełniających warunek, a
> chcemy pobrać wszystkich. Ma ktoś jakiś pomysł?

dlaczego nie mozna uzyc WHERE? da sie, tylko trzeba troche zmodyfikowac
warunek, tak zeby dopuszczal tez nullowe wartosci...

Pozdrawiam
Brzezi

Dnia 2008-06-17 15:50 Brzezi napisał(a):
> dlaczego nie mozna uzyc WHERE? da sie, tylko trzeba troche zmodyfikowac
> warunek, tak zeby dopuszczal tez nullowe wartosci...

Jeśli jesteś w stanie na szybko sklecić takie zapytanie, to bardzo bym
poprosił, bo nijak nie mogę sobie tego wyobrazić.

Pozdrawiam
Szarak

Dnia Tue, 17 Jun 2008 21:07:54 +0200, Szarak napisał(a):

> Dnia 2008-06-17 15:50 Brzezi napisał(a):
>> dlaczego nie mozna uzyc WHERE? da sie, tylko trzeba troche zmodyfikowac
>> warunek, tak zeby dopuszczal tez nullowe wartosci...
>
> Jeśli jesteś w stanie na szybko sklecić takie zapytanie, to bardzo bym
> poprosił, bo nijak nie mogę sobie tego wyobrazić.
>

Ja bym to widział jakoś tak:

SELECT d.id, AVG(s.ocena)
FROM dostawcy d
LEFT OUTER JOIN szkolenia s ON d.id = s.dostawca_id
GROUP BY d.id
WHERE (:data1 <= s.data AND s.data <= :data2) or (s.data is null)

wto, 17 cze 2008 o 21:42 GMT, Rafal napisał(a):

>> Dnia 2008-06-17 15:50 Brzezi napisał(a):
>>> dlaczego nie mozna uzyc WHERE? da sie, tylko trzeba troche zmodyfikowac
>>> warunek, tak zeby dopuszczal tez nullowe wartosci...
>>
>> Jeśli jesteś w stanie na szybko sklecić takie zapytanie, to bardzo bym
>> poprosił, bo nijak nie mogę sobie tego wyobrazić.
> Ja bym to widział jakoś tak:
>
> SELECT d.id, AVG(s.ocena)
> FROM dostawcy d
> LEFT OUTER JOIN szkolenia s ON d.id = s.dostawca_id
> GROUP BY d.id
> WHERE (:data1 <= s.data AND s.data <= :data2) or (s.data is null)

Mi o cos takiego chodzilo wlasnie, czyli o ORa

Pozdrawiam
Brzezi

Dnia 2008-06-17 21:42 Rafal napisał(a):
> Ja bym to widział jakoś tak:
>
> SELECT d.id, AVG(s.ocena)
> FROM dostawcy d
> LEFT OUTER JOIN szkolenia s ON d.id = s.dostawca_id
> GROUP BY d.id
> WHERE (:data1 <= s.data AND s.data <= :data2) or (s.data is null)

Po pierwsze chodzi mi o zapytanie w JPQL-u, bo w SQL-u wiem jak zrobić.
A po drugie to nie zadziała, bo s.data nigdy nie jest null. Data albo
się mieści w w przedziale, albo nie. Z tym, że warunek z datami założony
na WHERE ograniczy również dostawców.

Pozdrawiam
Szarak

Dnia Tue, 17 Jun 2008 22:13:20 +0200, Szarak napisał(a):

> Dnia 2008-06-17 21:42 Rafal napisał(a):
>> Ja bym to widział jakoś tak:
>>
>> SELECT d.id, AVG(s.ocena)
>> FROM dostawcy d
>> LEFT OUTER JOIN szkolenia s ON d.id = s.dostawca_id
>> GROUP BY d.id
>> WHERE (:data1 <= s.data AND s.data <= :data2) or (s.data is null)
>
> Po pierwsze chodzi mi o zapytanie w JPQL-u, bo w SQL-u wiem jak zrobić.
> A po drugie to nie zadziała, bo s.data nigdy nie jest null. Data albo
> się mieści w w przedziale, albo nie. Z tym, że warunek z datami założony
> na WHERE ograniczy również dostawców.

A próbowałeś odpalić to zapytanie (sql)?
Albo lepiej takie:
>> SELECT d.id, s.data
>> FROM dostawcy d
>> LEFT OUTER JOIN szkolenia s ON d.id = s.dostawca_id
>> WHERE s.data is null

dostawcy polączeni left joinem ze szkoleniami powodują to, że w liscie
wynikowej powinni znalezc się wszyscy dostawcy, a tam gdzie nie ma zadnego
szkolenia dla danego dostawcy rekord szkoleń będzie nullowy, czyli s.data
będzie wtedy również nullem.

Nie wiem czy mechanizm JPA działa podobnie. Musisz sprawdzić.
Nie mniej przy sql taki zabieg powinien być ok.

Dnia 2008-06-17 22:24 Rafal napisał(a):
> A próbowałeś odpalić to zapytanie (sql)?

Tak. Po pierwsze postgres wywala się z powodu tego, że where jest po
group by ;) Ale mniejsza o to, przestawiłem i efekt jest taki, że
zwracani są TYLKO dostawcy, którzy mają szkolenia w zadanym okresie,
albo nie mają szkoleń w ogóle. Pomijani są ci, którzy mają tylko
szkolenia poza zadanym okresem. Trochę się już bawiłem tymi tabelami w
samym sql-u, zanim zabrałem się za JPA.

> Albo lepiej takie:
>>> SELECT d.id, s.data
>>> FROM dostawcy d
>>> LEFT OUTER JOIN szkolenia s ON d.id = s.dostawca_id
>>> WHERE s.data is null
>
> dostawcy polączeni left joinem ze szkoleniami powodują to, że w liscie
> wynikowej powinni znalezc się wszyscy dostawcy, a tam gdzie nie ma zadnego
> szkolenia dla danego dostawcy rekord szkoleń będzie nullowy, czyli s.data
> będzie wtedy również nullem.

Niestety jest tak jak mówiłem. WHERE filtruje wynik złączenia, czyli to
zapytanie zwróci tylko takich dostawców, którzy nie mają w ogóle szkoleń.

Pozdrawiam
Szarak

Dnia Tue, 17 Jun 2008 22:58:46 +0200, Szarak napisał(a):

> Dnia 2008-06-17 22:24 Rafal napisał(a):
>> A próbowałeś odpalić to zapytanie (sql)?
>
> Tak. Po pierwsze postgres wywala się z powodu tego, że where jest po
> group by ;) Ale mniejsza o to, przestawiłem i efekt jest taki, że
> zwracani są TYLKO dostawcy, którzy mają szkolenia w zadanym okresie,
> albo nie mają szkoleń w ogóle. Pomijani są ci, którzy mają tylko
> szkolenia poza zadanym okresem. Trochę się już bawiłem tymi tabelami w
> samym sql-u, zanim zabrałem się za JPA.
>

Faktycznie, to nie uwzględnia jednej grupy dostawców.

To może coś takiego:

SELECT d.id, AVG(
CASE (:data1 <= s.data AND s.data <= :data2)
WHEN true THEN s.ocena
WHEN false THEN null
END)
FROM dostawcy d
LEFT OUTER JOIN szkolenia s ON d.id = s.dostawca_id
GROUP BY d.id


w ogóle bez where'a.
Ps1. Pisane od ręki. Nie wiem czy zadziała.
Ps2. Nie wiem czy da sie do przepisać na JPQL

Szarak wrote:
> Po pierwsze chodzi mi o zapytanie w JPQL-u, bo w SQL-u wiem jak zrobić.
> A po drugie to nie zadziała, bo s.data nigdy nie jest null. Data albo
> się mieści w w przedziale, albo nie. Z tym, że warunek z datami założony
> na WHERE ograniczy również dostawców.

Przepraszam, że nie w temacie ale chciałem zapytać czy użycie w tym momencie
procedury składowanej byłoby błędem? Jak widać skala kombinacji w zapytaniu
jest duża i chyba najłatwiej byłoby jednak tego joina użyć - właśnie w
procedurze składowanej.

Czy coś stoi na przeszkodzie? Jeśli tak, to może użyć widoku?

Dnia 2008-06-18 09:22 fv napisał(a):
> Przepraszam, że nie w temacie ale chciałem zapytać czy użycie w tym momencie
> procedury składowanej byłoby błędem? Jak widać skala kombinacji w zapytaniu
> jest duża i chyba najłatwiej byłoby jednak tego joina użyć - właśnie w
> procedurze składowanej.
>
> Czy coś stoi na przeszkodzie? Jeśli tak, to może użyć widoku?

Nie jestem pewien, ale żeby użyć takiej procedury składowanej, trzeba
zastosować chyba native query. Wtedy równie dobrze można sporządzić tego
joina z on. Z widokiem chyba będzie podobnie.

Pozdrawiam
Szarak

Dnia 2008-06-18 07:48 Rafal napisał(a):
> To może coś takiego:
>
> SELECT d.id, AVG(
> CASE (:data1 <= s.data AND s.data <= :data2)
> WHEN true THEN s.ocena
> WHEN false THEN null
> END)
> FROM dostawcy d
> LEFT OUTER JOIN szkolenia s ON d.id = s.dostawca_id
> GROUP BY d.id

Działa, nieźle :)

> Ps2. Nie wiem czy da sie do przepisać na JPQL


Hehe, w to wątpię :) Dzięki za zaangażowanie :)

Pozdrawiam
Szarak

Szarak wrote:
> Dnia 2008-06-18 09:22 fv napisał(a):
>> Czy coś stoi na przeszkodzie? Jeśli tak, to może użyć widoku?
> Nie jestem pewien, ale żeby użyć takiej procedury składowanej, trzeba
> zastosować chyba native query. Wtedy równie dobrze można sporządzić tego
> joina z on. Z widokiem chyba będzie podobnie.

Hm.. byłoby bardzo ciekawie, gdyby jakiś driver odróżniał normalną "tabelę"
od widoku. Odważyłem się napisać o widoku właśnie ze względu na możliwość
zrobienia na nim zwykłego selecta.

Może wyjaśnię co mam na myśli przez widok:

CREATE VIEW widoczek AS
i tutaj wklejasz swojego joina.

Każde zrobienie na widoczku selecta powoduje wywołanie zapytania po AS.

Przepraszam, że się wcinam - może czegoś nie rozumiem..

Dnia 2008-06-18 13:27 fv napisał(a):
> CREATE VIEW widoczek AS
> i tutaj wklejasz swojego joina.
>
> Każde zrobienie na widoczku selecta powoduje wywołanie zapytania po AS.

Wiem o czym mówisz i być może dałoby się to zrobić bez native query, ale
wtedy trzeba by zdefiniować osobną encję powiązaną z tym konkretnym
widokiem. Oczywiście zakładając, że JPA pozwala wiązać encje z widokami
- nie sprawdzałem ani nie wgryzałem się w specyfikację pod tym kątem.
Jak dla mnie to niepotrzebne komplikowanie modelu dla jednego raportu.
Już lepiej zrobić native query z joinem. Jest to zgodne z SQL i nie
powinno się nic wysypać przy zmianie bazy danych. Albo widok i native
query. Zaznaczam, że nie jestem specem od JPA, to są tylko moje
przypuszczenia.

Pozdrawiam
Szarak

Użytkownik "Szarak"napisał...
[..]
> d.id = s.dostawca_id AND :data1 <= s.data AND s.data <= :data2 GROUP BY
> d.id
>
> Jak to zrobić w JPA? Nie ma tu czegoś takiego jak ON, gdzie można
> rozszerzyć warunek poza zwykłe porównanie id. WHERE nie można zastosować,
> bo ograniczy to listę dostawców do spełniających warunek, a chcemy pobrać
> wszystkich. Ma ktoś jakiś pomysł?
>
> Pozdrawiam
> Szarak

Łał! :)
http://depressedprogrammer.wordpress...-jpql-and-jpa/
Wystarczyło zagooglać - pierwszy rezultat... ;)

ON rzeczywiście nie ma, bo nie jest w JPQL do szczęścia w niczym potrzebny.

Pozdrawiam,
minority